Post reply

Perfromance query tuning help

  • January 14, 2012 at 4:20 pm

    #249617

    Hi,

    I have attached my query and execution plan. I have tuned the query according to suggested by the tuning advisor. I have also updated statistics on all the tables. Tables are quite big but at the end it returns only 159 rows in 16s. Since this is called through app, this need to be returned within in 10s.

    I am completely new to this execution plan. Any help on how to optimize this would be much appreciated.

    Thanks in advance for your help.

  • January 15, 2012 at 3:52 am

    #1433795

    Two problems immediately.

    1) You have nolock everywhere. Either no one cares about the accuracy of the data or no one knows that nolock is not a go-faster switch. (see this other thread where someone's using nolock... http://www.sqlservercentral.com/Forums/Topic1234650-391-1.aspx)

    2) dbo.uf_ConvertDatetime(SROSCHRS.RSHODT,0) <= convert(varchar, getdate(), 101)

    That's a non-sargable predicate, UDFs are generally slow and you're converting to varchar without defining its length. Do you know what the default length is? What is that supposed to do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 15, 2012 at 8:13 am

    #1433829

    I think you nailed it...Removing the ufds on the where clause speed up the process. Thanks a a lot for your help.

  • January 15, 2012 at 8:16 am

    #1433830

    Pleasure.

    Make sure you sort those nolocks out as well unless your users don't care about accuracy of data.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2012 at 5:02 am

    #1434513

    Just so you know, you're venturing into a huge topic area. In my signature line are two books that might help. The one on execution plans is available in electronic form for free.

    "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

Viewing 5 posts - 1 through 5 (of 5 total)

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