November 14, 2008 at 4:27 am
I need an index on a heavily used table with 10,000 + rows for using in a where clause.
Is, in addition to a distinctive column, including a column with a few unique values useful?
e.g.
TABLE Enquetes(
ID int IDENTITY(1,1), -- PK
ConnectID int NOT NULL, -- 1 or 2 or 3 or 4
IsOK varchar(1) NOT NULL, --'y' or 'n' or ''
SelectID int NOT NULL, -- 1..200
ToShow varchar(35) NOT NULL,
ToShow1 varchar(150) NOT NULL,
ToShow2 varchar(150) NOT NULL,
ToShow3 varchar(150) NOT NULL,
ToShow4 varchar(150) NOT NULL
Extra int NULL)
Is used in:
SELECT ToShow, ToShow1, ToShow2, ToShow3, ToShow4
FROM Enquetes
WHERE SelectID = @SelectID AND IsOK = 'y' AND ConnectID = @ConnectID
with @SelectID = 123 and @ConnectID = 1
BOL says: an index on a column with few unique values is not usefull
But in this situation should I give Enquetes an index on "SelectID, IsOK and ConnectID"?
Or on "SelectID" alone?
November 14, 2008 at 5:31 am
Henk Schreij (11/14/2008)
I need an index on a heavily used table with 10,000 + rows for using in a where clause.Is, in addition to a distinctive column, including a column with a few unique values useful?
e.g.
TABLE Enquetes(
ID int IDENTITY(1,1), -- PK
ConnectID int NOT NULL, -- 1 or 2 or 3 or 4
IsOK varchar(1) NOT NULL, --'y' or 'n' or ''
SelectID int NOT NULL, -- 1..200
ToShow varchar(35) NOT NULL,
ToShow1 varchar(150) NOT NULL,
ToShow2 varchar(150) NOT NULL,
ToShow3 varchar(150) NOT NULL,
ToShow4 varchar(150) NOT NULL
Extra int NULL)
Is used in:
SELECT ToShow, ToShow1, ToShow2, ToShow3, ToShow4
FROM Enquetes
WHERE SelectID = @SelectID AND IsOK = 'y' AND ConnectID = @ConnectID
with @SelectID = 123 and @ConnectID = 1
BOL says: an index on a column with few unique values is not usefull
But in this situation should I give Enquetes an index on "SelectID, IsOK and ConnectID"?
Or on "SelectID" alone?
If you put an index on it, keep in mind to put the most filtering column first, then the second selective, ....
Test if your queries use the designated indexes, if not , drop it.
create index x_Enquetes on Enquetes ( SelectID ,ConnectID, IsOK );
With sql2005 you might consider to use the columns ConnectID and IsOK as included columns because the have a very weak filter factor.
create index x_Enquetes on Enquetes ( SelectID)
include ( ConnectID, IsOK );
Check BOL!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 14, 2008 at 6:47 am
I tried your suggestion (in SSMS2005 on SqlServerDeveloper SP2) with an index on "SelectID, IsOK and ConnectID.
(temporary not in your suggested order, because ConnectID at this moment has only 1 value)
At this moment there are 5000 rows, but it's growing fast.
I see a Nested Loop (0%) from an Index Seek (9%) and Key Lookup (91%), both resulting in e.g. 80 rows.
The Index Seek has
- an Object with the name of the index.
- an Outputlist (ID, ConnectID, IsOK, SelectID)
- Seek predicates (SelectID, ConnectID, IsOK)
How can I see it's using all the columns in the index? From seek predicates?
This it mean it's using the full index?
November 14, 2008 at 7:02 am
If it uses the index you intended to, and it is returning those columns at that point in the plan, it will have them from the index.
btw With SQL2005 you can save the graphical execution plan.
Go to the graphical execution plan pane, rightclick in that pane and select 'save as'.
This gives you a .sqlplan xml file. If you rename that by adding .txt you can post it in the forum as attachment. :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 14, 2008 at 7:21 am
ALZDBA,
Thank you for your help.
I'm sorry that I can't show you the txt sqlplan (xml file renamed to .txt).
The fieldnames are in Dutch:alien: and not conforming the simplified table, I showed here.
But I'll have a look myself. Thanks 🙂
November 14, 2008 at 7:27 am
Dutch should be no problem for me. It's my mother tongue :hehe:
Any ways, as a dba I'm used to mystic names :w00t: , not telling me what's the actual columns purpose or content. 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 14, 2008 at 7:49 am
OK, Zutendaal Belgium, Fleming. :smooooth:
And thanks for your tip on XML showplan, I'll use it in the future 😎
November 18, 2008 at 7:11 pm
Yes, I agree with ALZDBA using the SQL servers new concept of INLUDED COLUMNS will be better. It will tremendosuly increase the performance of the engine 😀
November 19, 2008 at 1:27 am
I don't think using an INCLUDED column is a solution in this case.
I only use the index (on SelectID, IsOK and ConnectID) in the WHERE clause.
And all the columns (including lots of varchars(150)) of the record in the result.
INCLUDED is ment for fast finding the results, in the SELECT. And I use these 3 columns only in the WHERE clause.
November 19, 2008 at 2:42 am
if you would use included column, the B-tree part of the index would be smaller, so faster.
I should have to test, if SQLserver still would resolve the filtering based on the included columns or if it would only apply that filter after reading the actual datapages of the table.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2008 at 8:01 am
well .... Kalen has great info on it ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2008 at 2:09 am
Thank you for this interesting link.
But my situation is somehow another than the situation described by Karen.
I get an index seek (not a scan 🙂 )and a key-lookup (showing all those varchar fields).
Without using a INCLUDED column.
I think the reason for this "well behavior" is that I don't use an ORDER BY in the result.
I do the ordering in the front end (where the user can choose the column for the ordering).
But I am glad with Karens article, it gives me more insight in how the ordering works in relation to the index 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply