ADO Connection Timeout

[RonRepp]

Hi all:

I have a rather lengthy and complex SQL statement in which I'm getting a timeout. I have the connection.TimeOut set to 0. Is this the same as setting it to infinity (as in SQL Server)? It only times out in certain circumstances, like when I choose more than one company. I have listed the code below.

Thanks,

Ron


Sub UpdateTrans()
Dim DB As String, D As String, C As String, T As String
Dim i As Integer, j As Integer, k As Integer
DB = "TSMaster"
Dim TransCon As New ADODB.Connection
Dim TransRS As New ADODB.Recordset
Set TransCon = New ADODB.Connection
TransCon.ConnectionString = "Provider=sqloledb.1;" & _
"Data Source=D63WV941;Initial Catalog=" & DB & ";User Id=sa;Password=sa; "

BegDate = frmWizard.dtBeg.Value
EndDate = frmWizard.dtEnd.Value

If ByDate = True Then
If ByCo = False Then
If AllTrans = True Then

SQL = "SELECT COMPANY AS Co, DIVISION AS Div, FIN_TRANS_DT, TRANSMITTAL_NR AS TransNum, " & _
"AMOUNTS_1 / 100 AS Debit, AMOUNTS_2 / 100 AS Credit From dbo.SV_MACORD_FT_SHIP " & _
"WHERE (FIN_TRANS_DT BETWEEN CONVERT(DATETIME, '" & BegDate & " 00:00:00', 102) " & _
"AND CONVERT(DATETIME, '" & EndDate & " 00:00:00', 102)) " & _
"ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT"
Else
'''pick up transmittals


End If

Else
'''by company here
If AllCo = True Then
If AllTrans = True Then
SQL = "SELECT COMPANY AS Co, DIVISION AS Div, FIN_TRANS_DT, " & _
"TRANSMITTAL_NR AS TransNum, " & _
"AMOUNTS_1 / 100 AS Debit, AMOUNTS_2 / 100 AS Credit From dbo.SV_MACORD_FT_SHIP " & _
"WHERE (FIN_TRANS_DT BETWEEN CONVERT(DATETIME, '" & BegDate & " 00:00:00', 102) " & _
"AND CONVERT(DATETIME, '" & EndDate & " 00:00:00', 102)) " & _
"ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT"
Else
'''pick up transmittals

End If
Else
'''individual company here
If AllTrans = True Then

SQL = "SELECT COMPANY AS Co, DIVISION AS Div, FIN_TRANS_DT, " & _
"TRANSMITTAL_NR AS TransNum, AMOUNTS_1 / 100 AS Debit, AMOUNTS_2 / 100 AS Credit " & _
"From dbo.SV_MACORD_FT_SHIP " & _
"WHERE (FIN_TRANS_DT BETWEEN CONVERT(DATETIME, '" & BegDate & " 00:00:00', 102) " & _
"AND CONVERT(DATETIME, '" & EndDate & " 00:00:00', 102)) AND "

j = 0
For k = 1 To frmWizard.tvCo.Nodes.Count

If frmWizard.tvCo.Nodes(k).Checked = True Then

j = j + 1
C = Left(frmWizard.tvCo.Nodes(k).Text, 2)
D = Mid(frmWizard.tvCo.Nodes(k).Text, 3, 2)
If j = 1 Then
If frmWizard.tvCo.Nodes(k).Text = "Null" Then
SQL = SQL & "(COMPANY IS NULL) AND (DIVISION IS NULL) "
Else
SQL = SQL & "(COMPANY = '" & C & "') AND (DIVISION='" & D & "') "
End If

Else
SQL = SQL & "OR (COMPANY = '" & C & "') AND (DIVISION='" & D & "') "

End If

End If
Next k


SQL = SQL & "ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT"
Else
'''load trans #

End If

End If

End If

End If

'On Error GoTo TransERR
TransCon.ConnectionTimeout = 0
TransCon.Open TransCon.ConnectionString

Set TransRS = New ADODB.Recordset

TransRS.Open SQL, TransCon, adOpenKeyset, adLockReadOnly
Do While Not TransRS.EOF
Screen.MousePointer = vbHourglass
ShowProgress False
DoEvents
NextRow
Comp = TransRS(0).Value
Div = TransRS(1).Value
TransDate = TransRS(2).Value
If IsNull(TransRS(3).Value) Then
Trans = "NULL"
Else
Trans = TransRS(3).Value
End If
TransCount = 1
Debit = TransRS(4).Value
Credit = TransRS(5).Value

AddValues

TransRS.MoveNext
Loop
Screen.MousePointer = vbHourglass
ShowProgress False
DoEvents
Exit Sub
TransERR:
MsgBox "You have encountered an error." & vbNewLine & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description, vbCritical + vbOKOnly, "Help"

End Sub

 

[AGIMA]

How large does this SQL get? I would use a IN statment in your WHERE

How long does this take to run through SQL manager?


AGIMA - professional web hosting is our business.

AGIMA Computing

http://www.agima.com.au

 

[ArtieChoke]

I have the connection.TimeOut set to 0. Is this the same as setting it to infinity (as in SQL Server)?

Yes, but it's only for the connection, not the query. ADO doesn't have a way to set the query timeout - that's strictly a SQL Server proposition. I've had similar problems where it ran fine in QA, but took forever in ADO. It turned out to be the where clause. Do you have an index on all the fields you are filtering on? That might help. Or simplify the where clause if you can.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[zarkon4]

Try setting the connection.CommandTimeout to zero as well.

 

[RonRepp]

AGIMA and ArtieChoke:

The SQL statement can get quite large. It's based off a treeview where the user can select over one hundred companies. They can also hit a Select All button, which directs the program to a different SQL (as can be seen in the example) which runs fine.

All tables have indexes and keys, and the query (depending on the number of selection criteria) takes about 2-1/2 minutes in QA.

AGIMA, what exactly do you mean by an IN statement in the WHERE clause? This is unfamiliar to me.

Thanks,

Ron

 

[zarkon4]

I had a very large sql statement (larger than yours) that had several joins and functions withing the query. The query ran fine in query analyzer but timed out in vb using ado.

I set the connectiontimeout to zero and set the commandtimeout to zero and all worked well.

 

[RonRepp]

Thanks, Zarkon4. I'll give that a shot.

Ron

 

[avanderlaan]

Ron,

You might also consider declaring your cursors to be maintained on the server when initializing the connection object.

 

[bjd4jc]

Do you have a combined index created on the fields (or are they indexed as separate indexes? These are the fields that I see that need a single index:

COMPANY, DIVISION and FIN_TRANS_DT

This is likely what is causing your issue. You also might want to look at the ORDER BY statment (which can be quite expensive). Have you done an Execution Plan? This will tell you which parts of your query are taking the most time.

I would also suggest that you move the convert out of the SQL Statment and put them into variables and reference the variable from SQL. In doing this I would also suggest you move your SQL Statement to a stored proc so it is compiled.

If a stored proc is not an option then use the following base SQL statment:

SQL = "Declare @BeginDt datetime " & vbcrlf
SQL = SQL & "Declare @EndDt datetime " & vbcrlf
SQL = SQL & "Set @BeginDt = CONVERT(DATETIME, '" & BegDate & " 00:00:00', 102)) " & vbcrlf
SQL = SQL & "Set @EndDt = CONVERT(DATETIME, '" & EndDate & " 00:00:00', 102)) " & vbcrlf

SQL = SQL & "SELECT COMPANY AS Co, DIVISION AS Div, FIN_TRANS_DT, TRANSMITTAL_NR AS TransNum, " & _
                "AMOUNTS_1 / 100 AS Debit, AMOUNTS_2 / 100 AS Credit From dbo.SV_MACORD_FT_SHIP " & _
                "WHERE (FIN_TRANS_DT BETWEEN @BeginDt AND @EndDt " & _
                "ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT"

 

[bjd4jc]

I should have mentioned that to do an execution plan you go to the Query menu and then choose Execution Plan toward the bottom.

This will give you info on what is taking the most time for your query.

 

[bjd4jc]

Sorry, I keep thinking of things to mention here. I should have thought this through a little more before I post.

But, when you do you Execution Plan in QA, you will want to look for any "Table Scans". You should not be doing any of these. If there is a table scan, that means it is not Index properly.

To create your INDEX do a statement like this in QA:

CREATE INDEX IX_MyIndexName ON SV_MACORD_FT_SHIP(COMPANY ASC, DIVISION ASC, FIN_TRANS_DT ASC)

Also, having an index that contains your ORDER BY fields will also greatly increase the speed of your query.

 

[ArtieChoke]

Doh - it was the order by that caused us the problem. I don't know what ADO is doing with it, but it did make the query time out. I don't believe that the command timeout will work in this case, since you're returning a recordset from a connection object, not setting up a command. Maybe if you change to return the results from the command object to the recordset it will work okay.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[bjd4jc]

I would still try to optimize the query. Why should the user have to wait 2.5 minutes for a query to return?? That's ridiculous. I am sure that this query could be optimized by create more indexes (or appropriate ones). I am anxious to hear from RonRepp to see what is progress is.