SQL Server Performance Forum – Threads Archive

help with better performace

Fellow develoeprs, below is a sample of some VB6 code on a large scale project Ive been working on. I’m having discussions with my IT manager on the best way to write our subroutines & functions. We have 1 data environment that has our connection information. Here is the connection string when played out:<br /><br />"Provider=MSDASQL.1<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=""<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=True;User ID=AFGIG;Data Source=AFGIG"<br /><br />Does anyone think this code is not efficient? It seems to run decent on my server (dual PIII 500mhz, 1G ram) but on his server (dual PII 400 mhz, 1G ram) it seems to run like a dog. This application is installed on all of our client machines with a DSN setup on each PC. Any tips or suggestions to make it better?? Our client machines run P4 with XP SP2. <br /><br />Public Function GetRackAssign(strOrderNum, iLineNum) As String<br /> <br /> Dim txtSQL As String, rsComm As New ADODB.Command, rs As New ADODB.Recordset<br /> Dim strRackSlot As String<br /> <br /> ‘ Find Rack assignment for printing one label<br /> txtSQL = "SELECT BeginCart, BeginSlot FROM ord_LineItem WHERE ord_OrderNum = ‘" & strOrderNum & "’ AND ord_LineNum = " & iLineNum<br /> rsComm.ActiveConnection = deOES.cnnOES.ConnectionString<br /> rsComm.CommandText = txtSQL<br /> With rs<br /> If .State = adStateOpen Then .Close<br /> .Open rsComm, , adOpenStatic<br /> If .RecordCount &gt; 0 Then<br /> strRackSlot = IIf(IsNull(!BeginCart), "", !BeginCart) & IIf(IsNull(!BeginSlot), "", !BeginSlot)<br /> Else<br /> strRackSlot = ""<br /> End If<br /> .Close<br /> End With<br /> Set rs = Nothing<br /> Set rsComm = Nothing<br /> <br /> GetRackAssign = strRackSlot<br /> <br />End Function<br /><br />Public Function GetReferenceLineNum(strOrderNum As String, iLineNum As Integer) As Integer<br /> <br /> Dim txtSQL As String, rsComm As New ADODB.Command, rs As New ADODB.Recordset<br /> Dim iRefLineNum As Integer<br /> <br /> ‘ Find Reference Line Number from original line item<br /> txtSQL = "SELECT ord_RefLineNum FROM ord_LineItem WHERE ord_OrderNum = ‘" & strOrderNum & "’ AND ord_RefLineNum = " & iLineNum<br /> rsComm.ActiveConnection = deOES.cnnOES.ConnectionString<br /> rsComm.CommandText = txtSQL<br /> With rs<br /> If .State = adStateOpen Then .Close<br /> .Open rsComm, , adOpenStatic<br /> If .RecordCount &gt; 0 Then<br /> iRefLineNum = IIf(IsNull(!ord_RefLineNum), 0, !ord_RefLineNum)<br /> Else<br /> iRefLineNum = 0<br /> End If<br /> .Close<br /> End With<br /> Set rs = Nothing<br /> Set rsComm = Nothing<br /> <br /> GetReferenceLineNum = iRefLineNum<br /> <br />End Function<br /><br />Public Sub AddConsumeInventoryTempRecord(strOrderNum As String, iLineNum As Integer, iQty As Integer)<br /> <br /> Dim txtSQL As String, rsComm As New ADODB.Command, rs As New ADODB.Recordset<br /><br /> txtSQL = "SELECT * FROM tblConsumeInventoryTemp WHERE OrderNum = ‘" & strOrderNum & "’ AND LineNum = " & iLineNum<br /> rsComm.ActiveConnection = deOES.cnnOES.ConnectionString<br /> rsComm.CommandText = txtSQL<br /> With rs<br /> If .State = adStateOpen Then .Close<br /> .Open rsComm, , adOpenStatic, adLockBatchOptimistic<br /> If .RecordCount = 0 Then .AddNew<br /> <br /> !OrderNum = strOrderNum<br /> !LineNum = iLineNum<br /> !OrderQty = iQty<br /> <br /> .UpdateBatch<br /> .Close<br /> End With<br /> Set rs = Nothing<br /> Set rsComm = Nothing<br /><br />End Sub<br />

