on 2011 Sep 19 3:22 AM
I know this sounds weird but this is a SELECT
query and it only happens on the deployment site, I cannot reproduce it on my local machine.
First time I run the query against database, it takes 25 to 30secs and the second time ~13secs (±2secs), the third time 25secs again, so the cycle goes on. It's exactly the same query every time.
Do you have any idea how or why this could happen? Maybe because of disk fragmentation? Or for some reason the cache "invalidates" itself after the second run? (this was a wild one) Unfortunately I cannot expose the full query but its format is like this:
WITH SubQuery (...) as (SELECT ..., row_number() over (...) FROM ....) WHERE .... SELECT ... FROM SubQuery WHERE ...
Note: No other processes were interacting with the db at the time of test.
Request clarification before answering.
You haven't posted enough details for us to help you. Some ideas:
Attaching graphical plans with statistics for the problem query (for both cases) would be a good idea.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.