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
app.
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"
cn.Open
' 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
option.
In the next installment I'll talk about the various ways XML can be used to
reduce round trips. Comments so far?
