Significant performance dropping when use CASE statement
-
Hello All,
Hope to get an idea what I can do to improve the following, please:
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
Anything you can advise what I should be looking for, please?
It is SQL 2012 SP1 with compatibility level set to 110
Much obliged!
-
Could you post the DDL (CREATE TABLE statements) for the tables?
-
Lynn Pettis (10/14/2014)
Could you post the DDL (CREATE TABLE statements) for the tables?Further on Lynn's request, the actual execution plans?
😎
-
Is that a constant? Does returning to the previous version solves the problem?
My guess is that the problem has to do with blocking or some kind of waits.
-
Thank you!
I have looked into the actual execution plan (I am not sure I can publish it here due to company's security policy).
What I have seen that with CASE it has Sort operation with a cost of 81% and then several Hash Matches.
Should I try to force an index, for example?
-
BOR15K (10/14/2014)
Thank you!I have looked into the actual execution plan (I am not sure I can publish it here due to company's security policy).
What I have seen that with CASE it has Sort operation with a cost of 81% and then several Hash Matches.
Should I try to force an index, for example?
You should be ok with "cities" and "countries" - unless you're an archaeologist and your firstname is Indiana 😉
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Forcing an index improved the performance from 03:55 to 01:46 only.
Anything else you could advise, please?
-
Can you post the full CREATE TABLE and CREATE INDEX statements?
-
Thank you, Ed
This is an issue - I am not allowed to publish any code for that specific customer.
This is why I am asking for guidelines only, as I understand no one can give me the right / full answer without seen the actual code.
-
Without seeing the table structures (columns and definitions) or the execution plan (which will show what is happening) not much we can do to help.
It could be implicit data conversions, bad indexing, or numerous other potential causes.
-
Lynn Pettis (10/14/2014)
Without seeing the table structures (columns and definitions) or the execution plan (which will show what is happening) not much we can do to help.It could be implicit data conversions, bad indexing, or numerous other potential causes.
I can't see how the change mentioned can be affected by indexing, data conversions, stale statistics or something similar. The change was made on the column list, no column was added and the "operations" are done on 2 rows.
Both queries should perform in the same amount of time, unless that something else in the server is affecting them.
-
Luis Cazares (10/14/2014)
Lynn Pettis (10/14/2014)
Without seeing the table structures (columns and definitions) or the execution plan (which will show what is happening) not much we can do to help.It could be implicit data conversions, bad indexing, or numerous other potential causes.
I can't see how the change mentioned can be affected by indexing, data conversions, stale statistics or something similar. The change was made on the column list, no column was added and the "operations" are done on 2 rows.
Both queries should perform in the same amount of time, unless that something else in the server is affecting them.
Hey, just throwing out guesses since we can't be shown anything.
-
Is B.Country_Area an Nvarchar field by chance? If so, try N'EUR' in your CASE statement.
Also, get the FULL XML QUERY PLANS from both executions and run them through a text comparison tool like WINMERGE. You can be assured that assuming this is a repeatable issue and not due to blocking or server getting hammered, etc. that there IS a meaningful difference in the details of the plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Quick thought, run both queries in the same query window with "Include Actual Execution Plan" turned on. Then compare the two plans and tell us what the difference is.
😎
-
What does this give you?
SELECTo.name AS TableName,
c.name AS ColumnName,
t.name AS DataType
FROM
sys.objects o
INNER JOIN
sys.columns c
ON o.object_id = c.object_id
INNER JOIN
sys.types t
ON c.user_type_id = t.user_type_id
WHERE
o.name = 'Countries'
AND c.name = 'Country_Area'
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply