Difference in Time for same query

Question

Difference in Time for same query

yuvipoy

SSChampion

Points: 10469
More actions
July 23, 2014 at 12:54 am

#309438

Query time having different timings
Start Time End Time
2014-07-23 12:10:28.4232014-07-23 12:10:28.643
Query
Select col1,col5,col8,col9,col10,col12,col14,col15,col16,col17,col18,col19,col20 from Table_name
Start Time End Time
2014-07-23 12:10:28.6572014-07-23 12:10:30.860
Select col1,col5,col8,col9,col10,col12,col14,col15,col16,col17,col18,col19,col20 from Table_name
The query is executed from the application and found that it is calling 2 times , so i need to cut short it to single call[only one time]
The above time are taken from profiler.
When i ran this in SSMS i am getting the output
SET STATISTICS TIME ON;
Select col1,col5,col8,col9,col10,col12,col14,col15,col16,col17,col18,col19,col20 from Table_name
SET STATISTICS TIME OFF;
SQL Server Execution Times:
CPU time = 304 ms, elapsed time = 1656 ms.
Total records in the table is 20k, no where condition
1) Why there is difference in Q1 220 millisecond and Q2 2203 millisecond?
2) Is Q1 220 millisecond is a valid one, since when i ran in SSMS i am getting 1656 millisecond.
Thanks!

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

All things being equal between the two query executions, the difference would be pages loaded in cache. Hence a quick question, what happens if you flush the cache DBCC FREEPROCCACHE WITH NO_INFOMSGS before each execution?

😎

yuvipoy SSChampion Points: 10469 More actions · Answer 2

As i said it is from application, it is not possible to run this query for every execution.

but my question is if it is cache then second query time should be less compare to first one right but here it is different

after running

DBCC FREEPROCCACHE WITH NO_INFOMSGS

ran the application

First Time

Q1 2014-07-23 14:53:06.6372014-07-23 14:53:06.760

Q2 2014-07-23 14:53:06.7602014-07-23 14:53:08.650

Second time

Q1 2014-07-23 14:57:26.5802014-07-23 14:57:26.940

Q2 2014-07-23 14:57:26.9402014-07-23 14:57:29.190

Thanks!

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

If it's just doing scans (no WHERE clause), you're completely subject to any number of areas of contention, on the disk, in memory, on the CPU. You could be seeing blocking from other queries also running at the same time. Execution time can radically vary because of all these things, especially on a query that's reading everything.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

yuvipoy SSChampion Points: 10469 More actions · Answer 4

During the execution time no other process is running.

Only the application with this statement is running.

i checked more than 50 times each time i see there is less time in Q1 and more time in Q2

Q1 almost <200 millisecond and Q2 almost ~1800 millisecond every time

that is what my question too why Q2 is taking more time.

When i ran the same query in SSMS it is taking ~1800 millisecond

Q2 and SSMS are having same time then how Q1 alone with less time :w00t:

But both Q1 and Q2 query are from profiler only.

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Your first post shows Q1 and Q2 to be identical. If they are not, and you're obfuscating the code, then:

1. Why are you expecting them to execute in the same time?

2. How do you expect folks to help when you're not showing the queries?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

For the same server, with the same data, on the same database, with identical statistics, for a single execution, with absolutely zero contention, you should be seeing roughly the same execution time. So, there must be other factors that you can see that we can't, or you're not providing a complete picture, or both.

One possibility, although I'm not sure it would really affect such a simple query, is that the ANSI settings are different between your app and SSMS.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning