Ayende @ Rahien

Hi!
My name is Ayende Rahien
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

@

Posts: 5,947 | Comments: 44,541

filter by tags archive

NH Prof New FeatureAlert 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

More posts in "NH Prof New Feature" series:

  1. (09 Dec 2010) Alert on bad ‘like’ query
  2. (10 Dec 2009) Filter static files
  3. (16 Nov 2009) Exporting Reports
  4. (08 Oct 2009) NHibernate Search Integration
  5. (19 Aug 2009) Multiple Session Factory Support
  6. (07 Aug 2009) Diffing Sessions
  7. (06 Aug 2009) Capturing DDL
  8. (05 Aug 2009) Detect Cross Thread Session Usage
  9. (22 May 2009) Detecting 2nd cache collection loads
  10. (15 May 2009) Error Detection
  11. (12 May 2009) Queries by Url
  12. (04 Feb 2009) View Query Results
  13. (18 Jan 2009) Superfluous <many-to-one> update
  14. (18 Jan 2009) URL tracking
  15. (10 Jan 2009) Detecting distributed transactions (System.Transactions)
  16. (06 Jan 2009) The Query Cache
  17. (05 Jan 2009) Query Duration
  18. (24 Dec 2008) Unbounded result sets
  19. (24 Dec 2008) Row Counts

Comments

Variant

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

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

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

Ayende Rahien

Variant,

You use the database full text indexes, or Lucene.

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

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

David,

You write a linq extension

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  2. The RavenDB Comic Strip (2):
    20 May 2015 - Part II – a team in trouble!
  3. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  4. Interview question (2):
    30 Mar 2015 - fix the index
  5. Excerpts from the RavenDB Performance team report (20):
    20 Feb 2015 - Optimizing Compare – The circle of life (a post-mortem)
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats