Databases
StackOverflow2013
Postsdbo
POWhy is this hql so much faster than using criteria?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is this hql so much faster than using criteria?
    text
    copied!<p>I'm using nhibernate as ORM. And one of my selections is incredible slow. The thing is, that it takes a long time to generate the sql. I'm sure it's not the sql query itself that's slow, because I timed the query itself with sql profiler. And it showed that there is a ~15 sec gap between starting to execute the nhibernate code, and the query being actually sent to the db. The generated SQL query itself is as fast as I'd expect it to be.</p> <p>The selection code (in a repository) is as follows</p> <pre><code>public IEnumerable&lt;Document&gt; GetAllDocumentsReadyForDeletion() { return from doc in _session.Query&lt;Document&gt;() where doc.StorageType == 'D' select doc; } </code></pre> <p>I also tried:</p> <pre><code>return _session.CreateCriteria&lt;Document&gt;() .Add(Restrictions.Eq("StorageType", 'D')) .List&lt;Document&gt;(); </code></pre> <p>wich is the equivalent (right?). However, they both perform about the same (slow, like 15 secs to generate the sql query)</p> <p>This however, performs as fast as I want it to be, and I have no clue why:</p> <pre><code>return _session.CreateQuery( "from Document doc where doc.StorageType = 'D'") .List&lt;Document&gt;(); </code></pre> <p>I really want to use the linq to nhibernate version. Any idea why the code performs differently? (If you need more details, just ask!)</p> <p><strong>Edit1</strong></p> <p>Oh man, did I make a stupid mistake or what.. I mistakenly read the wrong column in the sql profiler.. The ehm, actual execution time for the first two are ~18 seconds, the 3rd is ~0 secs. I'm trying to find the differences in sql atm...</p> <p><strong>Edit2</strong></p> <p>This is actually becoming a whole different question. The resulting queries are almost exactly the same, except for that the first two are wrapped inside a "exec sp_executesql"</p> <p>Now I've traced this a bit with query analyser, and the slow query has one step:</p> <pre><code>clustered index scan. </code></pre> <p>The fast query has two steps:</p> <pre><code>Index seek Bookmark lookup </code></pre> <p>Any experiences with something like this?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload