ADO.NET SQL Shortcut - If Exists

Don't Use Select * to Find if a Record Exists

by Les Smith

Is there a faster way to determine if a record exists in a database table than using a "Select * From Table" query.  Yes; use an "If Exists" Dynamic Sql or Stored Procedure to increase performance of the query.

Many times you need to determine if a record exists in a table, and you might have used code such as shown below.

Private Function IsUserExtant(ByVal user As String) As Boolean       Dim dt As New DataTable       Dim SQL As String       Dim conn As New SqlConnection(ConnectionString)       SQL = "select * from tbl_users "       SQL &= "where username = '" & user & "' "       Dim cmd As New SqlCommand(SQL, conn)       ' the DataAdapter will open and close the connection       ' automatically, so we don't bother with that       Dim da As New SqlDataAdapter(cmd)       da.Fill(dt)       ' if dt.rows.count > then return true, else false       Return dt.Rows.Count > 0    End Function

The problem with this approach, although it may well be the most obvious and simplest to code, since most of us use DataTables all of the time to retrieve data, is that there is overhead associated with this code that is unnecessary.  Since a DataTable is being returned, not only have we retrieved more columns than we need, but the Schema data describing the DataTable rows, columns, field types, sizes, etc., is all brought along as unneeded baggage.  Additionally, I created DataTable and DataAdapter objects that are not needed to get the information that I want.

The following set of code is much more desirable for several reasons.  I will show you the code and then explain it.

Private Function IsUserExtantFast(ByVal user As String) As Boolean       Dim SQL As String       Dim conn As New SqlConnection(ConnectionString)       SQL &= "if exists(select username from tbl_stats_assigned "       SQL &= "where username = '" & user & "') "       SQL &= "          select 1 as extant "       SQL &= "else "       SQL &= "         select 0 as extant "       Dim cmd As New SqlCommand(SQL, conn)       cmd.Connection.Open()       Dim i As Integer = cmd.ExecuteScalar()       conn.Close()       Return i > 0    End Function

In the first place, I am not using a DataTable, consequently no DataAdapter is required.  Also, no schema data is being built.  We have two less objects and much less overhead.  Obviously, as an isolated case, this is not a big deal, but if the procedures were being called in a loop, the creation of two extra data objects for every call is very significant with regards to instantiation and garbage collection of those objects.  Additionally, with either set of code, if they are being called in a loop, you would want to open a connection and keep it open for the life of the loop.

A better solution yet is to use a Stored Procedure as shown below.  Using a Stored Procedure is always better than using Dynamic SQL for many reasons.



       CREATE PROCEDURE sp_IsUserExtant
                                         @username as varchar(50)

        AS
    

        if exists(select username from tbl_stats_assigned where username = @username)
           Select 1  
       else
          Select 0  

       GO

Next, I will write the code to call the Stored Procedure as follows.  Again, I will use ExecuteScalar to avoid creating the DataTable and DataAdapter objects.

Private Function IsUserExtantCallSP(ByVal user As String) As Boolean       Dim conn As New SqlConnection(ConnectionString)       Try          conn.Open()          Dim cmd As New SqlCommand("QuickCheck", conn)          cmd.CommandType = CommandType.StoredProcedure          cmd.Parameters.Add("@username", CObj(user))          Dim i As Integer = cmd.ExecuteScalar          Return i > 0       Catch ex As System.Exception          MsgBox(ex.ToString)          Return False       Finally          Try             If Not conn Is Nothing AndAlso conn.State = ConnectionState.Open Then                conn.Close()             End If          Catch          End Try       End Try    End Function

ADO.NET has so many ways to do things, but with flexibility comes the responsibility to choose the best way for the task that you are attempting to perform.  If you do, you get good results returned and you get optimum performance.  If you choose unwisely, you can reap the consequences of bad performance and not even be aware of it.