Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Index

Author

Topic

flchico
Starting Member

46 Posts

Posted - 2007-04-19 : 16:17:49

'Product_Codes' table has a few indexes, one of the indexes is on field 'cCode1' and it's non-clustered.


SELECT a.cDescription,
 	(SELECT TOP 1 cCode
	 FROM Product_Codes 
	 WHERE cCode1 = a.theCode) AS cCode1
FROM (SELECT cDescription,cCode1 AS theCode
	FROM Product
	WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id) AS a

it uses the right index for "Product_Codes" and is an "index seek"

But if I add '000011' to get:


SELECT a.cDescription,
 	(SELECT TOP 1 cCode
	 FROM Product_Codes 
	 WHERE cCode1 = a.theCode) AS cCode1
FROM (SELECT cDescription,'000011'+cCode1 AS theCode
	FROM Product
	WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id) AS a

it uses another index for "Product_Codes", the clustered index and is a "clustered index scan" and it slows down the query considerably.

Any idea why this is happening?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 16:37:08

Sometimes the optimizer does not pick the correct index to use. In cases where you can repeat this issue, you should consider using an index hint. This is typically true when the index is a composite one.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-04-20 : 02:47:35

You may be changing the ability of the index to "Cover" the query.

Is this any better:


SELECT a.cDescription,
 	(SELECT TOP 1 cCode
	 FROM Product_Codes 
	 WHERE cCode1 = a.theCode) AS cCode1
FROM (SELECT cDescription,'000011'+cCode1 AS theCode
        FROM
            (SELECT cDescription, cCode1
	     FROM Product
	     WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id
            ) AS x
     ) AS a

I would have expected an index on field 'cCode1' to be used for the SELECT TOP 1 part, but not for the select sub-select - for that I would expect you to need an index on some combination of cStatus_id, iAvail_id and iAvailGroup_id

EDIT: Actually thinking about it some more the optimiser may be able to make a direct link between the two selects in your first example, but because of the calculated-column in the second example this is being denied it - so possibly even with an Index Hint it isn't going to be quick. Pulling the results from the second Sub-Select into a temporary table, and then using that with the first Sub-Select might offer some performance improvement, particularly if the TempTable was appropriately indexed

Kristen

flchico
Starting Member

46 Posts

Posted - 2007-04-27 : 12:00:26

Sorry to respond so late, just got back from vacation.

You're right, using an index hint actually makes the query slower. About the temporary tables, this query is being run on a constant basis by about 10 users, about every 20 seconds per user, would using the temporary tables be a bad idea under this scenario?

Seemed so simple but I guess to simple makes life boring! :)

flchico
Starting Member

46 Posts

Posted - 2007-04-27 : 12:18:13

Another angle:

If I take out:


AND iAvail_id = iAvailGroup_id

FROM


SELECT a.cDescription,
 	(SELECT TOP 1 cCode
	 FROM Product_Codes 
	 WHERE cCode1 = a.theCode) AS cCode1
FROM (SELECT cDescription,'000011'+cCode1 AS theCode
	FROM Product
	WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id) AS a

Then it goes much faster doing an "index seek" and then a "hash match/inner join", does this make any sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources