Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

NOT makes query never complete? Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 3:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
Join hints are a bad idea in most cases. Rather see why SQL's picking a loop join (probably low row estimations on one or both tables) and fix the cause.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414861
Posted Saturday, February 2, 2013 4:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 3:22 PM
Points: 6, Visits: 69
Low row estimation is the cause. It predicted 6k~ and ended up doing millions.

I guess the fix is a better index, key, join, etc. However, can you take a moment to tell me what is logically going on, and how the lack of one or more of those items makes this occur?
Post #1414972
Posted Sunday, February 3, 2013 1:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
Lack of what?

Yes, low row estimation, whatever the cause, will cause very inefficient query plans. Operators that are good on smaller row counts are terrible on larger ones.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414987
Posted Sunday, February 3, 2013 7:01 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
holyforce (2/1/2013)

SELECT  *
FROM
(
SELECT CASE WHEN cus_no IN ('1','2')
AND product_type <> 'BIKE' THEN 1 ELSE 0 END AS the_column
FROM [aforementioned tables]
) subq
WHERE the_column = 1

Run just the inner subquery, it runs quick. Run the whole statement, it locks up again.


It's most likely not exactly right.

Make sure you've got the whole recorset in your Management Studio window before you say "subquery runs quick".
Because you do not have any filter every row is gonna be displayed, so SSMS starts displaying resulting rows as they arrive.
It does not need to wait till the whole data set is processed.

Different story when you apply the WHERE clause.
Because there is no way any index can be used SQL Server needs to build the recordset from the subquery as a table in memory, and then apply the filter against it.
Post #1415083
Posted Monday, February 4, 2013 1:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
Sergiy (2/3/2013)
Different story when you apply the WHERE clause.
Because there is no way any index can be used SQL Server needs to build the recordset from the subquery as a table in memory, and then apply the filter against it.


Not how filters work. Sure, that's not something where SQL can seek for a value, but it can read from the index/table and apply the filter as it goes. Secondary filters are not a blocking operation, that's sorts, hash joins, hash aggregates, things where the entire resultset has to be available to work on.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1415168
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse