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

Reducing Round Trips

By Andy Warren, (first published: 2002/01/24)

One of the best ways to reduce the load on your server and increase application responsiveness is to reduce the number of "round trips" your application makes. A round trip consists of a request sent to the server for data or an action (select query for a request, insert/update/delete for action). Every time you do that it takes time and resources. To the client the time starts when you hit 'GO' and ends when they can proceed. Behind the scenes that time includes set up time in the app, maybe to build a sql string or some other task, then network time to send the request, server time to process the request, network time to return the results, then time to process the results on the client. Resources used included client RAM and CPU, network bandwidth, and server RAM and CPU.
So how can you reduce the round trips?
One good way is to try to do as much as you can in one trip. After all, you're going to have to connect to the server at some point! Instead of running a single query to return an account or order record, then a second query to return the associated contacts (or order details), combine the two in a stored procedure like this:
Create Proc usp_GetMyData @OrderID int as 
--comment here 
set nocount on --save just a little bit more by using this 
select field1, field2, etc, from ordertable where primarykey=@OrderID 
select field3, field4, etc, from orderdetails where foreignkey=@OrderID
By doing this we've saved the time/resources of one entire network trip! In this example I'm returning 'multiple recordsets' which are cake to handle in ADO. Get your developers using this method! Remember also that while I used a master detail relationship to illustrate, there is no requirement that the recordsets be at all related.
Of course if you don't have much data, you could use the same idea but return everything as output parameters. This can be tricky and/or aggravating if you'll have an unknown number of return records that you need to 'flatten' out into parameters. For example, if you might return up to 10 detail records, you might end up with a proc like this:
Create proc usp_GetMyData @OrderID int, @Order1ID int output, @Order1Desc varchar(20) output, @Order2ID int output, @Order2Desc varchar(20) output ....etc
If you've have a fixed set of values this works good. If you will end up having to spend a lot of work in the client working through all the values to see which are populated - I think you'll be better off using a recordset.
Next week we'll talk about a couple more ideas for decreasing round trips, including my variant - not making the trip at all! Got ideas or comments about this subject? Let me know!

Total article views: 19943 | Views in the last 30 days: 3
Related Articles

Network Computing - Is It Simply a Different Form of Client/Server?

New author! Dale discusses the differences between network computing and client server. Do you know ...


SQL Server Client Network utility

In what way does client side configuration effect the client-server communication


SQL Server Client Network Utility Download

Is the client network utility available for download seperately from the enterprise edition?


NULL value returning in output

NULL value returning in output in SQL 2005 whereas did not return 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 6.5    
sql server 7