SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOT makes query never complete?


NOT makes query never complete?

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217615 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


holyforce
holyforce
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217615 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24919 Visits: 12464
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217615 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search