October 30, 2008 at 9:27 am
I have a query which is passing last name, first name, birth date and gender to it:
BEGIN
SELECT @NO = NO,
@ID = ID,
@SUB_LAST_NAME = LAST_NAME,
@SUB_FIRST_NAME = FIRST_NAME,
@SUB_BIRTH_DATE = BIRTH_DT,
@SUB_GENDER = SEX
FROM XXX WITH (NOLOCK)
WHERE LAST_NAME = @pSUB_LAST_NAME
AND FIRST_NAME = @pSUB_FIRST_NAME
AND BIRTH_DT = @pSUB_BIRTH_DATE
AND SEX = @pSUB_GENDER
END
I have an index on LAST_NAME ASC,
FIRST_NAME ASC,
BIRTH_DT ASC,
SEX ASC
And my query plan is doing an index scan instead of an index seek. Any idea how to get it to change to a seek?
November 5, 2008 at 9:12 am
While you have an index for each of the items, the optimizer has decided that it is easier to just read the whole table, rather than to read each of the indexes, build four separate lists and then merge the four lists, and then go back and retrieve all of the data from the main table that match that list. so:
1) for this you would benefit from a narror PK
2) setup a materialized view against the table with only the columns that you
a) need in the select,
b) need in the where clause.
set up a unique clustered index on the view, with the first column being both the narrorest of the four match values, and having the higher distribution ( I'd go with birth date), and then last name, and then the ID. Then use the INCLUDE for all of the other columns.
you also may need to use the WITH (NOEXPAND) command if you do not have the Enterprise edition in production.
Now, your select will quickly use the view doing a clustered index seek.
The more you are prepared, the less you need it.
November 5, 2008 at 9:28 am
Is this one index or 4?
If you remove "no" and "id" what happens?
November 5, 2008 at 9:38 am
Why build an indexed view. You can just build a covering index.
--build a nonclustered index
create nonclustered index xxx_empno_IX1 on
xxx (
LAST_NAME,
FIRST_NAME,
BIRTH_DT,
SEX,
NO,
ID)
on default
-- My change to your code:
BEGIN
SET @SUB_LAST_NAME = @pSUB_LAST_NAME,
SET @SUB_FIRST_NAME = @pSUB_FIRST_NAME,
SET @SUB_BIRTH_DATE = @pSUB_BIRTH_DATE,
SET @SUB_GENDER = @pSUB_GENDER
SELECT
@NO = NO,
@ID = ID
FROM
XXX --WITH (NOLOCK)
WHERE
LAST_NAME = @SUB_LAST_NAME
AND FIRST_NAME = @SUB_FIRST_NAME
AND BIRTH_DT = @SUB_BIRTH_DATE
AND SEX = @SUB_GENDER
END
November 5, 2008 at 9:42 am
an index works as well, but try to keep it narrow and put some of the lesser values in an include. you want to keep it as narrow as possible if you can.
The more you are prepared, the less you need it.
November 5, 2008 at 10:09 am
Andrew Peterson (11/5/2008)
an index works as well, but try to keep it narrow and put some of the lesser values in an include. you want to keep it as narrow as possible if you can.
Agree with the INCLUDE, however, as this is a SQL Server 7,2000 forum, I am assuming that this is not an option here.
November 5, 2008 at 11:54 am
Andrew Peterson (11/5/2008)
an index works as well, but try to keep it narrow and put some of the lesser values in an include. you want to keep it as narrow as possible if you can.
Why? Wider indexes are, in general, more useful that narrow ones and aren't significantly (or at all) slower. I'd prefer 1 wide index that several queries can use than 3 or 4 narrower ones that only a single query can use.
Within reason, of course. An index including all of the columns in the table is probably overkill.
An indexed view has to be kept up to date as data changes, much like an index on the table would.
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
November 5, 2008 at 12:59 pm
Good point on the INCLUDE. In this case you would need to include all columns as part of the index. My oversight.
The more you are prepared, the less you need it.
November 5, 2008 at 1:12 pm
Please let us know if this changes things. I'd also watch reads and index size before and after if you can and let us know.
Steve
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply