why a select statement runs slow?

  • zxmgh

    SSC Eights!

    Points: 911

    Hi,

    I have an application runs slow. I know that is is caused by a select statement which runs mutliple times in the application. I run the SQL Profiler trace and I found that average duration of the select statment is 5 seconds and read is about 20 million and write is around 40. My question is why the select statement need physical disk write?

    Thanks

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    SQL Server probably has to use tempdb to process your query. 

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • zxmgh

    SSC Eights!

    Points: 911

    How to speed up the query, does that mean I don't have enough memory?

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    We can try to help you tune your query but we'll need a few things.

     

    Table definition with indexes, the query itself and the execution plan if possible.  I'm sure we can figure something out.

  • zxmgh

    SSC Eights!

    Points: 911

    I will post the query in a short time. One thing I need to mention is if we reboot the db server, the same query runs about 0.01 seconds, but after one day or two, the performance of the query gradually downgrade and it will take 5 second to retuern the same amount of rows.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Sounds like the plan of the query needs to be rebuilt... or the indexes and stats need work.

     

    Have you tried recompiling the SP when it starts running slow??

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

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