Efficient Dynamic Query - Part 2

Continue from the previous post "Efficient Dynamic Query - Part 1", someone actually requested me to write more detail about the solution of the problem.

We know the root cause, we know that putting input parameters null value check in the SQL WHERE condition, no matter how, it will perform table index scan. Therefore, I would like to share how should we write a stored procedure or C# code with ADO.net to prevent this thing happen.

First, we should only include those necessary data filter into the query. No more @input parameter IS NULL check insider WHERE condition!

Secondly, try to avoid using LIKE operator, I know it is not quite possible. However, if we use CONTAINS or FREETEXT (provided the SQL Server is enabled with Full-Text Search feature), there are pro and con we need to take note. The pro is good and fast for text search in large VARCHAR size data column; the con is you need to run a propagation job to build the full text index of the table and at this time the SQL server will be heavy load and probably will cause table lock. Normally the full text index population job run during non peak time.

The concept is to form the SELECT query string first, then form the WHERE condition, perform value check against input parameter, if the parameter value is null, do not include it into WHERE condition.

Yes, it sounds tedious but it is worth your time.

Stored Procedure:

CREATE PROCEDURE [dbo].[Profiles_Search]
  @firstName VARCHAR(50) = NULL,
  @lastName VARCHAR(50) = NULL,
  @hobbies VARCHAR(50) = NULL
AS
BEGIN

 DECLARE @query NVARCHAR(300)
 DECLARE @filter NVARCHAR(300)
 DECLARE @paramDefinition NVARCHAR(300);

 SET @query = 'SELECT * FROM dbo.Profiles '
 SET @filter = ''
 
 IF @firstName IS NOT NULL
  SET @filter = @filter + 'AND FREETEXT (FirstName, @firstName) ';

 IF @lastName IS NOT NULL
  SET @filter = @filter + 'AND FREETEXT (LastName, @lastName) ';

 IF @hobbies IS NOT NULL
  SET @filter = @filter + 'AND FREETEXT (Hobbies, @hobbies) ';
  
 IF @firstName IS NOT NULL OR @lastName IS NOT NULL OR @hobbies IS NOT NULL
 BEGIN
  --Remove the first AND before concate it with WHERE
  SET @filter = SUBSTRING(@filter, 4, LEN(@filter)) 
  SET @query = @query + 'WHERE' + @filter
 END

 SET @paramDefinition = '@firstName VARCHAR(50), @lastName VARCHAR(50), @hobbies VARCHAR(50)'
 PRINT 'Query: ' + @query
 PRINT 'Parameters: ' + @paramDefinition
 EXEC sp_executesql @query, @paramDefinition, @firstName, @lastName, @hobbies
END

Or, you can use LIKE by replacing the FREETEXT with LIKE such as following example, but be warned that the wildcard at the front of expression will still cause table index scan.

IF @firstName IS NOT NULL
 SET @filter = @filter + 'AND FirstName LIKE ''%'' + @firstName + ''%'' ';

IF @lastName IS NOT NULL
 SET @filter = @filter + 'AND LastName LIKE ''%'' + @lastName + ''%'' ';

IF @hobbies IS NOT NULL
 SET @filter = @filter + 'AND Hobbies LIKE ''%'' + @hobbies + ''%'' ';

WARNING: Putting logic into stored procedure is a bad practice, use at your own risk, and if you have any better idea, feel free to share with me.

Or, if you do not like or do not use stored procedure, you can have the logic coded in C#. Following is the example code with Enterprise Library 5.0, but the code is not completely shown here. I just put code snippet to show the idea only how to form the query which only include the necessary filter to the WHERE condition.

C#:

public List<Profile> Select(string firstName, string lastName, string hobbies)
{
    string SQL_STATEMENT =
        "SELECT * FROM Profiles {0}";

    List result = new List();

    // Connect to database. DatabaseFactory comes from enterprise lirary
    Database db = DatabaseFactory.CreateDatabase(base.ConnectionName);
    using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
    {
        // Filter construction.
        string filter = string.Empty;

        filter += BuildFilter(db, cmd, "FirstName", firstName);
        filter += BuildFilter(db, cmd, "LastName", lastName);
        filter += BuildFilter(db, cmd, "Hobbies", hobbies);

        //Remove the first AND
        if (!string.IsNullOrEmpty(filter))
            filter = "WHERE " + filter.Remove(0, 4);

        // Statement construction.
        string sqlCmd = string.Format(SQL_STATEMENT, filter);

        cmd.CommandText = sqlCmd;

        using (IDataReader dr = db.ExecuteReader(cmd))
        {
            while (dr.Read())
            {
                Profile profile = LoadProfile(dr);
                result.Add(profile);
            }
        }
    }

    return result;
}

private string BuildFilter(Database db, DbCommand cmd, string columnName, 
    string columnValue)
{
    string filter = string.Empty;
    string paramName = string.Format("@{0}", columnName);

    if (!string.IsNullOrWhiteSpace(columnValue))
    {
        db.AddInParameter(cmd, paramName, DbType.String, columnValue);
        filter = string.Format(" OR {0} LIKE '%' + {1} + '%'", columnName, paramName);
        //Change LIKE to CONTAINS or FREETEXT here
    }

    return filter;
}

private Profile LoadProfile(IDataReader dr)
{
    Profile profile = new Profile();
    profile.ID = base.GetDataValue(dr, "ID");
    profile.FirstName = base.GetDataValue(dr, "Domain");
    profile.LastName = base.GetDataValue(dr, "Name");
    profile.ContactNo = base.GetDataValue(dr, "ContactNo");
    profile.Email = base.GetDataValue(dr, "Email");
    profile.AboutMe = base.GetDataValue(dr, "AboutMe");
    profile.Interests = base.GetDataValue(dr, "Interests");
    profile.Hobbies = base.GetDataValue(dr, "Hobbies");
    return profile;
}

Thanks to Serena for the BuildFilter method idea.

Above code are repetitive in every where whenever developer creates CRUD operations for application. Actually there is a tool to generate this piece of C# code automatically. For more detail about the code generation, please refer to my previous post "Layered Architecture Solution Guidance (LASG)". Therefore, with the tool, you can actually save your time in doing code copy and paste, then change the parameters, etc. The LASG tool will automatically do it all for you with just a few clicks.

Next topic, I will cover full-text index setup and the performance difference between FREETEXT and LIKE.