SQL Server Poor Response times to VB6 app

[earlfo]

I have an application that builds a SQL statement based on search criteria, and displays the results. The first retrieve runs rather quickly, subsequent searches tend to run very slow. This happens even when the exact same search criteria is used.

One distinct difference between the first search and any subsequent searches is the way the Selects are passed to SQL server by Visual Basic.

In the first search the Select is passed to SQL Server as a straight SQL statement and SQL Server returns streams of large packets. I'm assuming this is the Select that returns the largest amount of data and has the most impact on apparent performance to the user. Subsequent Selects that are still part of the same search are passed as RPCs to 'sp_cursoropen'. The Select statement in this case is passed as a parameter to this stored procedure. The data that's returned is returned in much smaller packets with many other "sp_cursor..." RPCs in-between, no streaming. There are other Selects that are sent as straight SQL towards the end and they will stream.

When the search is executed a 2nd time the primary difference is that the 1st Select sent to SQL server is passed as an RPC to sp_cursoropen. The behavior is then no-streaming with small packets etc. Since this returns the largest chunk of the data it has a big impact on performance and when the user finally sees data on the screen.

I really can't explain this behavior, but based on this I think the application is determining the performance, not the server. Do you know why sometimes it would pass the Selects differently?

I am using recordsets (this is an overview of the connection and recordset creation):

Public m_Connection As ADODB.Connection
Private m_Recordset As ADODB.Recordset
Private strSelect As String

Set m_Connection = New ADODB.Connection

strconnect = "Data Source=DB; Database=DB; UID=" & txtUserID.Text & "; PWD=" & txtPassword.Text & "; SQL_PACKET_SIZE=4096"

m_Connection.ConnectionString = strconnect
m_Connection.CommandTimeout = 0
m_Connection.Open

< code to load a string select statement into strSelect >

m_Recordset.Source = strSelect
m_Recordset.Open

If m_Recordset.State = adStateOpen Then
m_Recordset.Close
End If

 

[chadt]

when do you close your connection? It looks like on every search you do you are creating a new connection....thus taking up resources.

Either open up a global connection when the app starts or close your connection after your search

ie...

conn.Close
Set conn = Nothing

Make sure you use a ClientSide cursor as well.....this will take up less server resources.

 

[earlfo]

Chad, Thanks for your response.

I checked my code and I am using a clientside cursor. It is a dynamic cursor. I do have a global connection object. I close the recordset after the retrieve, but not the connection.

Those were all good things to recheck though.

The problem I seem to have is how the data is being parsed up over the network. I have used the &quot;SQL_PACKET_SIZE=4096&quot; parameter to increase the packet sizes, but it does not seem to be having an effect. The use of sp_cursoropen to run the sql was seen when a trace was run returns lots of small packets instead of fewer larger packets. Is there any way to specify to SQL Server not to do this?

 

[zemp]

Chadt has a good point. When you are not usig an object close it or reuse it. Don't just open another one.

If I am running select statements that will search through lots of records I like use a server side cursor. Let SQL server do the work that it is designed to do. A client side will pull all records to your app and then look for the ones you want. A server side cursor will search for the records you want and send only those to your app. This can be a big factor over a busy network and not so much if everything is local.

I also like to use Stored procedures as mush as possible. Setting them up to receive one or two simple numeric parameters (ID numbers, etc.).

You can also play with the cursor types. Forwardonly is usually faster. However you lose the ability to move at will through the recordset. But do you need to?

Thanks and Good Luck!

zemp