1

We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.

I found one table referenced in this app with 1,332,730,786 records in it.

Table IDSelection 
IDType varchar(20) NOT NULL, 
SelectID int NOT NULL, 
UnID int NOT NULL, 
Batch int NULL

Table has following indexes

IX_IDSelection_UnID nonclustered located on PRIMARY UnID
PK_UIDSelection clustered, unique, primary key located on PRIMARY SelectID, IDType, UnID

My concerns are 1. This table has composite primary keys with some entries in IDType that are empty string (not NULL though). 2. Running simple SELECT queries like below against this table takes quite some time.

select * from IDSelection where IDType <> '' and Batch is not NULL ORDER by Batch desc  -- cluster index scan
select * from IDSelection where Batch = 9977    -- 8 minutes run for 19 records
select * from IDSelection where IDType = 'ParentID' and Batch is not NULL  -- 9 min 3614603 rows

If I use indexed UnID in WHERE clause like the one below, performance is great

SELECT * FROM IDSelection where UnID = 1093510

Index fragmentation is not an issue here. Would it help adding identity primary key and making current composite key as [non clustered unique index] help? What other options do we have?

DB version is MS SQL 2014 Enterprise

7
  • 1
    create a nonclustered index on batch, this will help for your first 2 queries. create a nonclustered index on idtype (you could make it filtered on IDType <>'') to improve last query Apr 15 '20 at 16:57
  • Have you checked if you can delete or archive part of this table? Apr 15 '20 at 17:10
  • @Dominique Boucher. Would additional indexes this create problems for INSERT's? Apr 15 '20 at 17:51
  • 1
    It does slow down insert/update/delete a bit but you have to make choices. If you run those select only once or twice a day and if they don't need to be faster, then just leave it as is. If you need those select to run fast, then the indexes will help a LOT and most of the time, the price to pay on the insert/update/delete worth it. Give it a try, you can easilly disable them or delete them if ever the impact on insert are too important. Apr 16 '20 at 13:06
  • @Dominique Boucher. And the part of my original question, would it help if instead of composite primary key I would add identity primary key and, make current composite key as [non clustered unique index] help? Apr 17 '20 at 11:15
1

How long do you think it will take to create an additional index? How often are DML executed? Is this table being read often?

Given this monstrosity has over 1 billion rows I would create separate filtered table with columns SelectID, IDType, UnID, Batch and apply there appropriate indexes.

5
  • what do you mean filtered table? Create a copy of the table, add indexes and, move data? Apr 15 '20 at 17:54
  • This table is not often executed and read but, every run it adds substantial number of record and only during batches that run once a week or so. It is used to generate statements. Apr 15 '20 at 18:23
  • What I meant by filtered table was another table with data which contains not null Batch column values. If its not very often executed I think it would be okay to add another index. Apr 15 '20 at 20:01
  • I asked this question originally, would it help if instead of composite primary key I would add identity primary key and, make current composite key as [non clustered unique index] help? Apr 17 '20 at 11:17
  • Well changing primary key and adding additional index won't change a bit in this case. If you have leeway in changing table structure i would suggest extracting IDType values to separate table and use surrogate key in the main table instead. Of course that would require query changes. Does the table even comply with 2NF & 3NF? Apr 17 '20 at 17:08
0

These need an index beginning with Batch. In the first case for the ORDER BY, in the second case for the WHERE:

select * from IDSelection where IDType <> '' and Batch is not NULL ORDER by Batch desc  -- cluster index scan
select * from IDSelection where Batch = 9977    -- 8 minutes run for 19 records

This needs INDEX(ParentID, Batch) in that order:

select * from IDSelection where IDType = 'ParentID' and Batch is not NULL  -- 9 min 3614603 rows

You already have an index on (or at least starting with) UnID, hence this is fast:

SELECT * FROM IDSelection where UnID = 1093510

Don't use * unless you really want all the columns. If you only need to select a small set of columns, they can be tacked onto the end of the index, thereby making it "covering".

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.