October 2, 2009 at 8:07 am
Please execute the below script which will create a table and populate few rows. Then execute the query and see the execution plan. You will see that it is not picking the right index. Please help me.
CREATE TABLE CUSTOMER (ID INTEGER NOT NULL,
NAME NVARCHAR(50) NOT NULL,
AGE INTEGER NOT NULL,
CITY NVARCHAR(50) NOT NULL,
COUNTRY NVARCHAR(50) NOT NULL);
ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUST PRIMARY KEY (ID, NAME);
CREATE INDEX IX_AGE ON CUSTOMER(AGE);
CREATE INDEX IX_CITY ON CUSTOMER(CITY);
CREATE INDEX IX_COUNTRY ON CUSTOMER(COUNTRY);
INSERT INTO CUSTOMER VALUES (1, 'SIDD1', 1, 'HYDERABAD', 'INDIA')
INSERT INTO CUSTOMER VALUES (2, 'SIDD2', 2, 'MUMBAI' , 'INDIA')
INSERT INTO CUSTOMER VALUES (3, 'SIDD3', 3, 'DELHI' , 'INDIA')
INSERT INTO CUSTOMER VALUES (4, 'SIDD4', 4, 'BBSR' , 'INDIA')
INSERT INTO CUSTOMER VALUES (5, 'SIDD5', 5, 'PURI' , 'INDIA')
---------Now execute the following query and see the plan. You will see that it is a index scan-----
--Following query should pick the index IX_AGE but is not picking this rather picking PK_CUST resulting in a scan
SELECT * FROM CUSTOMER WHERE AGE = 3;
Can someone explain me this behavious?
I have seen the same behavior for following queries also:
--Following query should pick the index IX_CITY but is not picking this rather picking PK_CUST resulting in a scan
SELECT * FROM CUSTOMER WHERE CITY = 'HYDERABAD';
--Following query should pick the index IX_COUNTRY but is not picking this rather picking PK_CUST resulting in a scan
SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA';
Regards,
Siddharth
October 2, 2009 at 8:28 am
spatnaik77 (10/2/2009)
---------Now execute the following query and see the plan. You will see that it is a index scan-------Following query should pick the index IX_AGE but is not picking this rather picking PK_CUST resulting in a scan
SELECT * FROM CUSTOMER WHERE AGE = 3;
The nonclustered index isn't covering (doesn't have all the columns required by the query) and the query is returning too high a percentage of the table for a nonclustered index seek + key lookups to be efficient.
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
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
October 2, 2009 at 9:22 am
Yes you are absolutely right. When i used the following query:
SELECT AGE FROM CUSTOMER WHERE AGE = 3; it picks the right index. But my requirement is i need almost all the columns in such queries. How should i go forward? Create a covering index for all the columns is not a good idea as it will have big impact on insert/update performance! Can you please suggest?
Regards,
Siddharth
October 2, 2009 at 9:30 am
spatnaik77 (10/2/2009)
SELECT AGE FROM CUSTOMER WHERE AGE = 3; it picks the right index. But my requirement is i need almost all the columns in such queries. How should i go forward?
Either create a covering index, knowing that it will consume a lot of space and be expensive to keep updated, or accept that the query will do a table scan if it retrieves a lot (more than about 0.5%) of rows.
Which you do depends on how critical this query is, how often it runs, how often the table is updated, etc.
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
October 2, 2009 at 11:01 am
Thanks. Any idea whether this issue is specific to SQLServer? Or we will see the similar behavior in Oracle and MySql Also? ( i have not yet tried). I am thinking of using HINT in the query. The only issue that i see is i need to write specific queries for each database. What do you think? is it a good idea?
October 2, 2009 at 11:14 am
The question isn't so much whether you CAN make it use the index, it's more is it SMART to force it to use the index. The reason it's not using the index in many cases is because it's "cheaper"/not as resource intensive to simply scan the table than to use the index.
There definitely are times when the optimizer might not make the best choice, but forcing the index is likely to cause its own set of issues in some cases.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2009 at 1:48 pm
spatnaik77 (10/2/2009)
I am thinking of using HINT in the query.
Why?
Do you want to see a query that uses an index seek or do you want the most efficient query plan? If you want a seek regardless of how expensive it may be, by all means use a hint, but be aware that you may very well be forcing a non-optimal execution plan.
If you want the most efficient query that leave off the hints unless you are 100% certain that you know better than the query optimiser what the most optimal plan for the query will be.
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply