CODEWITHSUNDEEP
c#.netlinq
SpoiledTechie.com
SpoiledTechie.com

Reputation: 10735

Help me make this LINQ query faster please?

To Set up the scenario, Lets say I have 100000 rows in the table and it grows more and more each day. This queue currently takes over 2 seconds to retrieve only about 40 or 50 rows from the table.

The data in this table are grouped by DateTime references so I start off grouping all the data by DateTime because this is the only equal value to other rows in the table. Each group of rows could be anywhere from 1 row to 5 rows MAX. I then select the grouped rows, pick apart the data and display it out to the user. The problem I can see is that I cause an EXIST and a Group by in the SQL. I must select all 40 rows at once to make the queue faster, but I pick each group out in a FOR loop. So how if any way can I make this queue faster? Its the Laggiest of them all and my users are complaining about the 2 second wait time. Please help.

(from yy in Data_Captured_Type_Militaries
    where yy.DateTime_Added >= DateTime.UtcNow.AddHours(-72)
    (from xx in Data_Captured_Type_Militaries
    where xx.DateTime_Added >= DateTime.UtcNow.AddHours(-72)
    group xx by xx.DateTime_Added into gg
    select gg.Key).Contains(yy.DateTime_Added)
    select new
    {
        yy.Elites,
        yy.DateTime_Added,
        yy.Uid,
        yy.Military_Location,
        yy.Province_ID,
        yy.Time_To_Return
    }).ToList()

What it translates to:

SELECT [t0].[Elites], [t0].[DateTime_Added], [t0].[uid] AS [Uid],[t0].[Military_Location], [t0].[Province_ID], [t0].[Time_To_Return]
FROM [Data_Captured_Type_Military] AS [t0]
WHERE (EXISTS (
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT [t1].[DateTime_Added]
        FROM [Data_Captured_Type_Military] AS [t1]
        WHERE [t1].[DateTime_Added] >= @p0
        GROUP BY [t1].[DateTime_Added]
        ) AS [t2]
    WHERE [t2].[DateTime_Added] = [t0].[DateTime_Added]
    )) AND ([t0].[DateTime_Added] >= @p1)

Upvotes: 1

Views: 538

Answers (2)

Ryan Versaw
Ryan Versaw

Reputation: 6495

You sort of explain this, but I still don't understand what this portion of the query does for you:

where (from xx in Data_Captured_Type_Militaries
       where xx.DateTime_Added >= DateTime.UtcNow.AddHours(-72)
       group xx by xx.DateTime_Added into gg
       select gg.Key).Contains(yy.DateTime_Added)

This nested query grabs each unique DateTime_Added within the last three days. You then use this to narrow down your outer query by making sure each of those DateTime_Addeds exist in the inner query.

The outer query is already narrowed down to the most recent three days of DateTime_Addeds, so it looks like the inner query won't actually do anything to it. I could be wrong, but is the inner query even needed at all?

If it is, please expand or let me know what I'm not understanding.

Upvotes: 2

Ben M
Ben M

Reputation: 22512

The query looks pretty simple. I'd start by looking at your table indexes in SQL Server. Are you indexing DateTime_Added?

If you don't currently have an index on that column, try this (in SQL Server Management Studio):

CREATE INDEX IX_Data_Captured_Type_Military_DateTime_Added 
ON Data_Captured_Type_Military (DateTime_Added)
GO

Upvotes: 2

Related Questions