.NET Tips and Tricks

Blog archive

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 at 1:51 PM


comments powered by Disqus

Featured

  • .NET Core Ranks High Among Frameworks in New Dev Survey

    .NET Core placed high in a web-dominated ranking of development frameworks published by CodinGame, which provides a tech hiring platform.

  • Here's a One-Stop Shop for .NET 5 Improvements

    Culled from reams of Microsoft documentation, here's a high-level summary of what's new for performance, networking, diagnostics and more, along with links to the nitty-gritty details for those wanting to dig in more.

  • Azure SQL Database Ranked Among Top 3 Databases of 2020

    Microsoft touted the inclusion of Azure SQL Database among the top three databases of 2020 in a popularity ranking by DB-Engines, which collects and manages information about database management systems, updating its lists monthly.

  • Time Tracker Says VS Code Is No. 1 Editor for Devs, Some Working 15+ Hours Per Day

    WakaTime, which does time tracking for programmers, released data for 2020 showing that Visual Studio Code is by far the top editor/IDE used by its coders, some of whom are hacking away for more than 15 hours per day.

Upcoming Events