Databases
StackOverflow2013
Postsdbo
POHow to map a DataReader to class properties and maintain performance?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to map a DataReader to class properties and maintain performance?
    primarykey
    data
    text
    <p>Preamble:</p> <ol> <li>All data connection strings, connections, etc are created using DbProviderFactories.</li> <li>Code is mixed C# and VB.Net from mulitple libraries.</li> </ol> <p>I am mapping a DbDataReader to entities and have some benchmarks:</p> <pre><code>[0] retrieved 159180 records in 45135 ms [1] retrieved 159180 records in 45008 ms [2] retrieved 159180 records in 44814 ms [3] retrieved 159180 records in 44987 ms [4] retrieved 159180 records in 44914 ms [5] retrieved 159180 records in 45224 ms [6] retrieved 159180 records in 45829 ms [7] retrieved 159180 records in 60762 ms [8] retrieved 159180 records in 52128 ms [9] retrieved 159180 records in 47982 ms </code></pre> <p>This is a significant amount of time and extremely poor considering it only takes 17 seconds to query from Sql Server Management Studio. My select statement:</p> <p>"SELECT * FROM tbl_MyTable"</p> <p>Table contains 43 fields and probably isn't indexed as best as it should; however, performing a <em>select all</em>, I wouldn't expect indexing to be problematic. So ... here is what I am doing:</p> <p><strong>Define an entity:</strong> </p> <pre><code>public class Concept { #region Columns [DataParameter("ConceptID", DbType.Int32)] public Int32 ConceptID { get; set; } [DataParameter("ConceptName", DbType.String)] public string ConceptName { get; set; } [DataParameter("ConceptTypeID", DbType.Int32)] public Int32 ConceptTypeID { get; set; } [DataParameter("ActiveYN", DbType.Boolean)] public bool ActiveYN { get; set; } #endregion } </code></pre> <p><strong>Query DataReader:</strong></p> <pre><code>for (int i = 0; i &lt;= 99; i++) { sw.Start(); var results = session.QueryReader&lt;Concept&gt;( new SqlCommand(command), dr =&gt; new Concept()); sw.Stop(); Console.WriteLine("[{0}] retrieved {1} records in {2} ms", i, results.Count(), sw.ElapsedMilliseconds); sw.Reset(); } </code></pre> <p>... calling: </p> <pre><code>Public Function QueryReader(Of TEntity As {Class, New})(ByVal Command As DbCommand, _ ByVal Projection As Func(Of DbDataReader, TEntity)) _ As IEnumerable(Of TEntity) Dim list As IEnumerable(Of TEntity) Command.Connection = dataReader.NewConnection Command.Connection.Open() Using _reader As DbDataReader = Command.ExecuteReader() list = _reader.Query(Of TEntity)(Projection).ToList() End Using Command.Connection.Close() Return list End Function </code></pre> <p>... and extension method <code>QueryReader&lt;T&gt;</code>: <em>edit placement of new TEntity() - thanks @Henk</em></p> <pre><code>public static IEnumerable&lt;TEntity&gt; Query&lt;TEntity&gt;(this DbDataReader Reader, Func&lt;DbDataReader, TEntity&gt; Projection) where TEntity : class, new() { // moving this reflection to another class Dictionary&lt;string, PropertyInfo&gt; props; while (Reader.Read()) { TEntity entity = new TEntity(); if (!entities.TryGetValue(typeof(TEntity).ToString(), out props)) { // reflection over TEntity props = (from p in entity.GetType().GetProperties() from a in p.GetCustomAttributes(typeof(DataParameterAttribute), false) select p) .ToDictionary(p =&gt; p.Name); entities.Add(typeof(TEntity).ToString(), props); } foreach (KeyValuePair&lt;string, PropertyInfo&gt; field in props) { if (null != Reader[field.Key] &amp;&amp; Reader[field.Key] != DBNull.Value) { field.Value.SetValue(entity, Reader[field.Key], null); } } yield return entity; } } </code></pre> <p><em><strong>Any suggestions on increasing performance would be greatly appreciated ...</em></strong></p> <hr> <h2>Update</h2> <p>I implemented dapper-dot-net as @EtienneT suggested - here are the retrieval times: </p> <pre><code>[0] retrieved 159180 records in 6874 ms [1] retrieved 159180 records in 6866 ms [2] retrieved 159180 records in 6570 ms [3] retrieved 159180 records in 6785 ms [4] retrieved 159180 records in 6693 ms [5] retrieved 159180 records in 6735 ms [6] retrieved 159180 records in 6627 ms [7] retrieved 159180 records in 6739 ms [8] retrieved 159180 records in 6569 ms [9] retrieved 159180 records in 6666 ms </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIAbstract
    1. USIAbstract
    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. POHow to map a DataReader to class properties and maintain performance?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to map a DataReader to class properties and maintain performance?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to map a DataReader to class properties and maintain performance?
      1. USWallace
      1. VTFavorite
    1. CO160K records with 43 fields populating entities via reflection? I'd say 45-50 seconds is pretty decent, but maybe I'm too forgiving.
      singulars
      1. POHow to map a DataReader to class properties and maintain performance?
      1. USAnthony Pegram
    2. CO@Anthony: I think it's *decent* ... but I want **wow** - especially since I use a dictionary to map the reader[field] to the propertyInfo ... but maybe my expectations are too high. ;)
      singulars
      1. POHow to map a DataReader to class properties and maintain performance?
      1. USIAbstract
    3. COJust for fun, try writing an old-fashioned `while(Reader.Read())` loop, Fx 2.0 style. It should give you an idea how expensive all this reflection and indirection is.
      singulars
      1. POHow to map a DataReader to class properties and maintain performance?
      1. USHenk Holterman
 

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