August 11, 2011 at 12:33 pm
I'm in the process of building a query and getting some whacky results that I don't know how to explain and/or prevent. Your help is appreciated...
I have one stipulation in my WHERE clause, and it is:
WHERE CUST_CONTACTS >=1
When I run it this way, it finishes within 45-60 seconds.
Now, to get to the other side of that, WITHOUT ALTERING ANYTHING ELSE BUT:
WHERE CUST_CONTACTS =0
--or--
WHERE CUST_CONTACTS <1
When I run it either of those two ways, it runs and runs and runs and I usually end up killing it after 10 minutes.
I tried for grins WHERE CUST_CONTACTS IS NULL, that finishes quickly, but obviously doesn't return my results.
The query is extracting this data from a view, and the column type is a BIGINT.
Thoughts? Suggestions?
August 11, 2011 at 12:43 pm
Well, this might be silly to ask, but is the >=1 returning only a few records while the other options return many more records? i.e, is your first query very restrictive while the others are not? are statistics up to date?
can you do a "show execution plan" for both queries and let us know the difference?
August 11, 2011 at 12:44 pm
Let me add that it is a subquery, one of 2 subqueries in a query.
And running just the subquery it will run fine, but running the parent query, is when it stalls out.
That >=1 changed to <1 or =0 is the only change being made.
August 11, 2011 at 12:45 pm
NJ-DBA (8/11/2011)
Well, this might be silly to ask, but is the >=1 returning only a few records while the other options return many more records? i.e, is your first query very restrictive while the others are not? are statistics up to date?can you do a "show execution plan" for both queries and let us know the difference?
Yeah, looking at the execution plans will definitely answer your question. If they aren't different, it is probably purely because of the amount of data it has to return.
August 11, 2011 at 12:47 pm
Good question on the results size...
The >=1 is actually 4x the volume of the <1 or =0.
August 11, 2011 at 12:48 pm
ah, so you tried to trick us! not very nice... how about sharing the whole query and having a look a the execution plans for both?
so when you run just the subqueries, do you get a vastly different amount of records? are you then joining those with another set of data?
August 11, 2011 at 1:25 pm
Wasn't trying to fool anyone, sorry about that! 😀
I'm not smart enough with SQL to understand the differences between the execution plans, but there ARE differences, that's for sure! Amazing with the only thing changing being the comparison operator for a single column! Not sure why in the world that would alter everything else - seems like just the execution plan should be the same and the results would just be different.
So here goes, my SQL ain't pretty or the most efficient, but I try! 😉
WORKING VERSION:
________________
SELECT
b.COLUMN1,
b.COLUMN2,
b.COLUMN3,
b.COLUMN4,
CAST(COUNT(b.COLUMN5) AS NUMERIC(9,0)) AS COLUMN5,
b.COLUMN6
FROM
(SELECT
COLUMN7,
COLUMN8
FROM
CD_P.dbo.v_cust_cont_smry
WHERE
COLUMN9='201008'
AND COLUMN8 >=1
) a
LEFT JOIN
(SELECT COLUMN5, COLUMN7, COLUMN1, COLUMN2, COLUMN3,
(CASE
WHEN CAST(COLUMN4 as numeric(9,0)) <='30' THEN '0-30'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '31' AND '90' THEN '31-90'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '91' AND '180' THEN '91-180'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '181' AND '365' THEN '181-365'
WHEN CAST(COLUMN4 as numeric(9,0)) >='366' OR COLUMN4 IS NULL THEN '366+'
END) AS COLUMN4,
(CASE
WHEN CAST(COLUMN4 as numeric(9,0)) <='30' THEN '1'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '31' AND '90' THEN '2'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '91' AND '180' THEN '3'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '181' AND '365' THEN '4'
WHEN CAST(COLUMN4 as numeric(9,0)) >='366' OR COLUMN4 IS NULL THEN '5'
END) AS COLUMN6
FROM CD_P.dbo.t_cust_201008) b
ON a.COLUMN7=b.COLUMN7
WHERE b.COLUMN1 IS NOT NULL
AND b.COLUMN3 IS NOT NULL
AND b.COLUMN2 IS NOT NULL
GROUP BY b.COLUMN1, b.COLUMN2, b.COLUMN3, b.COLUMN4, b.COLUMN6
ORDER BY b.COLUMN1, b.COLUMN2, b.COLUMN3, b.COLUMN6
********************************************
NON-WORKING VERSION
____________________
SELECT
b.COLUMN1,
b.COLUMN2,
b.COLUMN3,
b.COLUMN4,
CAST(COUNT(b.COLUMN5) AS NUMERIC(9,0)) AS COLUMN5,
b.COLUMN6
FROM
(SELECT
COLUMN7,
COLUMN8
FROM
CD_P.dbo.v_cust_cont_smry
WHERE
COLUMN9='201008'
AND COLUMN8 =0
) a
LEFT JOIN
(SELECT COLUMN5, COLUMN7, COLUMN1, COLUMN2, COLUMN3,
(CASE
WHEN CAST(COLUMN4 as numeric(9,0)) <='30' THEN '0-30'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '31' AND '90' THEN '31-90'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '91' AND '180' THEN '91-180'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '181' AND '365' THEN '181-365'
WHEN CAST(COLUMN4 as numeric(9,0)) >='366' OR COLUMN4 IS NULL THEN '366+'
END) AS COLUMN4,
(CASE
WHEN CAST(COLUMN4 as numeric(9,0)) <='30' THEN '1'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '31' AND '90' THEN '2'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '91' AND '180' THEN '3'
WHEN CAST(COLUMN4 as numeric(9,0)) BETWEEN '181' AND '365' THEN '4'
WHEN CAST(COLUMN4 as numeric(9,0)) >='366' OR COLUMN4 IS NULL THEN '5'
END) AS COLUMN6
FROM CD_P.dbo.t_cust_201008) b
ON a.COLUMN7=b.COLUMN7
WHERE b.COLUMN1 IS NOT NULL
AND b.COLUMN3 IS NOT NULL
AND b.COLUMN2 IS NOT NULL
GROUP BY b.COLUMN1, b.COLUMN2, b.COLUMN3, b.COLUMN4, b.COLUMN6
ORDER BY b.COLUMN1, b.COLUMN2, b.COLUMN3, b.COLUMN6
****************************************
As you can see, the only thing to change is line 16:
Working version: AND COLUMN8 >=1
Non-Working version: AND COLUMN8 =0
August 11, 2011 at 1:29 pm
If the execution plans are different, it means that SQL Server's optimizer decided (based on your predicate) to use a different way to get the results. That could be because of indexes or the range of values in that column...
Is that field (Col 8) included in any index?
August 11, 2011 at 1:34 pm
Please post both plans. We can speed your way through that.
Otherwise you can read this whole, excellent, and free book about the subject.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply