Performance Tuning One Table

SQL Server
I have just started at a new company as a Database Developer and found that this big company was having big performance issues on one very large table. To improve performance, they have split the table into multiple entities, ie, 1 table for each engine they produce and use some quite complicated code to read each entitity, then join the results together just before output. Now this breaks every rule of normalisation but they are adament this way has improved efficiency. Can you give some advice on performance tuning a database where the load is mostly on one table?

Answer Wiki

Thanks. We'll let you know when a new response is added.

When the performance on one table is bad, it can either be an indexing problem, or a storage problem. If an indexing problem, add the required index, or remove the superfluous indeces. If a storage problem, figure out how and where to store the table. This latter is specific to the DBMS in use. However, such things often involve a partitioned key, in which the DBMS does what you’re talking about, without programmer or program involvement. Also, it allows you to change schemes repeatedly, without changing any code. Back out the related code changes now.

Sheldon Linker (
Linker Systems, Inc. (
800-315-1174 (+1-949-552-1904)

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Rich0148
    Sheldon - thanks. I asked about if they tried Indexes before breaking the table up into single entries to which they of course said they had! I forgot to metion the RDBMS is SQL Server 2000 and we are planning to migrate soon to 2005. Maybe I could try experimenting with putting all of these tables into a single entity again and seeing how SQL 2005 does. Problem is that they have used this data model for 4 years now and I've been in the company only 2 weeks! I think in ORACLE you can put a single table onto one disk but this sort of thing is something I will need to investigate first before I put forward any proposal. Then again - maybe they are right and I am wrong!
    0 pointsBadges:
  • ChasTheDataGuy
    You may want to elaborate on the data placement on disk/controller as it relates to the partitions, disk configuration, size of the tables (column width and number of rows, verify the index structure and usage (is it 50% read 50% write). Here are a few other things you can do: - Track the waitstats and see what is so slow - Perfmon the cpu, page faults and disk queue lengths - Run Profiler and see what's going on in general. You can use the built in template to profile by duration to find the long running queries. Regards, Charles E. Deaton Database Administrator Realcomp II Ltd.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: