|
SQL Server 2000 Enterprise: Optimization of table design (many records)
Posted: 29 Jan 2006 11:03 PM |
Hello,
I have a question about SQL Server 2000 (Enterprise Edition): is there a limit for the number of records in a table?
If no - what do I have to take care of when the number of records amounts 1 or 2 millions?
How could I optimize table design to make simple SELECT-Statements be executed in an acceptable period of time?
(definition of field data types, indices, etc.)
I hope I'll find somebody here who knows the situation and could give me advice.
Additional information:
- The SELECT statements will only include this one table (no JOINs - I hope so ...).
- However the WHERE clause could include 6 or 7 fields.
Data types of requested fields: mixed ! (DATETYPE, INTEGER, CHAR, VARCHAR, etc.)
- The WHERE clause will be generated by the user of an ASP (3.0) - application.
The user gets a form to select different filters and specify the search items.
The he submits the form by clicking a button to get the result list.
Thanks in advance.
Regards
Thomas |
|
|
 |
|
|
Re: SQL Server 2000 Enterprise: Optimization of table design (many records)
Posted: 30 Jan 2006 09:38 AM |
You have 8060 bytes per row and a maximum database size of 1048516 TB. So I guess that puts the limit way over 1 or 2 millions.
As for optimising the table first you need to define what "...an acceptable period of time..." is.
You'll want to create a clustered index that will serve in the majority of your queries. eg: If all your queries will be over a date range, or other range. Then create the clustered index on those fields.
If the WHERE clause could contain 6 or 7 fields then a covering index over those fields could provide better performance.
Also, be sure to keep the statistics up-to-date. I can't count how many problems have been rectified by updating the statistics.
Make sure you test various indexing strategies because in the end it all comes down to the environment you're operating in.
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/ |
|
|
|
|
Re: SQL Server 2000 Enterprise: Optimization of table design (many records)
Posted: 30 Jan 2006 05:56 PM |
Hello,
thanks!
In a few days I know more... then the asp (3.0, "old asp") frontend is ready for testing the performance.
Maybe I could reduce the length of the fields... I will see.
At the moment there's only one index set > the primary key.
Regards
Thomas
(PS: winter in Germany can be terrible ... sometimes) |
|
|
|
|