CODEWITHSUNDEEP
c#linqoptimization
user1477388
user1477388

Reputation: 21430

Speeding up a linq query with 40,000 rows

In my service, first I generate 40,000 possible combinations of home and host countries, like so (clientLocations contains 200 records, so 200 x 200 is 40,000):

foreach (var homeLocation in clientLocations)
{
    foreach (var hostLocation in clientLocations)
    {
        allLocationCombinations.Add(new AirShipmentRate
        {
            HomeCountryId = homeLocation.CountryId,
            HomeCountry = homeLocation.CountryName,
            HostCountryId = hostLocation.CountryId,
            HostCountry = hostLocation.CountryName,
            HomeLocationId = homeLocation.LocationId,
            HomeLocation = homeLocation.LocationName,
            HostLocationId = hostLocation.LocationId,
            HostLocation = hostLocation.LocationName,
        });
    }
}

Then, I run the following query to find existing rates for the locations above, but also include empty the missing rates; resulting in a complete recordset of 40,000 rows.

var allLocationRates = (from l in allLocationCombinations
                        join r in Db.PaymentRates_AirShipment
                            on new { home = l.HomeLocationId, host = l.HostLocationId }
                            equals new { home = r.HomeLocationId, host = (Guid?)r.HostLocationId }
                        into matches
                        from rate in matches.DefaultIfEmpty(new PaymentRates_AirShipment
                        {
                            Id = Guid.NewGuid()
                        })
                        select new AirShipmentRate
                        {
                            Id = rate.Id,
                            HomeCountry = l.HomeCountry,
                            HomeCountryId = l.HomeCountryId,
                            HomeLocation = l.HomeLocation,
                            HomeLocationId = l.HomeLocationId,
                            HostCountry = l.HostCountry,
                            HostCountryId = l.HostCountryId,
                            HostLocation = l.HostLocation,
                            HostLocationId = l.HostLocationId,
                            AssigneeAirShipmentPlusInsurance = rate.AssigneeAirShipmentPlusInsurance,
                            DependentAirShipmentPlusInsurance = rate.DependentAirShipmentPlusInsurance,
                            SmallContainerPlusInsurance = rate.SmallContainerPlusInsurance,
                            LargeContainerPlusInsurance = rate.LargeContainerPlusInsurance,
                            CurrencyId = rate.RateCurrencyId
                        });

I have tried using .AsEnumerable() and .AsNoTracking() and that has sped things up quite a bit. The following code shaves several seconds off of my query:

var allLocationRates = (from l in allLocationCombinations.AsEnumerable()
                        join r in Db.PaymentRates_AirShipment.AsNoTracking()

But, I am wondering: How can I speed this up even more?

Edit: Can't replicate foreach functionality in linq. 

allLocationCombinations = (from homeLocation in clientLocations
                            from hostLocation in clientLocations
                            select new AirShipmentRate
                            {
                                HomeCountryId = homeLocation.CountryId,
                                HomeCountry = homeLocation.CountryName,
                                HostCountryId = hostLocation.CountryId,
                                HostCountry = hostLocation.CountryName,
                                HomeLocationId = homeLocation.LocationId,
                                HomeLocation = homeLocation.LocationName,
                                HostLocationId = hostLocation.LocationId,
                                HostLocation = hostLocation.LocationName
                            });

I get an error on from hostLocation in clientLocations which says "cannot convert type IEnumerable to Generic.List."

Upvotes: 2

Views: 629

Answers (2)

Fabian Bigler
Fabian Bigler

Reputation: 10895

How can I speed this up even more?

Optimizing is a bitch.

Your code looks fine to me. Make sure to set the index on your DB schema where it's appropriate. And as already mentioned: Run your Linq against SQL to get a better idea of the performance.

Well, but how to improve performance anyway?

You may want to have a glance at the following link: 10 tips to improve LINQ to SQL Performance

To me, probably the most important points listed (in the link above):

  • Retrieve Only the Number of Records You Need
  • Turn off ObjectTrackingEnabled Property of Data Context If Not Necessary
  • Filter Data Down to What You Need Using DataLoadOptions.AssociateWith
  • Use compiled queries when it's needed (please be careful with that one...)

Upvotes: 2

blorkfish
blorkfish

Reputation: 22824

The fastest way to query a database is to use the power of the database engine itself.

While Linq is a fantastic technology to use, it still generates a select statement out of the Linq query, and runs this query against the database.

Your best bet is to create a database View, or a stored procedure.

Views and stored procedures can easily be integrated into Linq.

Material Views ( in MS SQL ) can further speed up execution, and missing indexes are by far the most effective tool in speeding up database queries.

Upvotes: 3

Related Questions