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:57 PM0 comments


Files and Classes: Organize by Use

The default organization method Visual Studio uses when adding classes is to put each class in a separate file. That doesn't mean you should do the same thing. This is especially true in Visual Studio 2012, which combines Class View with the Solution Explorer standard File View (and adds a search capability on top of that).

Even in earlier versions of Visual Studio, you can always get to a class's code just by clicking on the class name in your code and pressing F12. With those tools in place, it's hard to see much advantage in being able to scroll to the class file in the Solution Explorer file view. Keeping each class in a separate file is an option, not a requirement.

Off the top of my head, I can think of at least two occasions where it makes sense to put multiple classes in the same file. First, where you have a class that's used only by one other class, it's probably easier for everybody if you keep those two classes in a single file. Second, EventArgs classes that are generated in one class and returned from that class's events might be best kept in the same file with the class that generates it.

I bet some of you can think of other occasions where it makes more sense to put two classes in the same file than it does to put them in separate files. That's OK.

Posted by Peter Vogel on 10/07/2014 at 9:44 AM0 comments


NimbleText: An Editor, Only Better

I've admitted it before: Regular expressions defeat me.

NimbleText gives me an editor and an "English-like" way of writing templates that will convert a list of data values into something more useful. Under the hood, it uses regular expressions to identify the data to change but I'm insulated from that. NimbleText isn't a Visual Studio add-in so you have to leave Visual Studio to use it, but even with that limitation NimbleText lets you do wonderful things.

NimbleText is relatively user-friendly: It's well-documented and has menus for selecting and inserting NimbleText keywords into your templates (though many of my templates don't need them). More importantly, NimbleText has a library of snippets to help get you started.

This snippet, for instance, integrates NimbleText keywords and JavaScript to generate C# properties from a list of datatypes and property names:

      <% $0.toLowerCase() %>private $0 <% $1.toCamelCase() %>;
$ONCE

$EACH
public $0 <% $1.toPascalCase() %> {
  get { return <% $1.toCamelCase() %>; }
  set { <% $1.toCamelCase() %> = value; }
}

I've also used NimbleText to process text files of data (eliminating duplicate rows, for instance). I can't tell you that the learning curve is zero, but it's pretty darn flat.

Posted by Peter Vogel on 10/02/2014 at 11:00 AM0 comments


Return Types for Methods that Return Collections

In a tip from a couple of months ago, I suggested that if you have a method or property that returns a collection, then your method should return only one of three interfaces: IList, IQueryable or IEnumerable. (Returning an interface allows you to change the type of the collection used inside your method without breaking the clients that use your method.)

Not surprisingly, I got some interesting feedback from readers. One reader pointed out that if you're going to allow the client to update your collection, Microsoft recommends your method return ICollection. ICollection allows the client to add to the end of your collection, to clear your collection, and to remove specific objects from the collection; ICollection doesn't allow the client to insert or delete items at specific positions in your collection. In other words, ICollection allows the client to update the collection you return only by adding to it or by removing specific objects. I can see how that could be a better choice than IList, which allows the client to arbitrarily insert and remove items by position.

Side note: Interestingly, neither ICollection or IList supports one of my favorite methods, AddRange, which allows you to add a collection of items to the end of another collection (AddRange is only available on the List class itself). I'm fond enough of AddRange that I've created my own extension method that adds the method to any class that implements IList. I'll switch that extension method's definition over to using ICollection.

No one objected to my recommendation for using IQueryable with methods that return Entity Framework results. However, I realized I was being narrow-minded. You should use IQueryable to return the result of any LINQ query where you want to give the client the ability to extend the query by using the result in another LINQ query (this gives the compiler more options in optimizing the final query). That's going to be an Entity Framework result 90 percent of the time, but I should be as inclusive as possible.

However, another reader did suggest that, if you were only going to let the client read your collection, IReadOnlyList is a better choice than IEnumerable. Just like IEnumerable, IReadOnlyList allows the client to retrieve items at specific locations and to build For…Each loops to process the list. Declaring your method as returning IReadOnlyList does have the advantage of being more obvious than IEnumerable in communicating that your collection is read-only, though. Unfortunately, IReadOnlyList is only available in the Microsoft .NET Framework 4.5 and newer.

So here's the latest and greatest version of my advice: If your method or property is returning a collection and you want to allow the client to add or remove objects in a controlled way (add only at the end, remove only by object reference), return ICollection; if you want to give the client the ability to work with the items in your collection by position, return IList. If you're returning the results of a LINQ query and want to give the client the ability to use the result in another LINQ query in an efficient way, use IQueryable. For everything else (which just leaves read-only collections) use IReadOnlyList; if IReadOnlyList isn't available, use IEnumerable.

Posted by Peter Vogel on 09/23/2014 at 10:48 AM0 comments


Set a Breakpoint and Start Debugging in Visual Studio

I didn't know I could do this until a few weeks ago: While still in Edit mode, you can right-click on a line of code and select Run to Cursor. Visual Studio will compile your application (if necessary), start your application in Debug mode and stop on the line you've selected. If your cursor is already on the line where you want to stop, you don't need to touch your mouse -- just press Ctrl+F10 to get the same result. Once Visual Studio stops on your line, you can set more permanent breakpoints by pressing F9.

In retrospect, this was obvious: I've used Run to Cursor while in Debug mode for years. And, to add insult to injury, every time I've right-clicked on a line of code in Edit mode, Run to Cursor has been right there on the shortcut menu. I just never thought to use it until recently.

Posted by Peter Vogel on 09/09/2014 at 2:21 PM0 comments


To Sync or Not to Sync

One reader's comments in an article I wrote about Entity Framework's async programming  turned into an interesting discussion on the role of asynchronous programming in the modern world (interesting for me, at any rate). Back in the day, I used to tell developers that the surest way to create a bug they'd never be able to track down was to write what we used to call "multi-threaded applications." I gave seminars on designing multi-threaded application where, perversely, I spent the first five minutes explaining why you shouldn't do multi-threading unless you absolutely had to. And then, of course, I'd go home and write multi-threaded applications for my clients: do as I say, not as I do.

Obviously, multi-core processors and the new async tools in the .NET Framework have changed the environment tremendously. But I discovered during the discussion that I still think of asynchronous programming as something you do when you have specific issues in your application that only asynchronous programming will solve. In my mental model, I would write my code synchronously, see where I had responsiveness issues, and then rewrite those parts of the application to use asynchronous code (which, on occasion, could trigger some architectural changes to the application). Only if I could see the responsiveness problems in advance would I start by writing asynchronous code. But I was always doing it as an alternative to my default mode: writing synchronous code.

The commenters challenged that, effectively saying that (in many cases) asynchronous programming should be the developer's default choice. As one reader pointed out, a blocked thread can take up to a megabyte of memory while it's idling. Integrating async programming can eliminate that loss.

Of course, there is a question of whether you care about that megabyte: for about $10,000, I can get a server with 256 gigabytes of RAM -- that's over a quarter of a million of those megabytes that we were worrying about saving. The fully loaded chargeback for a computer programmer would swallow that ten grand in a couple of days; so if the programmer is spending much "extra" time to save the odd megabyte, it doesn't make fiscal sense.

But here's the point: If the costs of writing the code asynchronously from the start is "trivial" (to quote another reader), shouldn't I be writing asynchronously from the beginning? You wouldn't need to buy the server, and while the incremental cost for the developer time in writing async from the start might not be zero, it could easily be negligible.

It's a powerful argument. I don't think I'm there yet (I still see the async tools as making it easier to do asynchronous programming when I need to do it), but I may be coming around. I still worry about those bugs you'll never track down, though. The exception EF raises when executing multiple asynchronous processes on the same context seem to me to be the kind of problem that wouldn't occur during development, but would raise its ugly head in production, for example. But I may be worrying unnecessarily.

Posted by Peter Vogel on 07/08/2014 at 7:29 AM0 comments


