March 15, 2012 at 12:19 pm
Hi All
I run a query that runs 54 sec.If i run it again (without changing anything) it runs 94 sec.Then 54 sec,then 420 sec,then 52 sec......Any idea what can cause this.It is on test server.
March 15, 2012 at 12:22 pm
Are you executing any Stored procedure with param? check the execution plan also for any clue.
March 15, 2012 at 12:26 pm
here is my query:
DECLARE @S GEOMETRY
SET @S = ( SELECT geom
FROM dbo.SHAPE WHERE ID=1
)
SELECT TAB_ID
FROM dbo.TAB
WHERE @S.STIntersects(GEOMETRY::Point(X / 1000000.0,
Y / 1000000.0,
@S.STSrid).STBuffer(0)) = 0
AND Tab_TIME >= '2012-01-15 00:00:00.0'
AND Tab_TIME <= '2012-01-30 23:59:59.0'
AND Tab_STATUS_ID != 41
March 16, 2012 at 8:22 am
Sure there are lots of things that can cause this:
Other processes running on the server
Having to go to disk instead of memory to retrieve the data
Network latency
A better measure of what's taking place on the SQL Server itself (to eliminate network and client side issues) is to use SET STATISTICS TIME ON and see what the CPU TIME and EXECUTION TIMES are. Odds are they are staying fairly static, especially CPU TIME
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply