Hi,
We have built a Windows VB.NET application using SQLServer 2000. Sometimes when we run a specific query via ExecuteNonQuery, they take very long to execute, for example a simple query like:
"UPDATE tbEmail SET EmailIcon = 'HospitalCaptured' WHERE EID = 19053"
EID is the primary key. This table has about 40 000 records. This query timedout in .NET - we pasted it in Query Analyser and it then took 11,5 minutes to execute. Once completed, all similar queries (with different primary key values) that are run after that, are executed immediately.
Does anybody know why this happens and what the solution is so that our .NET application does not timeout.
Thank you,
Lesego
Why do SQL Queries take long to execute ?
robinjam
Nitin could very well be right about the locks. Since the update is taking 11 minutes you should have no trouble checking this. Simply run an SP_LOCK in a separate query analyzer window while the update is running. This should show whether or not you've incurred a massive amount of lock contention.
Scott Chang
Please post the table structure, including constraints, indexes, triggers, etc. It will just save so much time. If you have a primary key constraint (in essence, a unique index) there is little way that that update could take any time at all, on any machine, especially with only 40000 rows, unless you have a trigger, or something you arent telling us about.
Also, try running the query in QA and seeing what it does. If it takes 11 microseconds, and the plan looks clean, then you can point your finger to the VB.NET app.
Lakshmi N
an update on table should not take such a long time given that there r only 40000 records and its on primary key(where clause)..most probably u'll have a clustered index on it, if not create it, also check if update stats is set to auto..if not set itand in the meanwhile run sp_updatestats to update stats....
now..once u executed ur query for the first time, its execution plan is cached, so it runs faster next time, as sql engine does not generate the plan agn but reuses earlier plan ..if availabe in cache...
but the reason i think that ur query took too ling is that probaly some other -heavy- transactions were goin on , and ur updates had to wait for the locks to get released....
j2associates
Lesego:
This is not really enough information to go on; however, guessing at what you are asking: It sounds to me like you might have a trigger running amuk. I can't image this update query taking 11.5 minutes even if you are missing the primary key. First, get a printout of the execution plan of your update statement and show us your execution plan. Next, check and see if you have a trigger on your tblEmail table. If so, give us a look at your update trigger. Then verify that you still have the primary key on this table.
The next layer of questions might have to do with the physical make-up of your server. What kind of machine is SQL Server running on How much memory does the server have How much memory is allocated to SQL Server