Post reply

database design - vertical partitioning question

  • October 31, 2003 at 9:16 am

    #107187

    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 9:51 am

    #480469

    There is no way in which you can pass a tablename as a parameter (without using dynamic SQL). Unless you write a huge IF THEN ELSE structure for each possible name.

    Have you explored performance increase by using appropriate indexing?

    Might it be possible to start using an 'archiving' table to store 'old values' that are not used often?

    You can keep the 'production' tables small and get good performance. If a user needs 'all' of his data, he can query the archive table (or a union of production and archive), but at a performance penalty.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply