CODEWITHSUNDEEP
sqlperformancelinqoptimization
Francesco
Francesco

Reputation: 10870

Select data excluding a subset of records with LINQ with minimal overhead

I initialiye a GridView with the top 250 most recent records (from Table Document). I developed a button add all, in order to be able to load all entries (on avarage all returned records might vary from 400 to 3000).

Instead of loading all the records set completely I would prefer an incremental approach, adding only the remaining records and not the ones already loaded.

However if I use the following query:

SELECT d.ItemID FROM Documents d
WHERE NOT EXISTS
( SELECT ItemID FROM Documents WHERE CategoryID = d.CategoryID )

I would need to pass the 250 IDs from the Client to the Server execute the query and the subquery and then return the result.

How could I optimize this procedure? Or is it in this case fine returning derctly all the record set?

Upvotes: 1

Views: 860

Answers (1)

Phil Sandler
Phil Sandler

Reputation: 28046

Will the top 250 records be the same between the first and second query? In other words, is the data likely to change between the first call and second?

If not, you could simply use the first query to exclude rows from the second (assuming T-SQL):

SELECT 
    d.ItemID 
FROM 
    Documents d 
WHERE 
    d.ItemID NOT IN 
      (
         --this would be whatever your first query was
         SELECT TOP 250 ItemID 
         FROM Documents 
         WHERE CategoryID = d.CategoryID 
         ORDER BY d.ItemID DESC
      )

Upvotes: 1

Related Questions