Ayende @ Rahien

Refunds available at head office

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

image

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:

image

And NH Prof (and all the other profilers) will now detect this and warn about this.

image

In fact, it will even detect queries like this:

image

Tags:

Posted By: Ayende Rahien

Published at

Originally posted at

Comments

Variant
12/09/2010 10:53 AM by
Variant

That makes sense. Is there a "right" way to allow in-string search?

David Neale
12/09/2010 12:05 PM by
David Neale

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?

Duncan Godwin
12/09/2010 12:16 PM by
Duncan Godwin

Nice feature! I supposed the equivalent for dates would be really easy to implement as well :)

Duncan Godwin
12/09/2010 12:17 PM by
Duncan Godwin

That should be date parts.

Ayende Rahien
12/09/2010 12:40 PM by
Ayende Rahien

Variant,

You use the database full text indexes, or Lucene.

Steve
12/09/2010 07:50 PM by
Steve

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").

David Neale
12/10/2010 10:03 AM by
David Neale

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.

Ayende Rahien
12/11/2010 05:15 AM by
Ayende Rahien

David,

You write a linq extension

Comments have been closed on this topic.