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
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
I understand it may be difficult to assist me without knowing my underlying schema and all the details. Let me begin by asking in general:
I have a query that sums up some sales by customer number and product type, EXCLUDING 3 customers, something like:
SELECT  customers.cus_no, sales.prod_type, SUM(sales.total_sales)
FROM dbo.sales
INNER JOIN dbo.customers
ON customers.cus_no = sales.cus_no
WHERE sales.date = 20130131
AND customers.cus_no NOT IN ('1','2','3')
group by cus_no, prod_type


So you can see customers 1, 2 and 3 are excluded.
But say if I wanted customers 4 and 5 to be in the report, but only for the 'Bike' product_type, I did this:
SELECT  customers.cus_no, sales.prod_type, SUM(sales.total_sales)
FROM dbo.sales
INNER JOIN dbo.customers
ON customers.cus_no = sales.cus_no
WHERE sales.date = 20130131
AND customers.cus_no NOT IN ('1','2','3')
AND NOT ( customers.cus_no IN ( '4','5')
AND sales.prod_type <> 'Bike'
)
group by cus_no, prod_type



I run this, and the query never completes. It runs forever and ever.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87223 Visits: 45272
Is there a question here?

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
I apologize, I thought the question was implied. Surely was to all my co-workers.

Why does the second statement run forever? Is there a better way to achieve what I'm trying to add onto the second statement, that will work?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87223 Visits: 45272
No way to tell without seeing the indexes and exec plan. Probably the indexes don't support the additional predicates, possibly combined with row estimation errors (NOT is a little hard to estimate) produces a plan that's highly sub-optimal.

The NOT outside the bracket can be converted to the negative of the two conditions inside, with an OR rather than an AND, and ORs require quite different indexing than ANDs do.

WHERE sales.date = 20130131
AND customers.cus_no NOT IN ('1','2','3')
AND NOT ( customers.cus_no IN ( '4','5')
AND sales.prod_type <> 'Bike'
)

means (via De Morgan's laws)

WHERE sales.date = 20130131
AND customers.cus_no NOT IN ('1','2','3')
AND (customers.cus_no NOT IN ( '4','5') OR sales.prod_type = 'Bike')

which can further be expanded to

WHERE (sales.date = 20130131 AND customers.cus_no NOT IN ('1','2','3','4','5'))
OR (sales.date = 20130131 AND customers.cus_no NOT IN ('1','2','3') AND sales.prod_type = 'Bike')

That OR is probably what's messing the query up.

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
EDIT: I notice you're adding more to your post. I'll check it again - thanks!!

What is interesting is I can remove the additional NOT where clause in statement number 2, and move it into the SELECT statement as a CASE that evaluates to 1 or 0 to determine if it's that customer/number, product type combination.

I run this, and the new column works fine, and the statement runs quick.

Then I wrap it as a sub query and ask it to select * from that sub query table where the CASE statement column is 0 or 1 (either, pick one), and it also hangs indefinitely.

Such as:


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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87223 Visits: 45272
Because the optimiser works on the entire query in one go, not the subquery first and the outer query second, the two very likely simplify to the same form.

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
I'm not familiar with De Morgan's laws but your explanation of my situation using them made sense (end result the same to me).

For now, can you think of any way this statement can be written (without any special needs) that would potentially be more efficient despite the underlying schema?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87223 Visits: 45272
Without knowing the underlying indexes, not really. It's not the syntax, the optimiser's smart enough to convert the forms, it's going to be the indexes that make a difference.

Without knowing what you have, this is a guess, but I'd put one index on sales.date and another on sales.prod_type, sales.date and one on customers.cus_no. That would be a start, I'd use the exec plan to tweak from there.

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


ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7889 Visits: 7151
I suspect the dbo.sales should really be clustered by date. If so, and you fix that, the rest of the query won't hurt the performance that much one way or the other.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
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
Gentlemen, thank you for your assistance, esp. without knowing my schema.

I forced the join on two tables to be hashed, as the query plan was showing nested loop.

I am currently educating myself further on these internal join processes.

The query runs under a second now.

Thanks!!
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