---

I don’t there is much things to be done and I hope ord_OrderNum ,ord_RefLineNum and Line no have indexes
And One more thing Do not use Select * From instead use Select FldName From

---

Hi<br /><br />I think the efficency is in your vb code not in the sql statement. you should either write a DB class or DB modules here is part of my actual module<br /><br />Public gAdoConnectStr As String<br />Public Const gTagSeparator As String = ";"<br /><br />’Public Const gAdoConnectStr As String = "driver={SQL Server};OLE DB Services=-1;" & _<br />’ "server=SERVERNAME;uid=USERID<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />wd=PASSWORD;database=DATABASE"<br />’I commented this out since I pass the gAdoConnectionStr at login to connect to different database eg:<br />’Developer and Production.<br /> <br />Private gConnection As ADODB.Connection<br /><br />Public Function GetConnection() As ADODB.Connection<br /> If (gConnection Is Nothing) Then<br /> Set gConnection = New ADODB.Connection<br /> gConnection.ConnectionString = gAdoConnectStr<br /> gConnection.Open<br /> End If<br /> Set GetConnection = gConnection<br /><br />End Function<br /><br />Public Sub CloseDBConnection()<br /> If (Not gConnection Is Nothing) Then<br /> gConnection.Close<br /> Set gConnection = Nothing<br /> End If<br />End Sub<br /><br />Public Function ExecuteSql(sSql As String, Optional bShowError As Boolean = True) As Boolean<br /> <br /> Dim i As Integer<br /> <br /> GetConnection<br /> <br /> gConnection.BeginTrans<br /> On Error GoTo err_sql<br /> gConnection.Execute sSql<br /> gConnection.CommitTrans<br /><br /> <br /> ExecuteSql = True<br /> Exit Function<br /><br />err_sql:<br /> gConnection.RollbackTrans<br /> ExecuteSql = False<br /> If bShowError Then<br /> MsgBox "modDataConnect.ExecuteSql() : SQL Error " & _<br /> vbCrLf & Err.Description & vbCrLf & _<br /> sSql, vbCritical<br /> End If<br /><br />End Function<br />Public Function ExecuteSqlBatch(oSqlCol As Collection, Optional bShowError As Boolean = True) As Integer<br /> ‘Return : Index of oSqlCol which causes an error<br /> ‘ IF OK, return 0<br /> <br /> Dim i As Integer<br /> Dim nErrSqlIdx As Integer<br /> <br /> GetConnection<br /> gConnection.BeginTrans<br /> On Error GoTo err_sql<br /> For i = 1 To oSqlCol.Count<br /> nErrSqlIdx = i<br /> gConnection.Execute oSqlCol.Item(i)<br /> Next i<br /> gConnection.CommitTrans<br /> <br /> ExecuteSqlBatch = 0<br /> Exit Function<br /><br />err_sql:<br /> gConnection.RollbackTrans<br /> ExecuteSqlBatch = i<br /> If bShowError Then<br /> MsgBox "modDataConnect.RunSqlBatch() : SQL Error " & _<br /> vbCrLf & Err.Description & vbCrLf & _<br /> oSqlCol.Item(nErrSqlIdx), vbCritical<br /> End If<br /><br />End Function<br /><br /><br />May the best cheaters win

---

i would consider forcing the netlib to tcp/ip , ie, prevent accident named pipes usage, unless you also have VIA.
Network Library=dbmssocn" also, if you need high call volume performance, you must use stored procedures, called as RPC, passing in native types, instead of text string.
not quite as good is a parameterized query.
otherwise, any discussion of peformance is limited to how bad you are willing to accept/tolerate

---

]]>