Post reply

Query performance?

  • August 22, 2012 at 8:39 am

    #260328

    Gurus,

    Need help with Query performance question.

    There are two dataregions in the report body.

    one tablix and one chart

    Tablix is just a raw data representation.

    chart is depiction of calculated/aggregated fields of varoius columns in the tablix above so there are say some 10 charts on report when run for say yesterday.

    now, both of the data regions use different stored procs sproc_chart and sproc_data and both the procs retrieve data from another proc sproc_origin.

    now the report is running very slow. Could it be because the Sproc_origin runs two times? that is already slow when run once.

    I have eliminated substrings, excess joins, tried creating nonclustered index on Identity column in the #table(not sure if its a good idea but had no choice because rest are varchar fields, did not help much, so deleted that) any ideas please.

    Thank you for your time !!

  • August 22, 2012 at 9:01 am

    #1528699

    I understand the offending "report" is created by several storedprocs which means several queries. If this is the case you have to figure out which query (or queries) are the ones spending most of the time. Trace the process, determine the big spenders, analyze them one by one.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 23, 2012 at 6:55 am

    #1529220

    thanks for the reply. will do so. How to check execution plan (navigation please) and how to interpret that? any good links here?

    thanks again

  • August 23, 2012 at 7:26 am

    #1529237

    Somewhere to start.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

  • August 24, 2012 at 6:41 am

    #1529685

    Thank you !! Well written and thought out.

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

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