Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 19854 | Views in the last 30 days: 4
 
Related Articles
ARTICLE

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 ...

FORUM

SQL Server Client Network utility

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

FORUM

SQL Server Client Network Utility Download

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

FORUM

NULL value returning in output

NULL value returning in output in SQL 2005 whereas did not return in SQL 2000

ARTICLE

Output Parameters

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

Tags
ado    
performance tuning    
programming    
sql server 6.5    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones