Databases
StackOverflow2013
Postsdbo
POUsing multiple ExecuteReader at the same time
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing multiple ExecuteReader at the same time
    text
    copied!<p>I'm reading a db that contains some tables using the ExecuteReader() command. Based on the result of the first Read() result I read in two different tables, as I need the ID returned on the first query to run the second one.</p> <p>The problem is that this search is extremely slow.</p> <pre><code>tuCommand.CommandText = "SELECT * FROM tblTranslationUnit WHERE DocumentId = " + doc.DocumentId; var tuReader = tuCommand.ExecuteReader(); while (tuReader.Read()) { var tu = new TranslationUnit { TranslationUnitId = tuReader.GetInt64(0), DocumentId = tuReader.GetInt64(1), Raw = tuReader.GetString(2), IsSegmented = tuReader.GetBoolean(3), Reader = this, // Ryan: Fixed so that it sets the reader to itself }; using (var propCommand = _dbConn.CreateCommand()) { propCommand.CommandText = "SELECT * FROM tblTranslationUnitProperties WHERE TranslationUnitId = " + tu.TranslationUnitId; var propReader = propCommand.ExecuteReader(); while (propReader.Read()) tu.Properties.Add(GetProperty(propReader)); } yield return tu; } </code></pre> <p>If I remove the second ExecuteReader() the query is really fast</p> <p>I have also tried to put the second ExecuteReader() using a new connection and a new transaction but the result is almost the same</p> <p>Any idea or clue? How can I do this kind of search? Are there any better approach? (I suppose yes).</p> <hr> <p>More details The db structure is :</p> <pre><code> - Document - properties - errors -TranslationUnits - properties - errors - Segments - properties - errors </code></pre> <p>So in some parts of the code we will have this structure</p> <pre class="lang-cs prettyprint-override"><code> foreach (document in db) foreach (property in document) foreach (error in document) foreach (translationunit in document) foreach (property in translationunit) foreach (error in translationunit) foreach (segment in translationunit) foreach (property in segment) foreach (error in segment) </code></pre> <p>Based on that, the use of a join to return everything is not a good idea I was thinking if the problem was just a SQLite configuration problem. I mean, if it's possible to add any parameter or similar to tell the system that we are going to use several pointers</p> <p>Now we are moving to a datatable solution:</p> <ol> <li>open a connection</li> <li>read 1000 entries of a table</li> <li>close the connection</li> <li>open a new connection</li> <li>read 1000 entries of the child table</li> <li>close the new connection</li> <li>...</li> </ol>
 

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