I have been using the following code for years to create a SQL string from an expression with the parameters filled in:
public static string ToUnparameterizedSelectStatement<T>(this SqlExpression<T> expression)
{
string s = expression.ToSelectStatement();
foreach (var p in expression.Params)
{
s = s.Replace(p.ParameterName, p.Value.ToString());
}
return s;
}
I recently stumbled across the ToMergedParamsSelectStatement on SqlExpression which I thought would be a good idea to use and replace my custom code with the built in one.
However this gave me a serious performance regression as seen in the before and after below. Any idea why this would be?
To find out what the differences are, check the implementations, here’s ToMergedParamsSelectStatement()
And MergeParamsIntoSql() which it calls:
Which uses Regex which is obviously going to perform worse vs a simple string replace in a micro benchmark. Also you’re just doing a simple string embed whereas OrmLite uses the registered converters to embed the proper quoted db value. Your solution is going to break when replacing parameters which share the same prefix, e.g. @p1 and @p10. I also remember having to update this once to handle a corner case you’ve reported.
You would only use this once in a query, i.e. it’s not called in a hotspot loop.
There’s a hot spot I have where this is being called where I want to use the result of ToMergedParamsSelectStatement to check if this query exists in a cache before hitting the database.
Maybe there’s a better way to generate a key for a cache based on a SqlExpression?
Based on any SqlExpression no, you can keep using you’re impl since it doesn’t have to be correct and just needs to generate a unique string.
Generally I’d be caching higher up and using a serialized Request DTO or RawUrl as the cache key or for internal queries just a name + serialized args, I.e. never a generic SqlExpression.
