.NET Tips and Tricks

Blog archive

Speed Up Apps by Doubling Up on Database Access

The slowest thing you can do in your application is read or write to your hard disk.

The second slowest thing you can do is issue a request to another computer. This means, of course, that whenever you access your database you're doing the two slowest things you can manage. Which means that one of the simplest things you can do to speed up your application is to reduce the number of trips you make to your database, even if you don't make any changes to the amount of data you update or retrieve.

Imagine, for instance, you have this code that first adds a record and then retrieves the number of records present after the insert:

cmd.CommandText = "Insert Into ... ;"
cmd.ExecuteNonQuery()

cmd2.CommandText = "Select Count(*) from ... ;"
Dim res As Integer
res = cmd.ExecuteScalar()

As it's written, this is going to involve two trips to the database. There's no reason, however, that the two SQL commands can't be combined into a single request, executed by calling ExecuteScalar:

cmd.CommandText = "Insert Into  ...;" & "Select Count(*) from  ...;"
Dim res As Integer
res = cmd.ExecuteScalar()

Part of the problem is that the ADO.NET method names (ExecuteReader, ExecuteScalar and ExecuteNonQuery) suggest there's only one kind of SQL statement you can use with any method. But, in fact, the three method names really reflect what's returned: ExecuteReader returns a DataReader that will let you work through the rows returned by a Select statement, ExecuteScalar returns the first column of the first row returned by a Select, and ExecuteNonQuery returns the number of rows updated.

You're free to pass any kind of SQL statement (or combination of SQL statements) to any of these methods and it will probably work out for you. If you need, for example, to issue some updates and then retrieve the results, then combine your Update/Insert/Delete statements with a Select statement, execute the commands with a call to ExecuteReader and then use the resulting DataReader to process the rows you get back.

Posted by Peter Vogel on 10/10/2014 at 1:51 PM


comments powered by Disqus

Featured

  • Visual Studio Code Dev Team Cleans Up

    The Visual Studio Code development team focused on some housekeeping in the October update, closing more than 4,000 issues on GitHub, where the cross-platform, open-source editor lives.

  • ML.NET Model Builder Update Boosts Image Classification

    Microsoft announced an update to the Model Builder component of its ML.NET machine learning framework, boosting image classification and adding "try your model" functionality for predictions with sample input.

  • How to Do Naive Bayes with Numeric Data Using C#

    Dr. James McCaffrey of Microsoft Research uses a full code sample and screenshots to demonstrate how to create a naive Bayes classification system when the predictor values are numeric, using the C# language without any special code libraries.

  • Vortex

    Open Source 'Infrastructure-as-Code' SDK Adds .NET Core Support for Working with Azure

    Pulumi, known for its "Infrastructure-as-Code" cloud development tooling, has added support for .NET Core, letting .NET-centric developers use C#, F# and VB.NET to create, deploy, and manage Azure infrastructure.

  • .NET Framework Not Forgotten: Repair Tool Updated

    Even though Microsoft's development focus has shifted to the open-source, cross-platform .NET Core initiative -- with the aging, traditional, Windows-only .NET Framework relegated primarily to fixes and maintenance such as quality and reliability improvements -- the latter is still getting some other attention, as exemplified in a repair tool update.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events