0

We normally think about normalisation as a way of avoiding data duplication, and for speed we tend to denormalise. However I have a performance problem with a database I maintain and I wonder if I could normalise some of the data to increase the speed of select queries.

We have a legacy MS SQL Server 2005 database that runs our helpdesk. There is a Tasks table that contains a task that some engineer has to do. Over the years this table has had more and more columns added to it. It now has 29 columns of various types including text (BLOB) columns.

The problem is that if I do a simple query (using ADO from VBScript) like select created_date, title from Tasks where blah blah then this is getting rather slow. If I look in detail I find that the query returns in a second or two but stepping through the result set and reading back the (few hundred) results takes 10-20 seconds. When I look at the server performance I see a big spike in the disk i/o and disk queue length.

This query doesn't have any joins, so it should be about as fast as it could be. The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I wonder if the speed problems are due to SQL Server having to pick out just the few columns I'm querying from the 29 columns currently in each row. So my question is whether I can improve performance by splitting the table into a table with just the most commonly queried columns and a second table with everything else?

I guess the obvious thing is to try the experiment, but setting up a test system with SQL 2005 is not an insignificant effort. It would be very helpful if someone has run into this sort of problem and can tell me whether it's likely to be worth trying or not.

4

No, is unlikely normalization will help. What you have there looks more like missing indexes. 29 columns is not much. And BLOB columns are already stored by the system on separate allocation units (out of row), see Table and Index Organization.

The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I recommend you read How to analyse SQL Server performance. Your indexes are not used, or not useful. For more useful answer post the exact table definition, all indexes, the exact query, and the execution plan XML.

| improve this answer | |
  • Thanks Remus. I've followed your advice and it does look like a problem with the indexing. – John Rennie Aug 18 '16 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.