SqlExpression key for caching

ServiceStack.OrmLite
1

I need an effective way to cache the results of a SqlExpression before calling the database to check if the db call has happened previously in a cache. In this way the database doesn’t need to be hit again for a period of time.

I have come up with the following way to calculate a unique cache key for a SqlExpression and I wanted to get some feedback to see if you believe it will be adequate:

        private static string HashCodeCacheKey<T>(SqlExpression<T> expression)
        {
            long code = 0;
            for (int i = 0; i < expression.Params.Count; i++)
            {
                code += expression.Params[i].Value.GetHashCode();
            }

            if (expression.SelectExpression != null)
                code += expression.SelectExpression.GetHashCode();

            if (expression.BodyExpression != null)
                code += expression.BodyExpression.GetHashCode();

            if (expression.GroupByExpression != null)
                code += expression.GroupByExpression.GetHashCode();

            if (expression.HavingExpression != null)
                code += expression.HavingExpression.GetHashCode();

            if (expression.OrderByExpression != null)
                code += expression.OrderByExpression.GetHashCode();

            if (expression.WhereExpression != null)
                code += expression.WhereExpression.GetHashCode();

            if (expression.Offset != null)
                code += expression.Offset.GetHashCode();

            if (expression.Rows != null)
                code += expression.Rows.GetHashCode();

            return code.ToString();
        }
    }

Out of interest, I used to use a much simpler method that relied on expression.ToSelectStatement() but after doing some benchmarks this turned out to be very non-performant and GC heavy. Using the hash codes seems to do the trick but I wanted to see what you think about this.

2

Have a look at Clone/CopyTo for all the fields you should cover:

3

We ended up using this kind of function:

        public static string ToCacheKey<T>(this SqlExpression<T> expression)
        {
            long code = 0;
            for (int i = 0; i < expression.Params.Count; i++)
            {
                code += expression.Params[i].ParameterName.GetHashCode() * expression.Params[i].Value.GetHashCode();
            }

            if (!string.IsNullOrWhiteSpace(expression.FromExpression))
                code += expression.FromExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.SelectExpression))
                code += expression.SelectExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.BodyExpression))
                code += expression.BodyExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.GroupByExpression))
                code += expression.GroupByExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.HavingExpression))
                code += expression.HavingExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.OrderByExpression))
                code += expression.OrderByExpression.GetHashCode();

            if (!string.IsNullOrWhiteSpace(expression.WhereExpression))
                code += expression.WhereExpression.GetHashCode();

            if (expression.Offset != null)
                code += expression.Offset.GetValueOrDefault();

            if (expression.Rows != null)
                code += expression.Rows.GetValueOrDefault();

            return code.ToString();
        }
4

It’s not a good idea to use a hash key to generate a unique hash. It’s better to use a StringBuilder to build a unique string then hash it with SHA1 for a better unique hash with fast performance. Also IsNullOrWhiteSpace does a full scan & is a lot more expensive than IsNullOrEmpty and you should be careful about which calculated public properties you use to avoid unnecessary string allocations (e.g. BodyExpression is a concatenation of From/Where/Group/Having/Expression with new lines).

I’ve added a ComputeHash() API for how I’d write it:

This API is available from v5.9.3 that’s now available on MyGet.

1 Like