SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Reducing Round Trips - Part 3

By Andy Warren,

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"

' 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?

Total article views: 7870 | Views in the last 30 days: 2
Related Articles

Permissions to execute sp_procedure_params_rowset

execute of sp_procedure_params_rowset return empty recordset


Calling Oracle stored procedure with return parameter from SQL2005

Calling Oracle stored procedure with return parameter from SQL2005


Help with frustrating BCP call and stored procedure params

BCP + Stored Procedure queryout with SP params - "quotes" are killing me !!!!


calling a stored procedure(with no return parameters) from jdbc

calling a stored procedure(with no return parameters) from jdbc in SQL 2000


Output Parameters

Regular columnist Robert Marda writes about the basics of using output parameters. If you're not usi...

performance tuning    
sql server 7    
visual basic 6