Exceptionally slow database response time on Linq query

Question

I have written what I thought to be a pretty solid Linq statement but this is getting 2 to 5 second wait times on execution. Does anybody have thoughts about how to speed this up?

            t.states = (from s in tmdb.tmZipCodes
                        where zips.Contains(s.ZipCode) && s.tmLicensing.Required.Equals(true)
                        group s by new Licensing { 
                            stateCode = s.tmLicensing.StateCode,
                            stateName = s.tmLicensing.StateName,
                            FIPSCode = s.tmLicensing.FIPSCode,
                            required = (bool)s.tmLicensing.Required,
                            requirements = s.tmLicensing.Requirements,
                            canWorkWhen = s.tmLicensing.CanWorkWhen,
                            appProccesingTime = (int) s.tmLicensing.AppProcessingTime 
                        }
                            into state
                            select state.Key).ToList();

I've changed it to a two stage query which runs almost instantaneously by doing a distinct query to make my grouping work, but it seems to me that it is a little counter intuitive to have that run so much faster than a single query.

Answer 1

Im not sure why it's taking so long, but it might help to have a look at LINQPad, it will show you the actual query being generated and help optimize.

also, it might not be the actual query that's taking a long time, it might be the query generation. I've found that the longest part is when the linq is being converted to the sql statement.

you could possibly use a compiled query to speed up the sql generation process. a little information can be found on 3devs. I'm not trying to promote my blog entry but i think it fits.

Answer 2

I would hope it's irrelevant, but

s.tmLicensing.Required.Equals(true)

looks an awful lot (to me) like:

s.tmLicensing

assuming it's a Boolean property.

Given that you know it's true, I don't see much point in having it in the grouping either.

Having said those things, John Boker is absolutely right on both counts: find out whether it's the SQL or LINQ, and then attack the relevant bit.

Answer 3

You don't seem to be using the group, just selecting the key at the end. So, does this do the same thing that you want?

t.states = (from s in tmdb.tmZipCodes
            where zips.Contains(s.ZipCode) && s.tmLicensing.Required.Equals(true)
            select new Licensing { 
                stateCode = s.tmLicensing.StateCode,
                stateName = s.tmLicensing.StateName,
                FIPSCode = s.tmLicensing.FIPSCode,
                required = (bool)s.tmLicensing.Required,
                requirements = s.tmLicensing.Requirements,
                canWorkWhen = s.tmLicensing.CanWorkWhen,
                appProccesingTime = (int) s.tmLicensing.AppProcessingTime 
            }).Distinct().ToList();

Also bear in mind that LINQ does not execute a query until it has to. So if you build your query in two statements, it will not execute that query against the data context (in this case SQL Server) until the call to ToList. When the query does run it will merge the multiple querys into one query and execute that.