Post reply

Performance issue with view

  • May 28, 2014 at 3:05 am

    #396824

    I have a view which has joins with multiple tables and have some complex logic. I am facing weird performance issue. We use this view to retirve information based on business date. Now for one particular date in one particular environment it is running infinitely. For other dates it's working fine. Also the date for which it is giving issues, it's working fine in different environment. I've updated statistics and defragmented all the underlying tables as well but no benefit. One more thing - While running the body of the view for the problem date, it works fine. It's just that view is running infinitely for so much time. Any guesses where to look out for ?

  • May 28, 2014 at 4:44 am

    #1716955

    try sp_refreshview as a start.

    I'm assuming when you say 'infinite' you mean a long time. Does it actually return with the data you need or does the query finish and not return anything?

    I would be interested to see the 2 actual queries you run from ssms.

    1 - the query with the view

    2 - the query with the body of the sql but not in a view (the one you say works)

  • May 28, 2014 at 4:52 am

    #1716958

    I tried refreshing view as well. One more thing i noticed now that it's working fine when running without where clause but issue remains when run with where condition. Where condition contains just one condition to check business date. Our infrastructure is such that though without where condition it will give records for same business date but to be on the safer side we have to keep it.

  • May 28, 2014 at 6:20 am

    #1716984

    The addition of a WHERE clause frequently changes the execution plan even if the same data is returned. Compare the two execution plans (one with and one without the WHERE clause) and see what's up.

    If you can't suss the problem from there, then post what you have IAW the second link under "Helpful Links" in my signature line below so that folks have a better chance of helping you figure this out.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 28, 2014 at 12:09 pm

    #1717117

    Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.

    I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:

    select * from view_impartant where business_date = 20131231

    -- This is causing issue

    Now when we run it like this:

    declare @business_date int

    select * from view_impartant where business_date = @business_date

    -- This runs perfectly

    Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

  • May 28, 2014 at 1:16 pm

    #1717135

    sqlnaive (5/28/2014)

    Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.

    I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:

    select * from view_impartant where business_date = 20131231

    -- This is causing issue

    Now when we run it like this:

    declare @business_date int

    select * from view_impartant where business_date = @business_date

    -- This runs perfectly

    Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

    This isn't part of the performance issue but why do you store date information as an int? You should use the date datatype. It provides instant validation and you don't have to fight sargability and all sorts of other issues because of incorrect datatypes.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 28, 2014 at 6:12 pm

    #1717198

    sqlnaive (5/28/2014)

    Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.

    I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:

    select * from view_impartant where business_date = 20131231

    -- This is causing issue

    Now when we run it like this:

    declare @business_date int

    select * from view_impartant where business_date = @business_date

    -- This runs perfectly

    Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

    From what I've seen, UNION will almost certainly cause a sort because it also does an implicit DISTINCT. In order to more quickly figure out the distict values, it's better to sort the data first and simply scan the data for changes than it is to "memorize" each value and see if it already exists in the "memorized values".

    I also agree about the INT dates. It makes it impossible to do certain date calculations easily without either an implicit or explicit conversion.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 1, 2014 at 4:12 pm

    #1718092

    The similar issue occurs due to parameter sniffing. You may find many explanations about parameter sniffing and different ways to handle it. One of the method is to use "OPTMIZE FOR UNKNOWN" hint.

  • July 9, 2014 at 10:14 am

    #1728138

    Thanks Ankush. If I ever get such chance again, wil test this option. 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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