Query not using the right indexes

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)

Query not using the right indexes

Author

Topic

flchico
Starting Member

46 Posts

Posted - 2007-04-16 : 18:12:37

I have the following Query:

SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode1 = '10044444055555') AS cCode1,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = '10044444055555') AS cCode2,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode3 = '10044444055555') AS cCode3
FROM Product a
WHERE a.cStatus_id = 'RELE'

Table "Product_Codes" has index for fields "cCode1","cCode2", and "cCode3".
This works fine, the Query is quick (1 second), the 3 Queries on the Field list do an "Index Seek", so it seems fine.

Now the problem is that if I add to the "WHERE" clause to make it:

"WHERE a.cStatus_id = 'RELE' AND a.iProduct_id = a.iProductGroup_id "

then the query instead of taking a second, takes over 3 minutes to run. I noticed that the last 2 queries (on "Product_Codes") are using the index of the field "cCode1" (which is clustered by the way) for some reason and doing a "Clustered index SCAN".

I tried to do the "WITH (index = indx_Code2)" and "WITH (index = indx_Code3)" for the last 2 queries to force them to use the right index and it does use them but does a "Index Scan" instead of the "Index Seek" that it uses when I don't modify the "WHERE" clause.

Any ideas why when modifying the "WHERE" clause it's not using the right index and if I force it then it's not using the "index seek"?

Thanks.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-16 : 18:41:01

Are you saying it runs slow when you do this

SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode1 = '10044444055555') AS cCode1,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = '10044444055555') AS cCode2,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode3 = '10044444055555') AS cCode3
FROM Product a
WHERE a.cStatus_id = 'RELE' AND a.iProduct_id = a.iProductGroup_id

or are you also changing something in the WHERE clauses of the three subqueries? None of the subqueries are correlated with the outer query so they should be completely unaffected by the outer query unless you are also changing them.

flchico
Starting Member

46 Posts

Posted - 2007-04-16 : 18:48:04

correct, I just add "a.iProduct_id = a.iProductGroup_id" and nothing else and then it goes from one second to get the query to 2-3 minutes.

flchico
Starting Member

46 Posts

Posted - 2007-04-17 : 01:12:02

I have to make a correction, the "WHERE cCode1", "WHERE cCode2", "WHERE cCode3", it's not using a fixed number it's actually some field manipulation, I put a fixed number to not make it confusing but now I think that's part of the issue. We can analyze the actual "SELECT" statement that's matching to "cCode2":

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Item AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

Basically it's grabbing 2 fields from "Products" and making sure they're length = 7 and padding "0"s at the beginning if needed.

So I tested using the hard coded numbers as in the original example and it came in one second even with the "AND a.iProduct_id = a.iProductGroup_id" added to the WHERE clause, all "index seeks".

so then I tested with taking out the REPLICATE commands in the current example (still with the "AND a.iProduct_id = a.iProductGroup_id"):

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

and worked fine, one second and all "index" seeks. Of course if I added the REPLICATES it will crawl back to 3 minutes UNLESS it's the first SELECT for cCode1 which has a clustered index on that field in the "Product_Codes" Table, doing a "Clustered index seek".

So basically I have narrowed it down to the REPLICATE command affecting the performance, but at the same time if I take out "AND a.iProduct_id = a.iProductGroup_id" the REPLICATE has no negative effect, it's fast.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-17 : 01:20:16

not sure if it will help your perf problem, but a clearer (to me anyway) way to pad with zeros is like this:

right('0000000' + cast(varchar, a.iUPC_Mfg),7)

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 04:10:00

"cast(varchar, a.iUPC_Mfg)"

Are you CASTing or are you CONVERTing?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-17 : 11:12:26

quote:
Originally posted by flchico

I have to make a correction, the "WHERE cCode1", "WHERE cCode2", "WHERE cCode3", it's not using a fixed number it's actually some field manipulation, I put a fixed number to not make it confusing but now I think that's part of the issue. We can analyze the actual "SELECT" statement that's matching to "cCode2":

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Item AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

