SQL Server Performance Forum – Threads Archive
Time for partioning?I have a question about performance of sql and how to design. Lets say you have a system wich surrounds around a user table. Each user has a lot of relations to other tables, like message, forumposts and that sort of things. Now for example lets say that we normally do a search on users by firstname, location, age and other fields in the user-table. That is not a big issue. The problem now is that if we want to implement a way for a user to inactivate his account. You could just create a bit-field and then always in the search-queries set that the active-field should be true. But lets say that after a few months/years you could imagine that the number of inactive users would grove and therefore the number of rows returned and needs to be removed after the query is executed is growing, which could result in bad performance. I have some ideas on how to solve this 1. Move all inactive users to a duplicated users_inactive-table. The same thing with the message, forumposts-table and all the others?
2. Split the table on two different physical places (different filegroups) (partitioned tables?)?
3. Keep everything as it is and just add the where active = 1 in my queries?
4. Create a copy of the whole db on a different filesystem and move all data when a users gets inactive. Any other suggestions?
I don’t really have all the info I need to make a great recommendation, but here are my thoughts: 1) Will the inactive accounts ever be needed in the future? If yes, then I would use a active/non-active flag as you suggested above, and make it part of a composite index that can be used by your query to quickly identify those records that are active/inactive, assuming that the number of active vs. non-active records has relatively few non-active records. If the non-active records are great, or more than half of the total, then I would consider table partitioning or moving the inactive records to another database. 2) If the inactive accounts will not be used, then I would delete them, unless there is a legal reason to keep this data. If there is, then I would archive the inactive records in another database.
Brad M. McGehee, SQL Server MVP
Id go for option 3. Add the bitfield, change your queries to reference it.
For something like a forum, nearly every query will already be joining onto the users table anyway (to get username etc). Also consider whether ‘some’ data can simply be deleted. Again using a forum as an example, youd typically want to keep the vase record, conatining the username. But other rows such as preferences etc, could be deleted once a user is no longer active.
quote:Originally posted by bradmcgehee I don’t really have all the info I need to make a great recommendation, but here are my thoughts:
Please tell me what info I should provide. Thanks.