Databases
StackOverflow2013
Postsdbo
PONHibernate LINQ query performance, which code fragment is better?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate LINQ query performance, which code fragment is better?
    primarykey
    data
    text
    <p>This question is asking which of the two approaches below is more encouraged (and with what reasons)?</p> <p>I am working with FluentNHibernate in a ServiceStack REST application using C# 4.0, but this question is general to NHibernate LINQ queries.</p> <p>Is it more encouraged to:</p> <p><strong>(Method 1)</strong> quickly run a <strong>simple</strong> query which returns all rows where it matches the user's id:</p> <pre><code>// Query the User by id var user = session.Get&lt;User&gt;(request.UserId); </code></pre> <p>and then separately use LINQ on the returned List to further narrow down the results:</p> <pre><code>// 'User' contains a 'List&lt;Location&gt;' var locations = user.Locations.Where(location =&gt; location.Timestamp &gt;= (request.MinTimestamp.HasValue ? request.MinTimestamp.Value : 0) &amp;&amp; location.Timestamp &lt;= (request.MaxTimestamp.HasValue ? request.MaxTimestamp.Value : DateTime.Now.ToTimestamp())); return locations; </code></pre> <p><strong>(Method 2) or</strong>, is it more encouraged to run a <strong>more complicated</strong> query which does the above in a single query:</p> <pre><code>var locationsQuery = session.QueryOver&lt;LocationModel&gt;() .Where(table =&gt; table.User.Id == request.UserId) .And(table =&gt; table.Timestamp &gt;= (request.MinTimestamp.HasValue ? request.MinTimestamp.Value : 0)) .And(table =&gt; table.Timestamp &lt;= (request.MaxTimestamp.HasValue ? request.MaxTimestamp.Value : DateTime.Now.ToTimestamp())); return locationsQuery.List(); </code></pre> <p><strong>if</strong> my goals are:</p> <p>a) faster execution time</p> <hr> <p><b>Benchmarks (revised)</b></p> <p>Revised Complete Test Code: <a href="http://pastebin.com/0ykKwcxX" rel="nofollow">http://pastebin.com/0ykKwcxX</a></p> <p><strong>Benchmarks Output:</strong></p> <p><strong>Method 1</strong> took <strong>147.291 seconds</strong> over <strong>5000 iterations</strong>.</p> <p><em>Query results of the last iteration:</em> <br> { Timestamp=1348659703485, Latitude=179.40000, Longitude=209.40000 } <br> { Timestamp=1348659703486, Latitude=179.55000, Longitude=209.55000 } <br> { Timestamp=1348659703487, Latitude=179.70000, Longitude=209.70000 } <br> { Timestamp=1348659703488, Latitude=179.85000, Longitude=209.85000 } <br> { Timestamp=1348659703489, Latitude=180.00000, Longitude=210.00000 } <br></p> <p><strong>Method 2</strong> took <strong>133.728 seconds</strong> over <strong>5000 iterations</strong>.</p> <p><em>Query results of the last iteration:</em> <br> { Timestamp=1348659703485, Latitude=179.40000, Longitude=209.40000 } <br> { Timestamp=1348659703486, Latitude=179.55000, Longitude=209.55000 } <br> { Timestamp=1348659703487, Latitude=179.70000, Longitude=209.70000 } <br> { Timestamp=1348659703488, Latitude=179.85000, Longitude=209.85000 } <br> { Timestamp=1348659703489, Latitude=180.00000, Longitude=210.00000 } <br></p> <p><b>Difference: Method 2 was approximately 13.5 seconds faster.</b></p> <hr> <p>b) long-term re-use and stability</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJason
    1. USJason
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. USHandprint
      1. VTFavorite
    3. VO
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIn terms of execution time, why don't you test it and see?
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. USTim Rogers
    2. COWarmup: 14.518 sec (10,000 cycles, doesn't count) Simpler Method: 12.326 sec (10,000 cycles) Second Method: 23.449 sec (10,000 cycles)
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. USJason
    3. COI'm quite surprised that the second method appeared to be slower. Have you checked the SQL queries that were executed by NHibernate? Could you please post them?
      singulars
      1. PONHibernate LINQ query performance, which code fragment is better?
      1. USlowleveldesign
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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