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