Optimize LINQ Query that check result if had value

Question

I have LINQ query check the query result and end the statement. The database have around 600k data. Below are the LINQ query and if statement.

 var feetypelist = from feetype in dbDataContext.tbl_fee_types
                          orderby feetype.seq
                          select feetype.id;

        foreach (var fty in feetypelist)
        {
            var checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                  where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
                                  orderby fee.seq
                                  select fee;

            if (checkFeeCharges.Count() == 0)
            {
                checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                  where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
                                  && fee.country == country
                                  orderby fee.seq
                                  select fee;
            }

            if (checkFeeCharges.Count() == 0)
            {
                checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                  where fee.site_id == siteId && fee.country == country && 
                                  ((fee.arr == arr && fee.dep == dep) || (fee.arr.Equals("DOM") && fee.dep.Equals("DOM")) ||
                                  (fee.arr.Equals("*") && fee.dep == dep) || (fee.arr == arr &&  fee.dep.Equals("*")))
                                  && fee.feetypeid == fty
                                  orderby fee.seq
                                  select fee;

                if (checkFeeCharges.Count() == 0)
                {
                    var country_route = from route in dbDataContext.tbl_country_routes
                                        where route.origin_airport_cd == arr && route.destination_airport_cd == dep
                                        select route;

                    if (country_route.Count() >= 1)
                    {
                        foreach (var c in country_route)
                        {
                            if (c.air_asiax == 'Y')
                            {
                                //con_flight = "Y";
                                checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                                  where fee.site_id == siteId && fee.country == country &&
                                                  ((fee.arr == "AAX" && fee.dep == "AAX") || (fee.arr == "*" && fee.dep == dep) ||
                                                  (fee.arr == arr && fee.dep == "*")) && fee.feetypeid == fty
                                                  orderby fee.seq
                                                  select fee;

                                if (checkFeeCharges.Count() == 0)
                                {
                                    checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                                      where fee.site_id == siteId && fee.country == country &&
                                                      ((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
                                                      (fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*") ||
                                                      (fee.arr == country && fee.dep == country)) &&
                                                      fee.feetypeid == fty
                                                      orderby fee.seq
                                                      select fee;
                                }
                            }
                            else
                            {
                                checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                                  where fee.site_id == siteId && fee.country == country &&
                                                  ((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
                                                  (fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*") ||
                                                  (fee.arr == country && fee.dep == country)) &&
                                                  fee.feetypeid == fty
                                                  orderby fee.seq
                                                  select fee;
                            }

                        }
                    }
                    else
                    {
                        checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                          where fee.site_id == siteId && fee.country == country &&
                                          ((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
                                          (fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*")) &&
                                          fee.feetypeid == fty
                                          orderby fee.seq
                                          select fee;
                    }
                }
            }

            if (checkFeeCharges.Count() == 0)
            {
                checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                                  where fee.site_id == siteId && fee.country == country &&
                                  ((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
                                  (fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*")) &&
                                  fee.feetypeid == fty
                                  orderby fee.seq
                                  select fee;
            }

The query execute take a while to done, have any idea how to optimize this query.

return json request

if (checkFeeCharges.Any())
            {
                List<FeeAndChargesModel_Fee_Schedule> feeTable = new List<FeeAndChargesModel_Fee_Schedule>();
                foreach (var p in checkFeeCharges)
                {
                    feeTable.Add(
                        new FeeAndChargesModel_Fee_Schedule()
                        {
                            arr = FlightScheduleAPIController.GetCountryName(arr, siteId),
                            dep = FlightScheduleAPIController.GetCountryName(dep, siteId), 
                            country = p.country,
                            feedesc = p.feedesc,
                            feetype = p.feetype,
                            currency = p.currency,
                            value = p.value,
                            remark = p.remark
                        }
                    );
                    label = p.feetype;


                }

                mainJson.Add(
                        new FeeAndChargesModel_Main
                        {
                            label = label,
                            con_flight = FlightScheduleAPIController.GetCountryName(GetConnectingFlight(dep, arr), siteId),
                            details = feeTable
                        }
                        );
            }
        }

        return mainJson;

Care to post the full code block where this comes from? It would appear you have code that does literally nothing and could be replaced with a nop. What happens to `checkFeeCharges` after all these queries? Do you return it eventually? Enumerate over it? Depending on what you're doing, you could potentially do early returns. — Mike Bailey, Apr 17 '13 at 23:42

score 1 · Answer 1 · answered Apr 17 '13 at 05:25

Ok so this code is really very hard to read, but lets give you some advice.

First Thing I notice is that the first sql statement is executed

var checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                              where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
                              orderby fee.seq
                              select fee;

and if it returns nothing

        if (checkFeeCharges.Count() == 0)
        {

It executes a sql query which is more specific than the first. It has all the same AND statements + another one. If the first query returns nothing, then the expectation (all things constant) should be for the second to return nothing. If so then why do it?

            checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
                              where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
                              && fee.country == country
                              orderby fee.seq
                              select fee;
        }

Personally I would attempt to understand or solve the following

1 What is the expected result set size, and could I retrieve a 'small' number of records and process these in-memory. I would need to understand if the result set would grow over time and its min,max type boundarys.

2 Is there a better database structure which I could refactor the code to use which would allow this query to be easier and more efficient.

3 To actually be able to optimize this query, I would attempt to write this first directly in sql and use explain to help understand exactly what the sql database is doing, what indexes are being used and what the size of the results sets are being returned with each query.

. I also believe that you should be able to combine a lot of the queries you have into a single expression.

This probably isn't exactly right, but should be ok as a demonstration

from fee in dbDataContext.tbl_fee_schedules
where (fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
    && fee.country == country) || (fee.site_id == siteId && fee.country == country &&
                                              ((fee.arr == "AAX" && fee.dep == "AAX") || (fee.arr == "*" && fee.dep == dep) ||
                                              (fee.arr == arr && fee.dep == "*")) && fee.feetypeid == fty)
|| ( ..... ) | ( .... )

From what I can see in there you will need a couple of joins or sub-selects which is why attempting it within sql would be quickest. Also there are a number of linq2sql/ORM profiling tools which potentially would be able to help with diagnostics.

thanks for the advice @Alistair.. i already implement some of your advice.. the issue is 'foreach (var fty in feetypelist)' will loop around 37 times.. because we have 37 fee type.. do we have any optimization code on LINQ for this? — Nazrul Mizuar, Apr 17 '13 at 06:47

score 0 · Answer 2 · answered Apr 17 '13 at 04:14

0

It's hard to tell how to optimize this because you have a lot of duplication here. I'd start with trying to reduce that.

One obvious thing to change is this:

if (checkFeeCharges.Count() == 0)

This enumerates the whole query each time you call it. A much cheaper way to check the same thing:

if (!checkFeeCharges.Any())

This will only enumerate one result off the query to determine whether it is empty.

answered Apr 17 '13 at 04:14

tallseth

3,635
1
23
24

Its same thing. We can't consider this when talking about optimizing the performance. – Roshana Apr 17 '13 at 04:18
1

@Roshana : Actually, there are difference http://stackoverflow.com/a/305156/529282 – Martheen Apr 17 '13 at 04:40
1

@Martheen I would expect if he is using a database (which he mentions) then http://stackoverflow.com/questions/2856965/query-result-what-should-i-use-count-or-any would be more correct. It really depends on what sql is happening under the hood. – Alistair Apr 17 '13 at 04:59

Optimize LINQ Query that check result if had value

2 Answers2