Databases
StackOverflow2013
Postsdbo
POHow best to optimise this small bit of c# Linq code
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow best to optimise this small bit of c# Linq code
    primarykey
    data
    text
    <p>If you take a look at our arcade homepage:</p> <p><a href="http://www.scirra.com/arcade" rel="nofollow noreferrer">http://www.scirra.com/arcade</a></p> <p>In the top right there is a box showing the last people who played this game. In the profiler I'm using, it shows this as taking 900ms to run which is about 80% of the total page load time.</p> <p>The query is relatively simple:</p> <pre><code>// Recent players using (MainContext db = new MainContext()) { var q = (from c in db.tblArcadeGamePlays join a in db.tblProfiles on c.UserID equals a.UserID where c.UserID != 0 select new { c.UserID, c.tblForumAuthor.Username, a.EmailAddress, Date = (from d in db.tblArcadeGamePlays where d.UserID == c.UserID orderby d.Date descending select new { d.Date }).Take(1).Single().Date }) .Distinct() .OrderByDescending(c =&gt; c.Date) .Take(16); </code></pre> <p>But it's too slow for my needs.</p> <p>An output cache on this would not be suitable because it would be nice for this box to be in real time. Also, 900ms ontop of normal page load is too slow even for one user every now and then so would like to avoid that if possible.</p> <p>Does anyone have any ideas on how I can speed this up? My two ideas at the moment are to have:</p> <ul> <li>A new database table that holds the last players removing the need for the join</li> <li>A field stored somewhere that holds the HTML of that box, every new play that happens it rebuilds that field</li> <li>Combination of the both</li> </ul> <p>Both sort of ugly! Any help appreciated.</p> <p><strong>As requested, linqpad results</strong></p> <p><strong>Lambda</strong></p> <pre><code>TblArcadeGamePlays .Join ( TblProfiles, c =&gt; c.UserID, a =&gt; a.UserID, (c, a) =&gt; new { c = c, a = a } ) .Where (temp0 =&gt; (temp0.c.UserID != 0)) .Select ( temp0 =&gt; new { UserID = temp0.c.UserID, Username = temp0.c.User.Username, EmailAddress = temp0.a.EmailAddress, Date = TblArcadeGamePlays .Where (d =&gt; (d.UserID == temp0.c.UserID)) .OrderByDescending (d =&gt; d.Date) .Select ( d =&gt; new { Date = d.Date } ) .Take (1) .Single ().Date } ) .Distinct () .OrderByDescending (c =&gt; c.Date) .Take (16) </code></pre> <p><strong>SQL</strong></p> <pre><code>-- Region Parameters DECLARE @p0 Int = 0 -- EndRegion SELECT TOP (16) [t6].[UserID], [t6].[Username], [t6].[EmailAddress], [t6].[value] AS [Date2] FROM ( SELECT DISTINCT [t5].[UserID], [t5].[Username], [t5].[EmailAddress], [t5].[value] FROM ( SELECT [t0].[UserID], [t2].[Username], [t1].[EmailAddress], ( SELECT [t4].[Date] FROM ( SELECT TOP (1) [t3].[Date] FROM [tblArcadeGamePlays] AS [t3] WHERE [t3].[UserID] = [t0].[UserID] ORDER BY [t3].[Date] DESC ) AS [t4] ) AS [value] FROM [tblArcadeGamePlays] AS [t0] INNER JOIN [tblProfile] AS [t1] ON [t0].[UserID] = [t1].[UserID] INNER JOIN [tblForumAuthor] AS [t2] ON [t2].[Author_ID] = [t0].[UserID] ) AS [t5] WHERE [t5].[UserID] &lt;&gt; @p0 ) AS [t6] ORDER BY [t6].[value] DESC </code></pre> <p><strong>Query Plan</strong></p> <p><img src="https://i.stack.imgur.com/rayRF.jpg" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTom Gullen
    1. USTom Gullen
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow best to optimise this small bit of c# Linq code
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTry using a group by rather than a distinct.
      singulars
      1. POHow best to optimise this small bit of c# Linq code
      1. USMattW
    2. COI'm assuming this is LINQ to SQL or Entity Framework?
      singulars
      1. POHow best to optimise this small bit of c# Linq code
      1. USChris Shain
    3. CO@Chris yes linq to sql
      singulars
      1. POHow best to optimise this small bit of c# Linq code
      1. USTom Gullen
 

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