Basically it's grabbing 2 fields from "Products" and making sure they're length = 7 and padding "0"s at the beginning if needed.

So I tested using the hard coded numbers as in the original example and it came in one second even with the "AND a.iProduct_id = a.iProductGroup_id" added to the WHERE clause, all "index seeks".

so then I tested with taking out the REPLICATE commands in the current example (still with the "AND a.iProduct_id = a.iProductGroup_id"):

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

and worked fine, one second and all "index" seeks. Of course if I added the REPLICATES it will crawl back to 3 minutes UNLESS it's the first SELECT for cCode1 which has a clustered index on that field in the "Product_Codes" Table, doing a "Clustered index seek".

So basically I have narrowed it down to the REPLICATE command affecting the performance, but at the same time if I take out "AND a.iProduct_id = a.iProductGroup_id" the REPLICATE has no negative effect, it's fast.

Aha, that's what I thought! That makes a HUGE difference, the way you had it, the subquery didn't have to run for every row in the outer query, this way it does.

So try this for your calculated product code

RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) + 
RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7)

I'd also try adding a computed column to the Product table and using that in the subqueries, so that you don't have to repeat the expression three times in the query to get the same value. You can also achieve that without a computed column using a derived table like this

SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id,
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode1 = a.ProductCode) AS cCode1, 
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode2 = a.ProductCode) AS cCode2, 
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode3 = a.ProductCode) AS cCode3 
FROM (SELECT cProduct_id, cProduct_Name, cStatus_id,
        RIGHT('0000000' + CAST(iUPC_Mfg AS VARCHAR(7)), 7) + 
        RIGHT('0000000' + CAST(iUPC_Item AS VARCHAR(7)), 7) AS ProductCode
    FROM Product
    WHERE cStatus_id = 'RELE' AND iProduct_id = iProductGroup_id) AS a

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 12:49:40

"I'd also try adding a computed column to the Product table and using that in the subqueries, so that you don't have to repeat the expression three times in the query to get the same value. You can also achieve that without a computed column using a derived table like this"

... or a VIEW, which I would favour in preference to a computed column.

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-17 : 13:08:14

quote:
Originally posted by Kristen

"cast(varchar, a.iUPC_Mfg)"

Are you CASTing or are you CONVERTing?

oops, meant convert of course.

www.elsasoft.org

flchico
Starting Member

46 Posts

Posted - 2007-04-18 : 13:45:06

Thanks for all your help everybody,

snSQL, I tried your code but I had to modify it b/c it needs to match to the 3 fields: cCode1,cCode2,cCode3 in 3 different ways, so modified it to:

SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id,
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode1 = a.ProductCode1) AS cCode1, 
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode2 = a.ProductCode2) AS cCode2, 
(SELECT TOP 1 cItemCode 
FROM Product_Codes 
WHERE cCode3 = a.ProductCode3) AS cCode3 
FROM (SELECT cProduct_id, cProduct_Name, cStatus_id,
        RIGHT('0000000' + CAST(iUPC_Mfg AS VARCHAR(7)), 7) + 
        RIGHT('0000000' + CAST(iUPC_Item AS VARCHAR(7)), 7) + 
        RIGHT('0000000' + CAST(iUPC_Case AS VARCHAR(7)), 7) AS ProductCode1,
        RIGHT('0000000' + CAST(iUPC_Mfg AS VARCHAR(7)), 7) + 
        RIGHT('0000000' + CAST(iUPC_Item AS VARCHAR(7)), 7) AS ProductCode2,
        RIGHT('0000000' + CAST(iUPC_Mfg AS VARCHAR(7)), 7) + 
        RIGHT('0000000' + CAST(iUPC_Case AS VARCHAR(7)), 7) AS ProductCode3
    FROM Product
    WHERE cStatus_id = 'RELE' AND iProduct_id = iProductGroup_id) AS a

Now there's good news and bad news:

1) good news is that it's faster than before

2) the bad news is that is still slow,
- now it takes 20 seconds

3) if I take out the

"AND iProduct_id = iProductGroup_id"

