SQLServerCentral Article

Reducing Round Trips - Part 3


Reducing Round Trips - Part 3

In previous columns I discussed a client

side caching scheme and returning

multiple recordsets as two of many ways to reduce the number of round trips

required by your applications. This week I'd like to cover a few more that are

quick and easy to implement without requiring extensive modifications to your


You should always use 'set nocount on' at the beginning of  your stored

procedures. The reason? Directly from MS, here is why:

"SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the

client for each statement in a stored procedure. When using the utilities

provided with Microsoft® SQL Server™ to execute queries, the results prevent

"nn rows affected" from being displayed at the end Transact-SQL

statements such as SELECT, INSERT, UPDATE, and DELETE."

You can event set this option globally by using trace flag 3640. The standard

warning is that some applications may depend on receiving the messages and will

then fail. I haven't seen one so far. But I do like to keep my options open so I

think it's safer to just add the line to all of the procedures I create. Add it

to your proc template!

Another technique is to manually construct the ADO parameters list rather

than use the parameters.refresh method. If you use the refresh method every time

you invoke the procedure ADO will make a call to the server to the parameter

list, then you set values, then it sends the parameters to the server and then

returns parameters, a recordset, or both. Building the param list manually is a

little more work up front. The only downside is that your code on the client is

not as 'dynamic' as it used to be - if you change the parameters of the

procedure the client app will have to be modified. The work around I've used is

when modifying the parameters list (or sometimes even what the proc

does/returns) is to create a new proc and use a number in the proc name to

indicate the revision. Something like this:

create proc usp_GetOrderDetails @OrderId

int as

set nocount on

select field1, field2, field3 from

dbo.orderdetails where OrderID=@OrderID

create proc usp_GetOrderDetails @OrderID

int, @EmployeedID int as

set nocount on

select field1, field2, field3 from

dbo.orderdetails where OrderID=@OrderID and EmployeeID=@EmployeeID

Not only does it allow me to make changes without having to immediately

distribute a new executable to the clients, it also lets me run multiple

versions of the app at the same time - for testing, or if a rollback is

required. The usual objection to building the parameters manually is the work.

Easy enough to avoid, download the stored

procedure add-in for VB from Bill Vaughn's

site. This is source code, so if you don't like the way it works, change it! As

you can see in the image below once you have entered your connect string you

just pick from the list of procs and set your options. I've included the full

code generated for the proc shown in the image so you can see the final result.

Great tool!

Dim params as ADODB.Parameters

Dim param as ADODB.Parameter

' Create connection and command objects

Set cn = New ADODB.Connection

Set cmd = New ADODB.Command

' Set connection properties and open

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=EG"


' Set command properties

With cmd

Set .ActiveConnection = cn

.CommandText = """Sales by Year"""

.CommandType = adCmdStoredProc

Set params = .Parameters

End With

' Define stored procedure params and append to command.

params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)

params.Append cmd.CreateParameter("@Beginning_Date", adDBTimeStamp, adParamInput, 0)

params.Append cmd.CreateParameter("@Ending_Date", adDBTimeStamp, adParamInput, 0)

' Specify input parameter values

params("@Beginning_Date") = MyVariable

params("@Ending_Date") = MyVariable

' Execute the command

cmd.Execute , , adExecuteNoRecords

' Retrieve stored procedure return value and output parameters

MyVariable = params("@RETURN_VALUE")

Another technique is to retrieve the data once, then do client side

filtering. ADO recordsets have a filter method, the only downside is that it

only accepts one where clause. Beyond that you would have to write code to loop

through the recordset and figure out which records met the criteria. Whether you

can use this method effectively will depend on the performance of the client

machine and the amount of data you have to return to make it effective. This is

a variant of the caching technique I discussed in my previous column, just

easier to implement since you're just solving one problem at a time. Of course

to make this work the connection must specify a client side cursor! I've got

some additional notes on ADO in general in a series

I wrote last year that you might find helpful if you decide to explore this


In the next installment I'll talk about the various ways XML can be used to

reduce round trips. Comments so far?


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating