April 8, 2011 at 7:42 am
Hi,
I am having an issue with a lookup on a clustered index that is split across a number of columns.
The table structure is similar to:
CREATE TABLE dbo.Table1 (
ID1 INT NOT NULL,
ID2 INT NOT NULL,
TypeID TINYINT NOT NULL,
.....
CONSTRAINT PK_Table1 PRIMARY KEY (ID1 ASC, ID2 ASC, TypeID ASC)
)
It currently has about 57 million records, although this is increasing.
When I do a lookup on ID1, then performance is ok:
SELECT ID1, COUNT(ID2)
FROM dbo.Table1 WITH(NOLOCK)
WHERE TypeID = 1
AND ID1 BETWEEN 100000 AND 100010
GROUP BY ID1
However, switching the query to use ID2 causes the query to grind to a stop:
SELECT ID2, COUNT(ID1)
FROM dbo.Table1 WITH(NOLOCK)
WHERE TypeID = 1
AND ID2 BETWEEN 100000 AND 100010
GROUP BY ID2
Looking at the execution plan, the primary key scan on the 1st query brings back 10 records, but the second query brings back 57 million.
I'm guessing this is because it is the second column in the key & so SQL Server can't utilise it.
My question is the best way to resolve it - I figure that if I put a separate index on ID2 this may help/resolve. My reservation with this, is that it will slow down updates/inserts/deletes etc.
Any advice on this would be appreciated!
April 8, 2011 at 8:59 am
Tom West (4/8/2011)
My question is the best way to resolve it - I figure that if I put a separate index on ID2 this may help/resolve. My reservation with this, is that it will slow down updates/inserts/deletes etc.
I think that's the heart of your issue: SELECT vs INSERT performance
Adding an index will also take more space, dont know if that is an issue at all for you.
Can you describe your workload, when are you inserting, when are you selecting?
If you do put a separate index on ID2 you will be able to improve your query. Keep in mind SQL will have to lookup (Bookmark Lookup) any fields that is not included in the index. This is trivial if your range is very restrictive (like in your example where you retrieved only 10 rows) but builds up with the number of row retrieved. At some point SQL will scan if too many rows have to be looked up.
Hth
Maxim
April 8, 2011 at 9:14 am
Tom West (4/8/2011)
Looking at the execution plan, the primary key scan on the 1st query brings back 10 records, but the second query brings back 57 million.I'm guessing this is because it is the second column in the key & so SQL Server can't utilise it.
Correct. To get a seek for the second query you need an index that contains TypeID and ID 2 as a left-based subset. I recommend an index TypeID, ID2 (in that order) so that SQL can seek and do an optimal group by too.
My question is the best way to resolve it - I figure that if I put a separate index on ID2 this may help/resolve. My reservation with this, is that it will slow down updates/inserts/deletes etc.
Yes, it will have an effect, but one index is not going to cause a huge degradation in insert performance. Test, but you really shouldn't notice much difference from just one index.
p.s. Why the Nolocks? Is inconsistent data acceptable?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2011 at 4:23 am
Hi,
Thanks for the replies.
My next question is, should I apply the index to the table or is it better off having the index on the view that is used to query the data? The view doesn't aggregate the data so I would guess it'd make no difference?
If I wrote a wrapping view that does aggregate the data, would that be a better indexed?
Thanks
Tom
p.s. WITH(NOLOCK) is the DBA policy, not mine... We query subscription tables in many instances, but we have to apply the logic on all queries...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply