Retrieving Multiple RecordSets in a Single Trip to the Database
I know that I keep going on about this, but: The best way to speed up your application is to retrieve all the data you need on each trip to the database and make as few trips to your database as you can. One way to do that when retrieving rows is to retrieve multiple sets of rows on each trip.
This means that you can reduce trips to the database in a stored procedure, by returning multiple sets of rows from a single stored procedure with a single call. If you're using ADO.NET, you can combine multiple Select statements in your Command object's CommandText property (just make sure you put a semicolon between the statements):
Dim cmd As New SqlCommand
cmd.CommandText = "Select * from Customers; Select * from Countries;"
When you call ExecuteDataReader to get your DataReader, the DataReader will be processing the first set of records returned from your stored procedure or from the first Select statement in your CommandText:
Dim rdr As SqlDataReader
'Processing customers
rdr = cmd.ExecuteReader()
You can process that recordset or not -- your choice. When you're ready to process the next set of rows, just call DataReader's NextResult method. This command moves the DataReader to process the Countries that I retrieved:
rdr.NextResult
Because of the way that ADO.NET talks to your backend database will vary from one database engine to another and on how much data you're retrieving, I can't guarantee that each NextResult won't trigger another trip to the database (ideally, all of the data will come down to the client in one trip).
But you're guaranteed that you'll only make one trip to the database when you make the initial request and that's a good thing.
And, as I mentioned in another tip, if you want to mix some update commands in with your Select statements, you can do that, too -- saving you even more trips. I wouldn't suggest that combining these tips eliminates the need for stored procedures; I would, however, suggest that you only use stored procedures when you need some control logic mixed in with your SQL statements.
Posted by Peter Vogel on 11/20/2014