Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLfriends
Starting Member
12 Posts |
 Posted - 2002-11-27 : 09:41:39
|
| I was writing SQL statements in my VB program as the following: strCmd = "Update GradesStud Set mergedtodl = '" & Now & "' where id =" & h MsgBox strCmd oConn.Execute strCmd, , adCmdText If Err.Number <> 0 Then MsgBox Err.Description MsgBox Err.Number End IfThe above code returned the "[ODBC SQL SERVER DRIVER]Timeout expired" error. However, the following code went through without any problems. strCmd = "Select SSN From DeansLetterStatus where SSN = '" & b & "'" Set objRS = oConn.Execute(strCmd, , adCmdText) If Not objRS.EOF Then strCmd = "Update DeansLetterStatus Set GradeTime = '" & Now & "'" _ & " Where SSN = '" & b & "'" oConn.Execute strCmd, , adCmdText Else strCmd = "Insert into DeansLetterStatus(Fname,Lname,SSN,GradeTime) " _ & "values('" & d & "','" & c & "','" & b & "','" & Now & "')" oConn.Execute strCmd, , adCmdText End IfI used the same connection object to execute the query statement. I am not sure why the first one "timeout" but the second one did not. I also set the CommandTimeout to 0. It ended up the first execution never returned an error. It did not appear to be a CommandTimeout issue.Any ideas will be appreciated. Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-27 : 10:01:52
|
| Well, they're updating two completely different tables for one thing. It could be the GradesStud table had a lot of locks on it and the connection timed out while waiting for them to be realeased; the other table was probably less contentious. And if the second block of code did an INSERT instead of an UPDATE, then you can't compare timeouts at all, they're two totally different operations.You shouldn't set CommandTimeout to 0, set it to 60 or 120 seconds at most. Anything that takes longer than 120 seconds needs to be reviewed, ADO shouldn't be calling processes that take that long to run.You can also rewrite these operations as SQL Server stored procedures, you can pass parameters to them like SSN and ID and have the server process them internally. It will very likely improve performance dramatically and make your VB/ADO code much cleaner.Edited by - robvolk on 11/27/2002 10:02:17 |
 |
|
|
SQLfriends
Starting Member
12 Posts |
Posted - 2002-11-27 : 12:34:50
|
| Thanks for your input. I used the "Exec sp_lock" and found out one of the rows has "CNVT" status. (The lock is being converted to another lock). This record might be corresponding to my SQL statement that does not work. I would like to inverstigate further about locks and concurrency about the GradesStud table. How do I do that? Or do you have other comments?Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-27 : 13:14:07
|
Books Online has a lot of good information on the various types of locks, transaction isolation levels, blocking, deadlocking, and various performance tuning measures you can take to speed up UPDATEs and the like. sp_lock is a good start, then you can work on using Profiler to spot more. Take some time to read it, don't jump right in with the tuning, try to absorb some of it first. It'll help you see your tables in a different perspective, and where the problems might be. Most of the time it's a pretty easy fix.As far as stored procedures go, the great benefit is that you can encapsulate the UPDATE, INSERT etc. in one unit, and set the locks and isolation levels specifically for that procedure, instead of letting ADO handle it (badly, IMHO)  I've found that about 90% of the time major performance issues go away when stored procedures are used. If you use ADO, you should get the data you need as quickly as possible and close the recordset. Then you can do the updates and such like you are now, via a Command or Connection object. Translating the code you have now to a stored procedure is pretty easy, and you can further refine and tweak the procedure without touching the ADO code.This thread might help:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=19638Also search the forums for "best practice" and "locking", and if you start using Profiler (or want an intro on it):http://vyaskn.tripod.com/analyzing_profiler_output.htmThere's also a section on stored procedures on SQL Team, although they're probably not specific to your situation, you'll get an idea on how to construct them efficiently. |
|
|
|
SQLfriends
Starting Member
12 Posts |
Posted - 2002-11-29 : 23:11:23
|
Thanks for your detailed reponse again. Actually, your first reply inspired me to find out the problem. You mentioned those SQL statements were updating two completely different tables. That made me paying more attention to those queries. What happened was I wrote a Select query and returned a recordset before the Update queries. The Select query was pulling data from a View which included the GradesStud table. In order to update the recordset, I have to set the cursor type and lock type to adOpenDynamic and adLockBatchOptimistic respectively. But I missed the optional arguments when opening the recordset. The timeout expired error message was not obvious at all. It misled me to go to the other direction that was not relevant to the problem.But the issue was resolved anyway. I appreciated your help.  |
|
|
|
|
|
|
|
|
[ODBC SQL SERVER] Timeout expired