WHERE Clause causing my sql to time out.

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 2005 Forums

Transact-SQL (2005)

WHERE Clause causing my sql to time out.

Author

Topic

ekellysql
Starting Member

16 Posts

Posted - 2008-01-18 : 12:58:49

I'm working with an existing sql statement. All I had to do was add two inactive record checks bolded below to the WHERE clause. When I do however the asp page timeouts. If I use just one of the inactive checks for just one of the tables then it works fine. Either table. Anyone have any idea? thanks

The sql is;

SELECT dbo.tblProdGroup.ProdGroupId, dbo.tblClientID.ClientName + ' - ' + dbo.tblProdGroup.ProdGroupCode AS ProdGroupCode
FROM dbo.tblProdGroup
INNER JOIN dbo.tblProducts ON dbo.tblProdGroup.ProdGroupId = dbo.tblProducts.ProdGroupId
INNER JOIN dbo.tblProductRestrictions ON dbo.tblProducts.ProductID = dbo.tblProductRestrictions.ProductId
INNER JOIN dbo.tblProductRestrictionType ON dbo.tblProductRestrictions.ProductRestrictionTypeId = dbo.tblProductRestrictionType.ProductRestrictionTypeId
INNER JOIN dbo.tblRepProductRestrictionTypes ON dbo.tblProductRestrictionType.ProductRestrictionTypeId = dbo.tblRepProductRestrictionTypes.ProductRestrictionTypeID
INNER JOIN dbo.tblClientID ON dbo.tblProdGroup.ClientID = dbo.tblClientID.ClientId
WHERE (dbo.tblProdGroup.Inactive = 0)

AND (dbo.tblProductRestrictions.Inactive = 0 )
AND (dbo.tblRepProductRestrictionTypes.Inactive=0)

GROUP BY dbo.tblProdGroup.ProdGroupId, dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode
ORDER BY dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-18 : 13:46:27

are those bit columns?

you are almost certainly forcing table scans of tblProductRestrictions and tblRepProductRestrictionTypes

looked at execution plan? (<ctrl> L)

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 13:48:40

yeah, those are bit fields. So, the scan of those two tables is proving too much work by the looks of the execution path. Any ideas of how I can change the sql to do what I need?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 15:30:01

are the fields u are joining on indexed? not much u can do to prevent table scan when where clause is on a bit column

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 17:42:46

thanks russell. I'll check out if any indexes are being used. This is some contract work and I don;t know the existing db design too well yet.

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 17:47:36

just checked the tables. There's an index on each of them.

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 18:10:55

I've also tried using NOLOCK after each table in the query. Didn't work.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 18:45:49

nolock hint is the most overused and usually worthless thing i see. it usually cant help but often can cause problems...

nuff of that...back to your problem...how many records in tblProductRestrictions table?

what if u try a correlated subquery...

...
JOIN (
select productId from tblProductRestrictions
where Inactive =0
) x
on ....

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 19:07:59

tblProductRestrictions has about 50,000 and tblRepProductRestrictionTypes has about 110,000

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 20:08:53

thats not so much to be timing out. when were indexes last rebuilt? try SET STATISTICS IO ON, execute query in management studio. post results (from messages pane) here. what is ASP timeout set to?

ekellysql
Starting Member

16 Posts

Posted - 2008-01-19 : 20:24:11

I added subqueries and tried this. It took 9 seconds to execute in query analayzer.

SET STATISTICS IO ON
SELECT dbo.tblProdGroup.ProdGroupId,
dbo.tblClientID.ClientName + ' - ' + dbo.tblProdGroup.ProdGroupCode AS ProdGroupCode
FROM dbo.tblProdGroup
INNER JOIN dbo.tblProducts ON dbo.tblProdGroup.ProdGroupId = dbo.tblProducts.ProdGroupId
INNER JOIN dbo.tblProductRestrictions ON dbo.tblProducts.ProductID = dbo.tblProductRestrictions.ProductId
INNER JOIN
(SELECT dbo.tblProductRestrictions.ProductId FROM dbo.tblProductRestrictions
WHERE Inactive = 0) As ProdRestrictions ON dbo.tblProducts.ProductID = ProdRestrictions.ProductId
INNER JOIN dbo.tblProductRestrictionType ON dbo.tblProductRestrictions.ProductRestrictionTypeId = dbo.tblProductRestrictionType.ProductRestrictionTypeId
INNER JOIN
(SELECT dbo.tblRepProductRestrictionTypes.ProductRestrictionTypeID, dbo.tblRepProductRestrictionTypes.RepId
FROM dbo.tblRepProductRestrictionTypes WHERE Inactive = 0)
as RepProdRestriction ON dbo.tblProductRestrictionType.ProductRestrictionTypeId = RepProdRestriction.ProductRestrictionTypeID
INNER JOIN dbo.tblClientID ON dbo.tblProdGroup.ClientID = dbo.tblClientID.ClientId
WHERE (dbo.tblProdGroup.Inactive = 0)
GROUP BY dbo.tblProdGroup.ProdGroupId, dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode
ORDER BY dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode

