Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It looks like you're doing a lot of unnecessary nested querying.</p> <p>Your <code>core</code> query is doing some relatively expensive filtering and sorting before returning items. It's best to only execute this query once.</p> <p>However, you're performing six unnecessary joins back on this query.</p> <p>Your query <code>Genders</code>, for example, is requerying <code>core</code> an only keeping the items that have the same <code>IndicatorId</code> that you already grouped by! If I can assume that <code>item.Indicator</code> is one-to-one on <code>item.IndicatorId</code> then your group <code>indicator</code> already contains this subset.</p> <p>You're querying for <code>AreaTypes</code> &amp; <code>Sectors</code> in the same way.</p> <p>Now each of <code>HasGender</code>, <code>HasAreaType</code>, &amp; <code>HasSector</code> each <strong>repeat</strong> the above queries and force a <code>.Count()</code> on each of them only to check if the value is greater than zero. This is a waste since <code>.Any()</code> will check for at least one value much more cheaply for you.</p> <p>Now to test how many times the <code>core</code> query is being accessed I created this test code:</p> <pre><code>var countryIDs = Enumerable.Range(0, 100).ToArray(); var indicatorIDs = Enumerable.Range(0, 100).ToArray(); data.Items.AddRange( Enumerable .Range(0, 100) .Select(n =&gt; new Item() { CountryId = n, IndicatorId = n, Indicator = "Indicator", GenderId = n, Gender = "Gender", AreaTypeId = n, AreaType = "Area", SectorId = n, Sector = "Sector", })); </code></pre> <p>I modified <code>core</code> to look like this:</p> <pre><code>var counter = 0; var core = (from item in data.Items where countryIDs.Contains(item.CountryId) &amp;&amp; indicatorIDs.Contains(item.IndicatorId) orderby item.Indicator select item).Do(_ =&gt; counter++); </code></pre> <p>The <code>Do</code> operator is from the Reactive Extensions <code>System.Interactive</code> assembly.</p> <p>Running your code I got the following result:</p> <pre><code>counter == 60100 </code></pre> <p>Since I have put 100 items in the collection this tells me that your query is invoking a new execution of <code>core</code> 601 times!</p> <p>This can be changed to execute <code>core</code> once quite easily.</p> <p>First I modified <code>core</code> to look like this:</p> <pre><code>var query = from item in data.Items where countryIDs.Contains(item.CountryId) &amp;&amp; indicatorIDs.Contains(item.IndicatorId) orderby item.Indicator select item; var core = query.ToArray(); </code></pre> <p>The <code>.ToArray()</code> brings the results of the query into memory.</p> <p>The <code>x</code> query was then modified to look like this:</p> <pre><code>var x = from item in core.Distinct() group item by new { item.IndicatorId, item.Indicator } into indicator let Genders = ( from g in indicator where g.Gender != null select new Gender { GenderID = g.GenderId, GenderDescription = g.Gender, }).ToList() let AreaTypes = ( from rat in indicator where rat.AreaType != null select new AreaType { AreaTypeId = rat.AreaTypeId, AreaDescription = rat.AreaType, }).ToList() let Sectors = ( from s in indicator where s.Sector != null select new Sector { SectorID = s.SectorId, Title = s.Sector, }).ToList() select new Indicator() { IndicatorID = indicator.Key.IndicatorId, IndicatorDescription = indicator.Key.Indicator, Genders = Genders, AreaTypes = AreaTypes, Sectors = Sectors, HasGender = Genders.Any(), HasAreaType = AreaTypes.Any(), HasSector = Sectors.Any(), }; </code></pre> <p>Notice that I am computing each of <code>Genders</code>, <code>AreaTypes</code>, &amp; <code>Sectors</code> once only and creating each as a list. This allowed me to change <code>x</code> to immediately produce instances of <code>Indicator</code>.</p> <p>Now the final creation of the <code>indicators</code> list was simple:</p> <pre><code>var indicators = x.ToList(); </code></pre> <p>When I used my sample data on this method my result was this:</p> <pre><code>counter == 100 </code></pre> <p>This means that this query only hit the original <code>core</code> query once!</p> <p>I then checked how the nesting behaves when I increased the original sample data to 1,000 items - I got a single hit with the new code and 6,001 hits with the original code - and it went much, much slower.</p> <p>Remember that LINQ is lazily computed so execution doesn't occur where you define your query, but where you execute it.</p> <p>So the advice here is that, memory permitting, you should execute your query as soon as practicable to bring your data into memory and then perform your computations once and only once.</p>
    singulars
    1. This table or related slice is empty.
    1. POvery slow conversion of query to list
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USEnigmativity
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POvery slow conversion of query to list
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COblew my mind. wow. thanks, enigmativity!
      singulars
      1. PO
      1. USdork
    2. COi'm awestruck! this is probably the most expert level answer that i ever read on SO! +1 ... and i'm still starring with awe on your answer.
      singulars
      1. PO
      1. USMare Infinitus
    3. COamazing, isn't he, mare?
      singulars
      1. PO
      1. USdork
 

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