1

I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together. What happens is the query does not run within the same time frame consistently. If I run the query on a new connection in SSMS it takes about 45 seconds to complete. After it completes if I hit the execute button again it takes almost 4 minutes to complete. If I then open a new query (connection) and execute the query it takes about 45 seconds again. These queries are being run from the server and I can consistently reproduce this.

Any help on what is going on and how to fix is helpful.

Improve this question
3
  • 1
    suggest you to look into the difference between execution plans for - when the query runs fast and when it runs slow. Are stats and indexes up-to-date ?
    – Kin Shah
    Commented May 7, 2014 at 17:26
  • 1
    Have you looked at execution plans for the "fast" and "slow" versions? Are they exactly the same? If not, therein lieth your answer. Check this answer for some real gold: dba.stackexchange.com/a/60181/10832
    – Hannah Vernon ♦
    Commented May 7, 2014 at 17:27
  • @Kin Yes the indexes and stats are up to date.I will compare the query plans. Thanks for the ideas.
    – Codingrunr
    Commented May 8, 2014 at 20:21

1 Answer 1

Reset to default
1

With that many rows, moving the results from the server to the client will be a significant part of the elapsed time. Eliminate variablity in that part by wrapping your query in some way such as:

select
    count(*)
from (
    {your query here}
) as xx

This way the network transfer will be a minimal part of the elapsed time and you can eliminate it from your enquiries.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.