Gave;

(17 row(s) affected)

Table 'tblProductRestrictions'. Scan count 48905, logical reads 102358, physical reads 0, read-ahead reads 0.
Table 'tblProducts'. Scan count 276, logical reads 586, physical reads 0, read-ahead reads 0.
Table 'tblClientID'. Scan count 281, logical reads 581, physical reads 0, read-ahead reads 0.
Table 'tblProdGroup'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'tblProductRestrictionType'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0.
Table 'tblRepProductRestrictionTypes'. Scan count 1, logical reads 675, physical reads 0, read-ahead reads 0.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 21:14:56

ok, so tblProductRestrictions is the problem. need to figure out a way to reduce those reads. just for testing, go back to your original query, then comment out the line

AND (dbo.tblProductRestrictions.Inactive = 0 )

any way to get same filter without that line? perhaps with one of your existing joins?

ekellysql
Starting Member

16 Posts

Posted - 2008-01-20 : 23:19:04

ok, I can try and see if a join can do the trick. So why are the logical reads(102358) more than twice the scan count. Why would this happen?

ekellysql
Starting Member

16 Posts

Posted - 2008-01-21 : 22:53:47

so russell if you happen to read this thread again. I created a test db, copied the tables and their data over. Ran the query below andit did it in 1 second.

SET STATISTICS IO ON
SELECT dbo.tblProdGroup.ProdGroupId,
dbo.tblClientID.ClientName + ' - ' + dbo.tblProdGroup.ProdGroupCode AS ProdGroupCode
FROM dbo.tblProdGroup
INNER JOIN dbo.tblProducts ON dbo.tblProdGroup.ProdGroupId = dbo.tblProducts.ProdGroupId
INNER JOIN
(SELECT dbo.tblProductRestrictions.ProductID, dbo.tblProductRestrictions.ProductRestrictionTypeId FROM dbo.tblProductRestrictions
WHERE Inactive = 0) As ProdRestrictions ON dbo.tblProducts.ProductID = ProdRestrictions.ProductId
INNER JOIN dbo.tblProductRestrictionType
ON ProdRestrictions.ProductRestrictionTypeId = dbo.tblProductRestrictionType.ProductRestrictionTypeId
INNER JOIN
(SELECT dbo.tblRepProductRestrictionTypes.ProductRestrictionTypeID, dbo.tblRepProductRestrictionTypes.RepId
FROM dbo.tblRepProductRestrictionTypes WHERE Inactive = 0)
as RepProdRestriction ON dbo.tblProductRestrictionType.ProductRestrictionTypeId = RepProdRestriction.ProductRestrictionTypeID
INNER JOIN dbo.tblClientID ON dbo.tblProdGroup.ClientID = dbo.tblClientID.ClientId
WHERE (dbo.tblProdGroup.Inactive = 0)
GROUP BY dbo.tblProdGroup.ProdGroupId, dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode
ORDER BY dbo.tblClientID.ClientName, dbo.tblProdGroup.ProdGroupCode

Table 'tblProductRestrictions'. Scan count 1, logical reads 347, physical reads 0, read-ahead reads 0.
Table 'tblProducts'. Scan count 1, logical reads 274, physical reads 0, read-ahead reads 0.
Table 'tblProdGroup'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'tblClientID'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'tblProductRestrictionType'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0.
Table 'tblRepProductRestrictionTypes'. Scan count 1, logical reads 676, physical reads 0, read-ahead reads 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 02:15:56

Are the indexes update and defragmented in the original table?
Run DBCC to find out fragmentation level.

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-22 : 19:02:16

Have you tried getting it to stop using the index (I'm guessing that is the problem - way too many reads)?

Try
AND (dbo.tblProductRestrictions.Inactive * dbo.tblProductRestrictions.Inactive = 0 )
AND (dbo.tblRepProductRestrictionTypes.Inactive * dbo.tblRepProductRestrictionTypes.Inactive =0)

to get the same affect but make it look like an expensive early option to the optimiser.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources