Practical .NET

Speed Up Your Application with Stored Procedure and Temporary Tables

Stored procedures can speed up your code by reducing trips to your database -- even if you only have one SQL statement to execute. Here's how to speed up your application (and how to simplify your stored procedure code).

• By Peter Vogel
• 04/15/2015

As far as performance goes, the real benefit of stored procedures usually happens when you bundle several SQL statements into a single stored procedure. That's because the second-slowest thing you can do in a business application (right after reading and writing to your hard disk) is to send a request to another computer for processing. Reducing trips to the database is the kind of change that gets you the performance gain that matters -- the kind where your users say, "Hey, that was faster!"

You can get that benefit even if you only have one SQL statement in your stored procedure, provided you use that statement multiple times. It's not unusual, for example, to see code like this that repeatedly calls a SqlCommand object within a loop (this is an ADO.NET example, but you can do the same thing with Entity Framework either by calling a stored procedure or using SaveChanges inside a loop):

Dim cmdSql As SqlCommand = New SqlCommand("…")
cmdSql.Parameters.Add(New SqlParameter("@sId", SqlDbType.VarChar, 10))

For Each cc As MyClass In CollectionOfClasses
  cmdSql.Parameters("@sId").Value = cc.Id
  cmdSql.ExecuteNonQuery()
Next

With this code, each pass through the collection triggers another trip to the database. It would be much faster to pass all the values to the stored procedure at once and make a single trip to the database to process all of them. You'd get the performance gain that matters even with only one SQL statement in the stored procedure.

Passing and Processing Multiple Parameters
The first step in passing multiple parameters at once to a stored procedure is to create a single string out of all of the values, separating each value with a character that doesn't appear in the values themselves. The following example uses a collection called NewHires containing a class with an Id property. The code uses the Join extension method to concatenate the Id property of all of the objects in the collection into a single string, with each Id separated by a comma (this code also eliminates any duplicate Ids):

cmdSql.Parameters("@sIds").Value = String.Join(
  ",", NewHires.Select(Function(cc) cc. Id).Distinct)

You can use this code almost as is -- just change the name of your collection and the property you want to use.

Now, in the stored procedure, you need to define a parameter to accept that string (in this example, I've called the parameter sIds):

Create PROCEDURE [dbo].[MyStoredProcedure]
	@sIds text
As

You could now loop through that string of parameters using the T-SQL version of a For…Each loop. However, that's a very non-SQL way of doing things (and, I expect, extremely inefficient). Furthermore, in a stored procedure with multiple SQL statements, you could end up having to recreate that For…Each loop multiple times. You'd have a complicated and hard-to-read stored procedure.

A more SQL-compatible way of doing things is to load each of the parameters into an in-memory table. It's much easier to integrate that table into any of the queries in your stored procedure.

The first step in implementing this plan is to define a table that will have a row for each value in the values string passed to your stored procedure. This example creates a temporary table with a single column called tempId (the # at the start of the table name flags the table as temporary):

Create Table #tempIds (tmpId nvarchar(20) Primary Key)

The next step is to split up the list of values at each comma and insert each value into the table. The code to do that looks like this:

Insert into #tempIds (tmpId)
  Select I.Value  
  From dbo.fnSplit(@sIds, ',') I

The magic in this code is the fnSplit function, which I've "borrowed" from one of my clients. The fnSplit function accepts the values string and the character that separates the values; the function returns a table of the values it finds, suitable for use in a SQL Insert statement. You can find the function in Listing 1.

Create Function [dbo].[fnSplit] 
  (
    @List      varchar(8000), 
    @Delimiter varchar(5)
  ) 
  Returns @TableOfValues table 
    ( 
      RowID   smallint Identity(1,1), 
      [Value] varchar(50) 
    ) 
As 

Begin
  Declare @LenString int 

  While len(@List) > 0 
    Begin          
      Select @LenString = 
        (Case charindex(@Delimiter, @List) 
          When 0 Then len(@List) 
          Else (charindex(@Delimiter, @List) -1)
        End) 
                                
      Insert Into @TableOfValues 
        Select substring(@List, 1, @LenString)                

      Select @List = 
        (Case (len(@List) - @LenString) 
          When 0 Then '' 
          Else right(@List, len(@List) - @LenString - 1) 
        End) 
    End          
  Return      
End

Now that your values are in a table, you can use SQL commands to join that table to other tables for processing. Here's a SQL statement that uses the values in the temporary table to retrieve rows from one table and insert them into another table:

Insert Into Employees (FirstName, LastName, Status)
  Select N.FName, N.LName, 'Hired'
    From NewHires N Join #tempIds T
      On N.Id = T.Id

If you're going to go to the trouble of using stored procedures, you might as well wring every ounce of performance out of them that you can. Concatenating your values into a string and then making one trip to the database instead of several will let you do that. Even your users will notice.