none
sort performance issue on sql 2012 RRS feed

  • Question

  • Consider the following query...

    select tbl1.col1, vw1.col2, vw2.col3, tbl2.col4, dbo.canUse('Admin', 1, tbl1.key, 'user') as reader

    from tbl1

    left join vw1 on vw1.fkey = tbl1.key 

    left join vw2 on vw2.fkey = tbl1.key 

    left join tbl2 on tbl2.fkey = tbl1.key 

    where

    dbo.canUse('Admin', 1, tbl1.key, 'user') = 2

    order by tbl1.col1

    OFFSET 100 ROWS FETCH NEXT 51 ROWS ONLY

    ----------------------------------------------------

    vw1 and vw2 are views that join many to many tables to the detail.

    When I run the above query the result comes back instantaneously. This is also true if I am to sort on any column from tbl1. However, if I sort on columns from vw1, vw2, or tbl2, it takes more than 30 seconds for the result to come back.

    Also, if I was to remove the function completely from the query then sort on all tables and views would be instantaneous.

    Please note, I need sort and offset for this query.

    What do I need to do to make this query sorted by vw1.co2 or vw2.col3 or tbl2.col4 respond as fast as when sorting on tbl1.col1?

    Thanks in advance

     
    Wednesday, April 20, 2016 2:36 AM

Answers


  • You should rewrite your user-defined scalar function "dbo.canUse" as a table function or see if you can use normal tsql logic to replace it.

    This is a very common problem where your scalar function is forcing you to process row by row instead of using set based processing. To verify this, you can start a Profile trace/Extended Event session to monitor you running this process and you will see a row for every time the scalar function is evaluated which will be for every row that returns data.



    Wednesday, April 20, 2016 5:16 AM
  • Three answers, three appraoches. John Sterrett is right on the money. The culprit is the scalar user-defined function. Avoid scalar functions with data access. The are called for each row, and only the call to function comes with an overhead. But not only, since the optimizer has no clue how expensive it is, it may get the idea to evaluate the function for all rows before the sorting and gettings rows 51 to 100. Now it becomes really horrible.

    Wednesday, April 20, 2016 7:47 AM

All replies

  • Consider the following query...

    select tbl1.col1, vw1.col2, vw2.col3, tbl2.col4, dbo.canUse('Admin', 1, tbl1.key, 'user') as reader

    from tbl1

    left join vw1 on vw1.fkey = tbl1.key 

    left join vw2 on vw2.fkey = tbl1.key 

    left join tbl2 on tbl2.fkey = tbl1.key 

    where

    dbo.canUse('Admin', 1, tbl1.key, 'user') = 2

    order by tbl1.col1

    OFFSET 100 ROWS FETCH NEXT 51 ROWS ONLY

    ----------------------------------------------------

    vw1 and vw2 are views that join many to many tables to the detail.

    When I run the above query the result comes back instantaneously. This is also true if I am to sort on any column from tbl1. However, if I sort on columns from vw1, vw2, or tbl2, it takes more than 30 seconds for the result to come back.

    Also, if I was to remove the function completely from the query then sort on all tables and views would be instantaneous.

    Please note, I need sort and offset for this query.

    What do I need to do to make this query sorted by vw1.co2 or vw2.col3 or tbl2.col4 respond as fast as when sorting on tbl1.col1?

    Thanks in advance

     
    • Merged by Olaf HelperMVP Wednesday, April 20, 2016 7:37 AM Same question from same OP
    Wednesday, April 20, 2016 2:33 AM

  • You should rewrite your user-defined scalar function "dbo.canUse" as a table function or see if you can use normal tsql logic to replace it.

    This is a very common problem where your scalar function is forcing you to process row by row instead of using set based processing. To verify this, you can start a Profile trace/Extended Event session to monitor you running this process and you will see a row for every time the scalar function is evaluated which will be for every row that returns data.



    Wednesday, April 20, 2016 5:16 AM
  • You should rewrite your user-defined scalar function "dbo.canUse" as a table function or see if you can use normal tsql logic to replace it.

    This is a very common problem where your scalar function is forcing you to process row by row instead of using set based processing. To verify this, you can start a Profile trace/Extended Event session to monitor you running this process and you will see a row for every time the scalar function is evaluated which will be for every row that returns data.

    Wednesday, April 20, 2016 5:23 AM
  • If views vw1 and vw2 qualifies the conditions for indexed view you can try adding an index on required columns so that data will be persisted and it will take much less time to sort the records

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 20, 2016 5:31 AM
  • duplicate

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/06e3cb06-ac88-4546-98cd-9e1e858b1ec9/sort-performance-issue-on-sql-2012?forum=transactsql

    please dont crosspost


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 20, 2016 5:51 AM
  • As you mention that the above query gives result instantaneously, so there is no issue with with used of dbo.CanUse in the select statement (However you should avoid it because it forces row by row processing rather then set based).

    If i would have to faced the above problem, i would have created a nested CTE for vw1,vw2 and then sort the result depending on need. Use of CTE has save me previously while dealing with almost the same scenario.

    Wednesday, April 20, 2016 6:28 AM
  • Three answers, three appraoches. John Sterrett is right on the money. The culprit is the scalar user-defined function. Avoid scalar functions with data access. The are called for each row, and only the call to function comes with an overhead. But not only, since the optimizer has no clue how expensive it is, it may get the idea to evaluate the function for all rows before the sorting and gettings rows 51 to 100. Now it becomes really horrible.

    Wednesday, April 20, 2016 7:47 AM