NH Prof New Feature: Alert on bad ‘like’ query
Originally posted at 12/3/2010
One of the things that are coming to NH Prof is more smarts at the analysis part. We now intend to create a lot more alerts and guidance. One of the new features that is already there as part of this strategy is detecting bad ‘like’ queries.
For example, let us take a look at this
This is generally not a good idea, because that sort of query cannot use an index, and requires the database to generate a table scan, which can be pretty slow.
Here is how it looks like from the query perspective:
And NH Prof (and all the other profilers) will now detect this and warn about this.
In fact, it will even detect queries like this:
Comments
That makes sense. Is there a "right" way to allow in-string search?
Why did you choose to make this alert an error as opposed to an alert? Are there not times when an open query like this is necessary?
Nice feature! I supposed the equivalent for dates would be really easy to implement as well :)
That should be date parts.
Variant,
You use the database full text indexes, or Lucene.
David,
Honestly, I've met a lot of developers who really don't understand how SQL works under the hood, so they might not be aware that this type of query does a full table scan. Especially when the code they write is .Contains("EF").
Is there any way to use full text indexes without having to resort to HQL? I use LINQ to implement a specification pattern and can't think of a way to introduce HQL to this.
David,
You write a linq extension