October 31, 2003 at 9:18 am
Hi!
I have a design issue on which I need some insights. We have 3-4 tables that have millions of records in them. And the application that uses these tables are now having performance issues. So we decided to vertically partition the tables by users. There are approx. 100 diff. users. But 20% of the users account for 80% of the records. My questions:
1. Does it make sense to have 1 table for each user? Or should I have like say - 1- tables and divide the users among these 10 tables
2. I dont want to use partitioned views (because 1. you have to have SQL Server Enterprise Edition for partitioned views; 2. am not sure if partitioned views would work?) - given this restriction how do I accomplish tasks like inserting, updating, deleting and selecting records from tables according to the username?
3. Lets say I decide to split into 10 tables with names table_1, table_2, table_3 and so on - now how do I change my stored procedures so that my application can pass the table names as a variable to the SP. I could use dynamic sql - but that would prevent SQL Svr from using cached execution plans - is there any other alternative?
4. What would be the ideal way to store the table information? Create a table that stores information about the main tables?
ANy comments, alternative approaches, critics are welcome.
Thanks for your help,
DK
October 31, 2003 at 2:03 pm
Before you go through all that, I'd check to see if you can get better performance, either by tuning or by buying more hardware. Can you post a query plan for one that runs slow? Indexes have been rebuilt recently? How wide is the table?
I know buying hardware sounds like an easy way out - it is! If adding some memory or putting a better processor or getting faster drives will fix it, thats far easier and probably cheaper in the long run.
Going to dynamic sql definitely is a trade off, I'd do that as a last resort.
Andy
October 31, 2003 at 2:37 pm
Tend to agree with Andy. Partitioning can work, but gets complex and usually requires some application work. Paritioned views would probably help, but would be some serious $$.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 31, 2003 at 4:03 pm
We already have some good hardware - 2GB, dual processor, RAID 10 arrays, but still have issues. Have looked into the indexes and the query plans - most queries run OK - but once in a while one of these queries may take time - reasons vary from locks to outdated statistics - but it all happens only once in a while.
I was wondering if I use sp_executesql can I pass a table name as a variable?
November 1, 2003 at 8:37 am
Can pass whatever you want into sp_executesql.
Stats you can manage with a job, whether you run your own or use the maint plan. Same for blocking - you'll almost always have some, key is to keep the length of time any one spid is blocked low.
Andy
November 2, 2003 at 5:28 pm
If you really want to partition your table consider the following:
that was my 2 cents
Bye
Gabor
Bye
Gabor
November 3, 2003 at 10:56 am
hi,
are you sure you need SQL 2000 Enterprise edition for partitioned views?
I'm running it on single server just fine.
ps
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply