Post reply

Significant performance dropping when use CASE statement

  • October 15, 2014 at 4:38 am

    #1752450

    Thank you, Sean

    It comes back as VARCHAR for a DataType column

  • October 15, 2014 at 4:51 am

    #1752459

    You can use SQL Sentry Plan Explorer. This allows you to anonymize the plan.

    Does IIF have the same performance impact?

    SELECT A.City_Name, B.Country_Code, IIF(B.Country_Area = 'EUR', 1, 0) AS Country_Area

    FROM Cities A

    JOIN Countries B

    ON B.Country_Id = A.Country_Id

    WHERE B.Country_Type='ABC';

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • October 15, 2014 at 5:01 am

    #1752464

    While you might have seen a minor improvement with forcing an index, that's a very bad choice most of the time for tuning. Let the optimizer do it's job. If you're hitting issues, there's usually something wrong with your code or your structure. Taking control away from the optimizer is very seldom a good solution.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 15, 2014 at 9:03 am

    #1752583

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

  • October 15, 2014 at 9:21 am

    #1752597

    BOR15K (10/15/2014)

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

    Quick questions:

    1) Is the Country_Code column by any chance NVARCHAR?

    2) If you change " CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area" to

    "CASE WHEN B.Country_Area = 'EUR' THEN 1 ELSE 0 END AS Country_Area", do you get the same performance?

    😎

  • October 15, 2014 at 9:53 am

    #1752612

    Eirikur Eiriksson (10/15/2014)

    BOR15K (10/15/2014)

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

    Quick questions:

    1) Is the Country_Code column by any chance NVARCHAR?

    2) If you change " CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area" to

    "CASE WHEN B.Country_Area = 'EUR' THEN 1 ELSE 0 END AS Country_Area", do you get the same performance?

    😎

    1.Country_Code is VARCHAR

    2. Same performance issue.

    Thank you

  • October 15, 2014 at 10:04 am

    #1752615

    BOR15K (10/15/2014)

    Eirikur Eiriksson (10/15/2014)

    BOR15K (10/15/2014)

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

    Quick questions:

    1) Is the Country_Code column by any chance NVARCHAR?

    2) If you change " CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area" to

    "CASE WHEN B.Country_Area = 'EUR' THEN 1 ELSE 0 END AS Country_Area", do you get the same performance?

    😎

    1.Country_Code is VARCHAR

    2. Same performance issue.

    Thank you

    Now I'm really getting interested, have seen adverse effects caused by implicit conversions, now that we can rule those out, the next question is did you have a chance to compare the actual execution plans?

    😎

  • October 15, 2014 at 11:07 am

    #1752634

    BOR15K (10/15/2014)

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

    Again, we're just taking stabs in the dark without being able to see stuff, but, no, not likely. A view is just a query. Putting the view together with a table is effectively putting it together with a derived table. No real difference for the optimizer. Why would it help in this case? I just don't know without any evidence.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 15, 2014 at 11:49 am

    #1752651

    Grant Fritchey (10/15/2014)

    BOR15K (10/15/2014)

    Apologies for the complete blunt question.

    I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

    and all works like a charm now. Is it OK to assume that the issue is a way SQL's optimiser processes table vs view?

    Thanks

    Again, we're just taking stabs in the dark without being able to see stuff, but, no, not likely. A view is just a query. Putting the view together with a table is effectively putting it together with a derived table. No real difference for the optimizer. Why would it help in this case? I just don't know without any evidence.

    I agree with Grant on this, it shouldn't make any difference especially when these are such a trivial operators. Just to make certain, is the case statement the only alteration to the query?

    😎

  • October 16, 2014 at 10:33 am

    #1752920

    In the execution plan with the case clause, is it determining the 'EUR' vs. other condition before or after the join and where conditions. If it is doing it before and if there is no index on that field it would have to look at every row to sort them out (I don't know why the optimizer would choose to do that but just taking a stab in the dark here). If that's the case, try coercing the optimizer into doing the condition after the join and where. Perhaps something like:

    SELECT City_Name, Country_Code, CASE Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area

    FROM

    (

    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'

    ) tbl

    (that's just a hasty attempt -- there's probably a better way to coerce it)

    - Les

  • October 16, 2014 at 1:36 pm

    #1752951

    From a purely performance stand-point, we'd almost certainly want to process Countries prior to it being JOINed:

    SELECT A.City_Name, B.Country_Code, B.Country_Area

    FROM Cities A

    INNER JOIN (

    SELECT Country_Code, CASE Country_Area WHEN 'EUR' THEN 1 ELSE 0 END AS Country_Area

    FROM Countries

    WHERE Country_Type = 'ABC'

    ) AS B ON B.Country_Id = A.Country_Id

    [Btw, yes, I am fanatical about putting the "INNER" in "INNER JOIN". For one thing, hints can only be specified when INNER is used.]

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 16, 2014 at 1:42 pm

    #1752952

    ScottPletcher (10/16/2014)

    From a purely performance stand-point, we'd almost certainly want to process Countries prior to it being JOINed:

    SELECT A.City_Name, B.Country_Code, B.Country_Area

    FROM Cities A

    INNER JOIN (

    SELECT Country_Code, CASE Country_Area WHEN 'EUR' THEN 1 ELSE 0 END AS Country_Area

    FROM Countries

    WHERE Country_Type = 'ABC'

    ) AS B ON B.Country_Id = A.Country_Id

    [Btw, yes, I am fanatical about putting the "INNER" in "INNER JOIN". For one thing, hints can only be specified when INNER is used.]

    Scott, I don't think you guaranteed anything with that refactor, right? Everything in it is commutative (I think), so the optimizer can move all of those operations up or down the plan without affecting the correctness of the entire SELECT.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 16, 2014 at 2:08 pm

    #1752960

    ScottPletcher (10/16/2014)

    From a purely performance stand-point, we'd almost certainly want to process Countries prior to it being JOINed

    Ultimately in absence of real data we're all just shooting in the dark, anyway. Worst case is the 'EUR' comparison is done with no index after the join but before the where clause since that is likely to be a large number of rows. Doing it on the Country table after filtering by the where clause (as one hopes your example would work out) is probably the best case. But both of those scenarios sort of implicitly assume the join will have more rows than the countries table (as it would if the cities table contained all cities for each country) but since his filtered join returned only two rows, that's probably not what the cities table is like so the join may primarily reduce the number of entries which may make leaving the comparison until last as the best bet (even without an index, doing the 'EUR' comparison on two rows is likely to be speedy). But all this is just speculation so I suppose we might as well be arguing the best way to count the number of unicorns on the head of a pin.:-D

    - Les

  • October 16, 2014 at 2:11 pm

    #1752963

    Haven't seen that OP had found a solution already.

    Alex Suprun

  • October 16, 2014 at 2:14 pm

    #1752965

    The single biggest factor for performance is to get the best clustered index on every table.

    Then adjust the non-clustered indexes as required.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply