Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>