from the WHERE clause, it takes 5 seconds

4) if I take out the

"AND iProduct_id = iProductGroup_id"

from the WHERE clause AND include the indexes for cCode1 and cCode2 ("WITH (index = indx_Code2)" and "WITH (index = indx_Code3)) then it's one second which is my goal, BUT of course if I add to the WHERE clause it takes 20+ seconds.

Still confused why adding the code to the WHERE clause is causing it to go really slow.

quote:
Originally posted by snSQL

quote:
Originally posted by flchico

I have to make a correction, the "WHERE cCode1", "WHERE cCode2", "WHERE cCode3", it's not using a fixed number it's actually some field manipulation, I put a fixed number to not make it confusing but now I think that's part of the issue. We can analyze the actual "SELECT" statement that's matching to "cCode2":

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Item AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

Basically it's grabbing 2 fields from "Products" and making sure they're length = 7 and padding "0"s at the beginning if needed.

So I tested using the hard coded numbers as in the original example and it came in one second even with the "AND a.iProduct_id = a.iProductGroup_id" added to the WHERE clause, all "index seeks".

so then I tested with taking out the REPLICATE commands in the current example (still with the "AND a.iProduct_id = a.iProductGroup_id"):

(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,

and worked fine, one second and all "index" seeks. Of course if I added the REPLICATES it will crawl back to 3 minutes UNLESS it's the first SELECT for cCode1 which has a clustered index on that field in the "Product_Codes" Table, doing a "Clustered index seek".

So basically I have narrowed it down to the REPLICATE command affecting the performance, but at the same time if I take out "AND a.iProduct_id = a.iProductGroup_id" the REPLICATE has no negative effect, it's fast.

Aha, that's what I thought! That makes a HUGE difference, the way you had it, the subquery didn't have to run for every row in the outer query, this way it does.

So try this for your calculated product code
RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) +
RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7)

I'd also try adding a computed column to the Product table and using that in the subqueries, so that you don't have to repeat the expression three times in the query to get the same value. You can also achieve that without a computed column using a derived table like this

SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode1 = a.ProductCode) AS cCode1,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode2 = a.ProductCode) AS cCode2,
(SELECT TOP 1 cItemCode
FROM Product_Codes
WHERE cCode3 = a.ProductCode) AS cCode3
FROM (SELECT cProduct_id, cProduct_Name, cStatus_id,
RIGHT('0000000' + CAST(iUPC_Mfg AS VARCHAR(7)), 7) +
RIGHT('0000000' + CAST(iUPC_Item AS VARCHAR(7)), 7) AS ProductCode
FROM Product
WHERE cStatus_id = 'RELE' AND iProduct_id = iProductGroup_id) AS a

flchico
Starting Member

46 Posts

Posted - 2007-04-18 : 16:56:01

I'm trying different things, and narrowed some code to test out certain things. I have the following SQL command testing with matching to just one of the codes to make it more simple:


SELECT a.cProduct_id, a.cProduct_Name, a.cStatus_id,
 	(SELECT TOP 1 cItemCode
	 FROM Product_Codes
	 WHERE cCode1 = a.ProductCode1) AS cCode1
FROM (SELECT cProduct_id, cProduct_Name, cStatus_id,LEFT(cProductKey_id,21) AS ProductCode1
	FROM Product
	WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id) AS a

Took 1 second, pretty Fast ("index seek"), since I have an index on field "cCode1" on "Product_Codes"

But if I add "000011":


SELECT a.cProduct_id, a.cProduct_Name, a.cStatus_id,
 	(SELECT TOP 1 cItemCode
	 FROM Product_Codes
	 WHERE cCode1 = a.ProductCode1) AS cCode1
FROM (SELECT cProduct_id, cProduct_Name, cStatus_id,'000011'+LEFT(cProductKey_id,21) AS ProductCode1
	FROM Product
	WHERE cStatus_id = 'RELE' AND iAvail_id = iAvailGroup_id) AS a

It takes 5 seconds ("Clustered Index Scan") and uses the wrong index, it uses the index of another field.

Why is this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources