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!