.NET Tips and Tricks

Blog archive

Retrieve 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 DataReader 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 Entity Framework talks to your back-end 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, "Speed Up Your Application by Doubling Up on Database Access," 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 01/13/2015 at 2:20 PM


comments powered by Disqus

Featured

  • Microsoft's Tools to Fight Solorigate Attack Are Now Open Source

    Microsoft open sourced homegrown tools it used to check its systems for code related to the recent massive breach of supply chains that the company has named Solorigate.

  • Microsoft's Lander on Blazor Desktop: 'I Don't See a Grand Unified App Model in the Future'

    For all of the talk of unifying the disparate ecosystem of Microsoft-centric developer tooling -- using one framework for apps of all types on all platforms -- Blazor Desktop is not the answer. There isn't one.

  • Firm Automates Legacy Web Forms-to-ASP.NET Core Conversions

    Migration technology uses the Angular web framework and Progress Kendo UI user interface elements to convert ASP.NET Web Forms client code to HTML and CSS, with application business logic converted automatically to ASP.NET Core.

  • New TypeScript 4.2 Tweaks Include Project Explainer

    Microsoft shipped TypeScript 4.2 -- the regular quarterly update to the open source programming language that improves JavaScript with static types -- with a host of tweaks including a way to explain why files are included in a project.

Upcoming Events