CODEWITHSUNDEEP
c#sqllinqc#-4.0
Andy
Andy

Reputation: 2318

What is the quickest way to group by and get the MAX of something in c#?

I am inserting new elements into a database where the primarry key is a barcode combined with a version Number. If i just insert them and use a fake version number this takes me 9 minutes for a small file, However if i use the linq statement i provided to calculate the version number this jumps up to 30minutes in a clean database and this is getting slightly worse if the database is prepopulated. Is there a better way to do the version number?

var results = from p in dataContext.GetTable<mailsortEntity>()
                              where p.Barcode == barcode
                              group p by p.Barcode into g
                              select new { currentVersionNo = g.Max(p => p.VersionNo) };

Upvotes: 0

Views: 229

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1502496

Given that you're already restricting the bar code, your grouping is pointless. Basically your query can be simplified to:

var version = dataContext.GetTable<mailSortEntity>()
                         .Where(p => p.Barcode == barcode)
                         .Max(p => p.VersionNo);

EDIT: If you need to allow for situations where the barcode isn't in the database at all, you can use a nullable form:

var version = dataContext.GetTable<mailSortEntity>()
                         .Where(p => p.Barcode == barcode)
                         .Max(p => (int?) p.VersionNo);

(Assuming p.VersionNo is int).

That will make version a Nullable<int> with a null value if the barcode isn't in the database.

Now without the "where" clause, it makes more sense to have the grouping - but you only need the version part, so you can change the grouping:

var results = from p in dataContext.GetTable<mailsortEntity>()
              group p.VersionNo by p.Barcode into g
              select g.Max();

On the other hand that won't tell you which version number is the maximum for which bar code, so you may want:

var results = from p in dataContext.GetTable<mailsortEntity>()
              group p.VersionNo by p.Barcode into g
              select new { Barcode = g.Key, CurrentVersion = g.Max() };

I have no idea whether these will improve your query performance, but they're more logical representations of your query, which may make it easier to track down what's going on in terms of performance using SQL profiling etc.

Upvotes: 5

Related Questions