Need to improve performance query a table with millions of rows - SQL Server

Question

This query on an audit trail table starts slowing down once the load increases in performance testing (reads and inserts) and I've done what I can with indexing. With this query and these tables and indexes, what more can I do?

CREATE TABLE [dbo].[mod2] (
  [id] [int] IDENTITY,
  [userId] [int] NOT NULL,
  [epochTime] [bigint] NOT NULL,
  [forecastId] [int] NOT NULL,
  [description] [char](12) NOT NULL,
  [auxText] [text] NULL,
  [auxDate] [date] NULL
);


ALTER TABLE [dbo].[mod2] ADD CONSTRAINT PK_mod2 PRIMARY KEY(ID);

ALTER TABLE [dbo].[mod2]  WITH CHECK
    ADD CONSTRAINT [FK_mod2_forecastId] FOREIGN KEY([forecastId])
    REFERENCES [dbo].[forecast] ([id]);

ALTER TABLE [dbo].[mod2] CHECK CONSTRAINT [FK_mod2_forecastId];

ALTER TABLE [dbo].[mod2]  WITH CHECK
    ADD CONSTRAINT [FK_mod2_userId] FOREIGN KEY([userId])
    REFERENCES [dbo].[user] ([id]);

ALTER TABLE [dbo].[mod2] CHECK CONSTRAINT [FK_mod2_userId];

CREATE NONCLUSTERED INDEX IX_modification_auxDate ON [dbo].[mod2] (auxDate ASC);

CREATE NONCLUSTERED INDEX IX_modification_epochTime ON [dbo].[mod2] (epochTime ASC);

CREATE NONCLUSTERED INDEX IX_modification_description ON [dbo].[mod2] (description ASC);

CREATE NONCLUSTERED INDEX IX_modification_forecastId ON [dbo].[mod2] (forecastId ASC);

CREATE NONCLUSTERED INDEX IX_modification_userId ON [dbo].[mod2] (userId ASC);

and this is my query:

SELECT name, epochTime, auxDate 
    FROM mod2 WITH (NOLOCK)
    JOIN [user] ON [user].id = mod2.userId 
    WHERE forecastId = ? AND description = ? AND auxDate = ?

This is a legacy system and it was crawling before I put these indexes on it and changed the description field from VARCHAR to CHAR

The forecast and user id fields are INT and indexed similarly.

You might also consider changing the datatype of auxText from text to varchar(max). it isn't going to help this query but the text datatype has been deprecated in favor of varchar(max) for over a decade now. — Sean Lange, May 03 '17 at 14:05

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

There are a few things you can do here.

One is to ensure there is a clustered index on user for its id field (probably there is, but making sure won't hurt).

The individual indexes you put in are not great - in particular given the query pattern you have shown - either non of them would be used (as they do not contain the full data), or some of them might be used and then the full table will be refered to in order to pick out the remaining data required to fulfill the query.

For this particular query, for mod2, I'd likely add an index on userId with it covering forecastId, description and auxDate - this way the index contains all the data required to fulfil the query (on the mod2 side).

edited May 23 '17 at 10:31

Community

1
1

answered May 03 '17 at 14:03

DigiFriend

1,164
5
10

@Adam - consider removing your one field indexes in favour of less indexes that cover more. – DigiFriend May 03 '17 at 15:36
OK. So if do that, and then have another query which doesn't use `auxDate` in the `WHERE` clause, should I create a second covered index for it or will the original covered index suffice, even though it includes `auxDate`? – Adam May 03 '17 at 15:45
@Adam - if the other query still uses the other covered fields, the index will likely still be used, so another index may not be needed. – DigiFriend May 03 '17 at 15:46
Checked the [user].[id] column is indexed with a PK clustered index, and created the index (edited your answer) and ran some performance tests which show the duration dropped by 2/3, which is nice. It is significantly still the slowest operation in the app though, which is puzzling because the other 2 main end-points do writes to that table but are twice as quick now (previously 5 times quicker). Does that still suggest a problem or am I going to have to live with this? – Adam May 04 '17 at 11:37
@Adam - check the query plan, see what is and isn't using indexes, what is scanning (good) vs seeking (not so good). Consider that inserting **one** row vs going through millions will of course be worse. When you say slowest, by what factor? – DigiFriend May 04 '17 at 14:21
No scans, just seeks! Currently playing with index. Does ASC/DESC help much? – Adam May 04 '17 at 16:18
ASC/DESC just describes the order these are saved in the index, though the closer that is to the actual query requirements, the better. – DigiFriend May 04 '17 at 16:21
I had to delete the other indexes before SQL Server would use my new covered index. And now it does, but I see it's doing a Seek, not a Scan. See my answer, although I don't see what else I can do. – Adam May 04 '17 at 16:40
At least it _is_ using the index. How much slower is the query than others? – DigiFriend May 04 '17 at 16:50
need to get the sql query logging with log4jdbc, but spring boot won't let me so I'm leaving it like this for now. – Adam May 05 '17 at 15:52

Adam · Answer 2 · 2017-05-04T16:38:52.023

CREATE NONCLUSTERED INDEX IXmod2_user_desc_forecast_auxdate 
ON [dbo].[mod2] (userId, forecastId, description, auxDate DESC);

and the query plans looks like this:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDB].[dbo].[mod2].[id]) OPTIMIZED)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDB].[dbo].[user].[id], [Expr1006]) WITH UNORDERED PREFETCH)
       |    |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[user].  [PK_user]), ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([MyDB].[dbo].[mod2].[IX_mod2_user_desc_forecast_auxdate]), SEEK:([MyDB].[dbo].[mod2].[userId]=[MyDB].[dbo].[user].[id] AND [MyDB].[dbo].[mod2].[forecastId]=(40357) AND [MyDB].[dbo].[mod2].[description]='SAVE' AND [MyDB].[dbo].[mod2].[auxDate]='2017-01-31') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[mod2].[PK_mod2]), SEEK:([MyDB].[dbo].[mod2].[id]=[MyDB].[dbo].[mod2].[id]) LOOKUP ORDERED FORWARD)

Need to improve performance query a table with millions of rows - SQL Server

2 Answers2

Linked