Databases
StackOverflow2013
Postsdbo
POHow to get the best performance from LINQ querying several rows in a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get the best performance from LINQ querying several rows in a table
    primarykey
    data
    text
    <p>my goal is to get lots of rows from a translation table. I use an ID to get a subset of the table (say 50 rows) then I use another ID to the rows I want from this subset. Using typed datasets I do the following to get the main dataset: </p> <pre><code>funderTextsDS.tbl_funderTextsDataTable fd = (funderTextsDS.tbl_funderTextsDataTable)(new funderTextsDSTableAdapters.tbl_funderTextsTableAdapter()).GetData(); </code></pre> <p>Then for each value I want to get: </p> <pre><code>fd.Select("eng_code = '" + element + "' and funderID = '" + funderID + "'")[0]["funderText"].ToString(); </code></pre> <p>Using ANTS profiler to check the code I found that this method used about 170ms over 10 page refreshes (220 calls to the fd.select...) </p> <p>When I rewrote this to LINQ it took more than 2000ms to do the same work. Here is the LINQ code I used: </p> <pre><code>IrmDatabaseContext irmDB = new IrmDatabaseContext(); irmDB.tbl_funderTexts.Single(f =&gt; f.funderID == funderId &amp;&amp; f.eng_code == element).funderText; </code></pre> <p>Anyone have a good way of doing this with LINQ? By looking into sql server profiler i saw that the LINQ actually generated a single select for each text i retrieved. (ie LINQ= 220 selects from the db, tableadapter method = 10 selects) </p> <p>Solution: After having read around the net I found that David B was on the right track, although the for loop threw me off for quite a while. Anyway, the trick as he said, is to use a list as this actually forces linq to run the query against the DB and cache it localy. <a href="http://blogs.msdn.com/wriju/archive/2007/07/17/linq-to-sql-caching-the-query-execution.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/wriju/archive/2007/07/17/linq-to-sql-caching-the-query-execution.aspx</a>. </p> <p>So my solution ended up like this: </p> <pre><code>List&lt;tbl_funderText&gt; fd = (from tf in irmDB.tbl_funderTexts where tf.funderID == (int)cpcrow.cpc_fundingPartnerID select tf).ToList(); </code></pre> <p>Then everytime I want an element I do: </p> <pre><code>fd.Single(f =&gt; f.eng_code == element).funderText; </code></pre> <p>Analyzing with ANTS I then found that time was reduced to 150ms (about the same as the tableAdapter. SQL query analyzer shows that the SQL is run only one time.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmat
    1. USdevzero
    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
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to get the best performance from LINQ querying several rows in a table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCould someone format this a bit better so it's easier to read.
      singulars
      1. POHow to get the best performance from LINQ querying several rows in a table
      1. USNathan W
 

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