Post reply

Performance tune SS SP

  • January 19, 2011 at 3:36 am

    #1275172

    Hi,

    SQLs are taking 20 and 15 seconds respectively. I did try using both temp table and table variable no major difference.

    Thanks!

  • January 19, 2011 at 4:17 am

    #1275187

    Please post both of the plans as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


    Clear Sky SQL
    My Blog[/url]

  • January 19, 2011 at 4:27 am

    #1275189

    Hi,

    Please find the attached execution plans for those two SQL.

    Let me know you should need more details if any.

    Thanks!

  • January 19, 2011 at 4:36 am

    #1275193

    Dont look to bad...

    Can you post the "actual" plans though , you only posted the estimated


    Clear Sky SQL
    My Blog[/url]

  • January 19, 2011 at 4:39 am

    #1275196

    Please post the Actual execution plans, not the estimated execution plans.

    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 19, 2011 at 4:43 am

    #1275199

    Dave Ballantyne (1/19/2011)

    Dont look to bad...

    Depends. If the row estimation is off that key lookup is going to hurt.

    I think that the output from statistics IO would also be useful

    SET STATISTICS IO ON

    GO

    <Run query here>

    GO

    SET STATISTICS IO OFF

    GO

    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 19, 2011 at 4:47 am

    #1275202

    GilaMonster (1/19/2011)

    Depends. If the row estimation is off that key lookup is going to hurt.

    Agreed, just commenting that the overall 'shape' is pretty much inline with what i was expecting.


    Clear Sky SQL
    My Blog[/url]

  • January 19, 2011 at 7:49 am

    #1275318

    Would a single index on Latitude and Longitude help? It seems that the optimizer is doing a great job of using the indexes available, and perhaps a composite index might help by removing the hash join of the 2 index seeks.

    How often would you only search using latitude or longitude?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 19, 2011 at 8:02 am

    #1275326

    Hi,

    Attached is the actual execution plan for those two SQLs. Let me explain how I got these sql plan files.

    1. I click on "Include Actual Execution Plan'

    2. Ran SQL query

    3. After query execution nothing was appeared so I click on "Display Estimated Execution Plan"

    Another thing is I did try creating composite non-unique non-clustered index on latitude and longitude columns but that didn't help again.

    co_address is having around 5 million of records and I wish that insert run within 5 sec atmost. Please guys suggest me something.

    Thanks

  • January 19, 2011 at 8:07 am

    #1275327

    bhushan.bagul (1/19/2011)

    1. I click on "Include Actual Execution Plan'

    2. Ran SQL query

    3. After query execution nothing was appeared so I click on "Display Estimated Execution Plan"

    Then they are still estimated plans.

    What do you mean 'nothing appeared'? The actual execution plan displays in a separate tab, focus doesn't go there automatically.

    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 19, 2011 at 8:07 am

    #1275328

    Then it's still the estimated plan.

    I've never had the plan NOT appear after the queries were done running.

    Where are you looking for it? In the default settings of SSMS, you should get an extra tab besides the results.

  • January 19, 2011 at 8:27 am

    #1275336

    Oh! I didn't look that third tab "Execute Plan" after query execution.

    Here is actual exe plan for you guys.

  • January 19, 2011 at 8:37 am

    #1275352

    Try including the delete_flag to both of the indexes.

    Should resolve the nested loop lookup.


    Clear Sky SQL
    My Blog[/url]

  • January 19, 2011 at 8:49 am

    #1275361

    Kudos for you all guys!!!

    By adding adr_delete_flag in indexes it tooks only 2 seconds.

    Keep it up. And again thanks to you all.

  • January 19, 2011 at 1:55 pm

    #1275562

    Cool , glad you got there in the end.

    For the icing on the cake, you should change the scalar udf into an inline one too.


    Clear Sky SQL
    My Blog[/url]

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

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