Return an Interface from Your Methods for Maximum Flexibility

If I'm writing a method that returns a collection, I can, of course, declare my method's return type using a class name, like this:

Public Function GetCustomersByCity(City As  String) Returns List(of Customer)

But by declaring my return type as class in this way, I restrict my method to only returning that class (in this example, a List). I might eventually want to rewrite the method to return some other class, but my overly-specific return type will prevent me from doing that. A much better practice is to just specify an interface name when returning a collection. That allows me to return any class I want, provided I pick a class that implements the interface I choose.

You want to choose an interface that applies to the maximum number of classes (giving you maximum flexibility in deciding what class to use), while also exposing all the functionality that someone using your method will want to use (giving your clients exactly as much flexibility as you want). There's going to be some conflict here because, presumably, the most common interface is going to be the one with the least functionality. Microsoft gives you at least three choices: IQueryable, IList and IEnumerable.

From the point of view of supplying functionality, if you just want to give your users the ability read the entries (i.e. loop through the collection with a For…Each loop or apply LINQ queries to it), any of these interfaces will do. If you want to give the application that's calling your method the ability to add or remove items from a collection, you'll want to return the IList interface (that does restrict your method to returning classes that support adding and removing items, which means, for example, that you won't be able to return an array from your method).

From the point of view of giving yourself maximum flexibility, IEnumerable is your best choice (both IList and IQueryable inherit from IEnumerable). A quick, non-exhaustive survey suggests to me that IQueryable is your most limiting choice (you can't return a List from a method with a return type of IQueryable). But performance matters also: IQueryable is the right choice for LINQ queries running against Entity Framework, because IEnumerable doesn't support server-side processing or deferred execution the way IQueryable does.

Summing up, my current best advice is: Use IList if your clients need to change the collection; IQueryable if your method is returning an Entity Framework result; IEnumerable for everything else.

I bet I'm going to get comments about this advice …

Posted by Peter Vogel on 06/28/2014 at 4:36 AM0 comments


The Easy Way to Change Your Visual Studio Color Scheme

For all the complaints about the muted color scheme in Visual Studio 2012, it's actually pretty easy to change the colors. Just go to Tools | Options and you'll find that the Environment node includes a Visual Theme choice (there's no equivalent feature in Visual Studio 2010, but then, no one complains much about Visual Studio 2010's color scheme).

Even in Visual Studio 2012, though, the Visual Theme choice just gives you a list of pre-defined muted color schemes: What if you don't like any of them, either? It is possible to change the colors individually for Visual Studio's components through Tools | Options | Environment | Fonts and Colors -- but only in the same sense that, if you're trapped by yourself at the South Pole, it is possible to remove your own appendix. The good news here is that Microsoft has theme editors for Visual Studio 2010, 2012, and 2013 (the editors are also available as Visual Studio extensions from Visual Studio's Tools menu).

Download the editor, install it, restart Visual Studio and you're ready to go. In Visual Studio 2010 you'll find a new top-level menu called Theme; in Visual Studio 2012, you'll find a Change Color Theme choice on your Tools menu. From the dialog that either choice opens you can pick a pre-defined theme or modify an existing theme to create your own, custom theme. The editor for Visual Studio 2012 provides a much better experience for changing colors than the Visual Studio 2010 version (the Visual Studio 2012 version gives you more feedback on the results of your change) and throws in the ability to export your custom theme to share it among other copies of Visual Studio. But then, I guess, Visual Studio 2012 needs more help here.

Posted by Peter Vogel on 06/27/2014 at 6:55 AM0 comments


How to Concatenate Collections into Strings

I was writing a For…Each loop yesterday to create a comma-delimited string from the properties of objects in a collection (and, thanks to LINQ, I don't write many For…Each loops any more). As I was typing in my code I noticed that the String class's Join method now accepts a collection as one of its parameters. That let me delete my whole For…Each loop because, with this new version of Join, all I have to do is pass the Join method the separator I want between my strings (in this case, a comma) and a collection of strings.

Since the collection I wanted to process was a collection of Customer objects, I did need to include a lambda expression to specify which property on the Customer object I wanted to concatenate (I could also have overridden my Customer's ToString method to return the value I wanted to concatenate). So, in the end, all I needed was this:

Dim res = String.Join(",",  db.Customers.Select(Function(c) c.FirstName)) 

I got back the string "Peter,Jan,Jason…" and so on.

One sad thing: This feature is only available in .NET 4.5. I'm not saying that, all by itself, this feature is a compelling reason to upgrade, but I am now lobbying a little bit harder to get my clients using older versions of .NET to upgrade.

Posted by Peter Vogel on 06/17/2014 at 8:19 AM0 comments


One Simple Tip to Fix the ObamaCare Web Site

According to the Time magazine article about the team that fixed the ObamaCare site, the first thing the team did was build in a cache (in fact, they were horrified to discover that the site was built without a server-side cache for frequently used data). If you're reading this Web site, you already knew about that and how easy it is to implement using either the ASP.NET Cache object on the server or local storage on the client. It could have been you on that team.

There's even better news: With .NET 4, a MemoryCache object is available in the System.Runtime.Caching library, and you can use it in non-ASP.NET applications.

Posted by Peter Vogel on 06/09/2014 at 11:21 AM0 comments


Writing Flexible Entity Framework Methods with Set

Every once in a while I find myself writing one set of code to do something to the Customers collection and then writing almost identical code work with the Orders collection. When I spot that duplicate code, I use the Set method on the DbContext object to find the collection of entities I want and write the code once. I just pass the Type of the object I want to the Set method.

For instance, I could write code like this to work with the Orders collection:

res = ctx.Orders

If I use the Set method, I just have to pass the type of the entity in the collection. For the Orders collection, that's the Order class:

res = ctx.Set(GetType(Order))

You can use the Set method to create general purpose methods in at least two ways. First, you can pass the type of the object to a method that accesses the DbContext object:

Public Sub MyMethod(EntityType As Type)
  res = ctx.Set(EntityType)
  '…code to work with the collection    
End Sub

Alternatively, you can write a generic method and pass the type when you call the method:

Public Sub MyMethod(of T As Class)()
    res = ctx.Set(GetType(T))
End Sub

Posted by Peter Vogel on 05/30/2014 at 9:58 AM0 comments


Making Generic Classes Useful with Constraints

Many developers are aware that they can write a method that works with many different kinds of objects without using the Object data type: Just write a generic class or method. For example, this method will work with any class, provided that the developer specifies the class when calling the method:

Public Sub MyMethod(Of T)()
  Dim im As T
  '...using the variable im
End Sub

To use this method with a Customer class, a developer would write code like this:

 MyMethod(of Customer)

The problem is that you can't do much with the variable declared as T. You can't instantiate the class with code like this, for example:

Public Sub MyMethod(Of T)()
  Dim im As T
  im = New T
  '...using the variable im
End Sub

That is, unless you promise to .NET that the class will have a constructor (in Visual Basic, a New method) that accepts no parameters. A version of the method that would let you instantiate the class would look like this:

Public Sub MyMethod(Of T As New)()
  Dim im As T
  im = New T
  '...using the variable im
End Sub

Of course, you do have to specify a Class that has a parameterless constructor when you call the method -- you've constrained the number of classes that will work with this method.

You can specify other constraints on the class: that the class must inherit from some other class and/or implement a specific interface, for example. If you do, you'll be able to use methods or properties defined in those classes or interfaces in your method. You can even combine multiple constraints by enclosing them in curly braces ({}).

The following example requires that the class used in the method inherit from Customer, implement the ICreditRating interface, and have a parameterless constructor. That doesn't leave a lot of classes that can be used with this method, but the trade-off is that you can do more with the class in the method:

Public Sub MyMethod(Of T As {New, Customer, ICreditRating})()
  Dim im As T
  im = New T
  im.FirstName = "Peter"
End Sub

Posted by Peter Vogel on 05/27/2014 at 11:14 AM0 comments


Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.