long running query runs ffine in QA
-
Hi,
I've got a query:
SELECT "OrderID" ,"SignOff" ,"AddedDate" FROM "MYDB"."MYTABLE"
Which when run in query analyzer takes all of 0:00 seconds. 2000 rows in the table.
It's logging in as the worst offender so far with 10+ queries ranging from 10 seconds to 66 seconds duration from a profiler trace.
My only question, to start this out is, when a statement is prepared and queued for execution, is that when its 'timer' starts? Could these have been sitting in the queue waiting for other queries to finish?
Thanks!!
-
Your qeustion is not clear...
MohammedU
Microsoft SQL Server MVP -
possibly, the query will scan ( probably table ) thus any updates to that table will likely cause a delay in execution of the select. You already know the execution time varies so a profile trace will only confirm that. You need to monitor for blocking on your query. Scans are bad, as you've discovered, and will always give problems if you're updating that table. You could use dirty reads but there is a chance of inconsistent results.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Mohammed - I've added info in red above, which should clarify.
-
If one of these statements were being blocked, wouldn't they deadlock after a certain amount of time? I have a select statement with a time of 23 minutes in my list!
-
no blocking and deadlocks are very different .. you've got blocking I'd guess .. you have to speed up your updates and preferably stop table scanning on selects. Failing that try dirty reads.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
A little more info on the subject. The application is a ms access front end, supplied by a vendor. One of the users has said that sometimes they want to get out of the program, but the screen goes grey and they can only minimize it. I didn't seem to have her full attention, and this is the best description I could get out of her. Another person in their department is going to take a look at it and get back to me.
Any MS Access related issues that could be causing this that anyone knows of?
-
Ok, let us know what transpires.
-
How are the tables linked in access? I know I've run into problems with tables and ms access locking schemes. Dynaset, read-only, etc. (can't remember them all). I would look at the queries they are running on access and see if the updates could be turned into pass-through queries.
Tom
-
you couldn't probably choose a worse front end - can't say that I would expect to be able to compare quereis in qa vs queries in access ( it can do some aweful things )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I can't get access to access yet, but I've been told it's an mdb, not an adp. I'm going to have to remote into a user box - so I'll keep you posted.
-
I couldn't believe we bought an access solution (I'm new here, about 2.5 months)!! For some reason the idea of a company selling an access front end just makes me laugh.
The saga continues - I now have a few selects in the 130-530 second range and the second runner up @ 1028 seconds. The king is now an odbc call to the archive version of this db @ 5867 seconds.
-
6000 seconds???
I bet you can shorten it up to less than 10 on sql server... maybe even less than one.
Keep us posted on that one too
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply