Query Performance
-
I created table M_CUSTOMER table in the local sql server and ran the below query.
Select * from M_CUSTOMER
The above query has 200000 records.
The above query ouput is executed at different time gaps.
At first, it took 20 seconds
II it took 2 seconds,
III it took 2 seconds
IV it took 10 seconds
What could be the reason
With Kind Regards,M.S.Balavijay
-
Hi there,
It could be that the first time you were running the statement, the execution plan was being created and then subsequent queries were using the same execution plan.
However, it could be that other SQL processes or other processes on the box itself are consuming IO/CPU etc and causing the different query times.
Clive
-
Hi , yes i am here.
okay i understand what u said. now the problem is , i would like to know the exact execution time for the query . is it possible to find it ? and more over, first 3 times it gives approximatly same execution time. But if suppose i run some other query after executing the previous query , it varries.
With Kind Regards,M.S.Balavijay
-
Try running SQL Profiler when you run the query - you should be able to get exact execution times that way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
a large select ( * ) will always be problematic .. you need to clear your data cache before each run if you want a consistant timing. Data may or may not be in cache which could influence the execution time. The width of the table, the number of procs, indexes and all sorts may have an impact. I can't see that such a query would likely ever produce a consistant timing in a true production environment.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
As you are selecting all the records, you may have to look at the client machine's available memory and cpu usage. Try to run the query in SQL Server box itself, so that you can eliminate the network wait and client machine related issues.
-
Actually i am running in the local sql server itself . so there is no chance for network traffic. Moreover if i check with the time statistics, it will give the exact answer . Previously i was checking the time taken in the query analyser which will come in the right side bottom (task bar in the Query Analyser) of the window. It shows me the different values for each execution. Please clarify my doubt.
With Kind Regards,M.S.Balavijay
-
Hi,
I'd have to agree with previous replies. Your query is far to broad, you are selecting all columns and all rows. That means the only factor that is likely to be affecting your query is the environment.
I would suggest to remove all unnecessary processes from your machine and stop any unwanted services so that you are effectively running on a clean machine. See if you can get some consistent values.
The time you get from QA should be good enough.
HTH
Graeme
-
M.S.
It may help to 'start with the end in mind' as the management guys like to say. It would help if we know what you intend to do with the times.
Why do you need the exact time a query takes to run? If you are trying to determine exactly how long the query will take and you plan to schedule something or otherwise depend on the time staying the same, you may need to adjust your plan.
As Colin pointed out, the same query will take at least a slightly different amount of time every time it is run. Even on the server, the query will take longer if rows have been added since the last time it ran, or less time if rows have been deleted. Think of it this way, if you go to the store to get two cans of corn, it will take less time than if you go to get 200 cans.
To complicate things a bit, SQL Server will sometimes reuse a previous execution plan and sometimes will create a new one, especially if indexes are added to, or dropped from the table. Or if you update the statistics.
It occurs to me that we have assumed you know about execution plans. If not, you may reasonably expect the same code to run in the same length of time every time it is executed. This is not the case. SQL Server uses information about the tables (available indexes, number of rows, statistics about samples of the data, etc.) to decide how it will fetch the records. You tell it WHAT to fetch, it decides HOW to fetch it. The time it takes the query to run is dependent upon the number of rows and HOW the data is retrieved.
If we knew what you need to do with the time, we may be able to provide more help.
-
Dear Mr.Cjohnson & Mr.collins
I have a table named dbo.M_CUSTOMER in created database
and having 200000 records.
This table has 2 fields Emp_No and Emp_Name .
SQL server is the local one.
The problem is , I ran the query "select * from M_CUSTOMER"
First time , it got executed with in 10 seconds and
second time i executed it immediately , it took just 2 seconds.
again third time i did it, it took just 2 seconds.
But again i ran it , it took 24 seconds .
It varries often .
Mainly i am checking this for tunning the reports.
just i am estimating the time gap for the queries.Before that just i wished to run the simple query and get the time taken for execution. But it gets varried everytime. I wondered for this time mismatch. That's problem . Hopefully you can understand what happened and why i raised the query.
Thanks and Regards.,
Balavijay.M.S.
With Kind Regards,M.S.Balavijay
-
Run the following DBCC commands before running your benchmark query
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
These statements will bring your server to cold boot state and after that you know the worst time your query would take. For further technical details lookup in BOL for these statements.
-
This is certainly one of the more bizarre and exasperating threads on the go at the moment - suggestions for differences in performance have been posted and these seem perfectly plausible to me.
To paraphrase CJohnson's request: what practical reasons (ie not just the fact that you are puzzled) do you have for needing your query to run in exactly the same period of time every time? If you are running a number of processes which need to run in series, why not just create a job with a number of steps?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply