February 25, 2011 at 1:00 pm
The database will have about 10-20k "active" records. About 5000 records a week will be added, and about the same number will become "inactive".
The table will use FTS on a filesteam field.
The table will be queried heavily. To start maybe 250k queries per day with expected growth to more than a 1M queries per day or more.
I want to optimize as much as possible because if a query uses just .01 seconds more than it needs too, it would amount to 3 hours of processor time per day. (seems like a huge amount to me)
First, would moving "inactives" to an archive table enhance performance on the main table, or would indexing the datetime field (which determines active from inactive) be sufficient.
Second, do you have any recommendations or best practices for this situation?
February 25, 2011 at 1:21 pm
KermitTheRock (2/25/2011)
The database will have about 10-20k "active" records. About 5000 records a week will be added, and about the same number will become "inactive".The table will use FTS on a filesteam field.
The table will be queried heavily. To start maybe 250k queries per day with expected growth to more than a 1M queries per day or more.
I want to optimize as much as possible because if a query uses just .01 seconds more than it needs too, it would amount to 3 hours of processor time per day. (seems like a huge amount to me)
First, would moving "inactives" to an archive table enhance performance on the main table, or would indexing the datetime field (which determines active from inactive) be sufficient.
Second, do you have any recommendations or best practices for this situation?
There is not a lot of detail to go on here. Reading your post it sounds like you are creating a database with a single table? Regardless it is not really the number of queries per day but the efficiency of each one that matters. 10-20k records is not a terribly big table. If the "inactive" records are no longer needed then it would be beneficial to delete them when they are no longer needed. If you provide some details about the tables and what you are trying to do there are lots of people on here that can offer suggestions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply