Databases
StackOverflow2013
Postsdbo
PONHibernate issues redundant queries with composite keys
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate issues redundant queries with composite keys
    primarykey
    data
    text
    <p>For the sake of the example, let's say that I have to model the "person" entity of the database of my country's revenue service, and that in my very small country the first name and the last name of a person are enough to uniquely identify the person. Additionally, the revenue service's database does not use surrogate keys, and adding a surrogate key to it would zero out the GDP of the country for the next 10 years. </p> <p>The <em>Persons</em> table has three fields:</p> <ul> <li><em>FirstName</em></li> <li><em>LastName</em></li> <li><em>CurrentAddress</em></li> </ul> <p>And, given the size of my country, the table has a <em>unique</em> constraint on the &lt;<em>FirstName</em>, <em>LastName</em>&gt; pair of columns.</p> <p>Given this schema, my very simple <em>Person</em> class has the following members:</p> <ul> <li><em>Key</em>: an instance of a <em>PersonKey</em> class, which in turn has <em>FirstName</em> and <em>LastName</em> members, and of course implements <em>Equals()</em> and <em>GetHashCode()</em>;</li> <li><em>CurrentAddress</em>: a simple string.</li> </ul> <p>The NHibernate mapping looks as follows:</p> <pre><code>&lt;class name="Person" table="Persons" lazy="false"&gt; &lt;composite-id name="Key" class="PersonKey"&gt; &lt;key-property name="FirstName" type="string" column="FirstName"/&gt; &lt;key-property name="LastName" type="string" column="LastName"/&gt; &lt;/composite-id&gt; &lt;property name="CurrentAddress" type="string" column="CurrentAddress" not-null="true" /&gt; &lt;/class&gt; </code></pre> <p>So far so good, this mapping works fine and I can happily load <em>Person</em> entities from the DB. </p> <p>However, when I look under the hood, I can see that when loading the entire set of persons, NHibernate does the following:</p> <ol> <li>Opens a recordset to load key properties only (i.e. exclusively the <em>FirstName</em> and <em>LastName</em> fields) from the <em>Persons</em> table;</li> <li>For each &lt;<em>FirstName</em>, <em>LastName</em>&gt; pair loaded from <em>Persons</em>, it issues a SELECT - of course against <em>Persons</em> as well - to load the CurrentAddress for the person having that <em>FirstName</em> and <em>LastName</em>.</li> </ol> <p>In other words, NHibernate is first loading the keys, and then it issues a series of SELECT's to load each <em>Person</em> separately providing the key in the WHERE clause. </p> <p>Provided that I am not interested in <em>writing</em> to the database, is there a way to tell NHibernate that it could use a single recordset to retrieve <strong>both</strong> key and non-key properties from the table?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGabriele Giuseppini
    1. USGabriele Giuseppini
    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 issues redundant queries with composite keys
      1. USSixto Saez
      1. VTFavorite
    2. VO
      singulars
      1. PONHibernate issues redundant queries with composite keys
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PONHibernate issues redundant queries with composite keys
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUpdate: this happens only when I use `IQuery.Enumerable()`. If I use `IQuery.List()` NHibernate does the smart thing and only queries the table once. However, when I have 3.7 gazillions of persons in the table and my app only needs to process a person at a time, storing all persons in a list is a _bit_ overkill.
      singulars
      1. PONHibernate issues redundant queries with composite keys
      1. USGabriele Giuseppini
    2. COhow does it behave when you use `IQuery.Future<Person>()` ?
      singulars
      1. PONHibernate issues redundant queries with composite keys
      1. USFiro
    3. COThanks for the suggestion. `IQuery.Future<Person>()` behaves differently depending on the driver's capabilities. If I use a driver that does not support multiple queries, but which supports multiple result sets (e.g. OdbcDriver), then `::Future()` operates exactly like `::List()`. On the other hand, if the driver supports multiple queries and no multiple result sets (e.g. SqlClientDriver), then `::Future()` reads the entire recordset in memory (killing the app). Unfortunately NHibernate does not ship with a driver for SQL Server that supports both multiple queries and multiple record sets.
      singulars
      1. PONHibernate issues redundant queries with composite keys
      1. USGabriele Giuseppini
 

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