Link to home
Start Free TrialLog in
Avatar of programmerist 1983
programmerist 1983Flag for Türkiye

asked on

How can I convert my linq to SQL old school Query for better performance?

Hi; I want to improve my query performance , When you take a look for my previous question, you will see ny question . I asked to  you for modifying of my linq query.Because I need really better performance .

But I decided to look at the equality of SQL for my linq query. As a result, How can I convert below linq to SQL  with taking care of performance issue? Because I need to have a better performance that's the reason;  it is more essential than before.A guy who is willing to answer this question   has to focus on

1) better performance
2) sql equality.

   string sortColumnName = GetSortColumnName(request);

            if (!string.IsNullOrEmpty(sortColumnName))
            {
                queryOrdered = queryOrdered.OrderBy(sortColumnName);
            }

            var data = await queryOrdered.Skip(request.Start).Take(request.Length).ToListAsync().ConfigureAwait(false);

            data.ForEach(x =>
            {
                foreach (var item in x.SalesItems)
                {
                    if (flightSalesItemType.Any(y => y == item.Value))
                    {
                        var flightSales = DbContext.Set<FlightTicketNumber>().Include(q => q.FlightPassenger).Include(q => q.FlightTicketFare).FirstOrDefault(q => q.SalesItemId == item.Key);
                        if (flightSales != null)
                        {
                            var flightLeg = DbContext.Set<FlightLeg>().Include(q => q.Airline).FirstOrDefault(q => q.PNRId == flightSales.PNRID);

                            x.TicketNo = flightSales.TicketNumber;
                            x.ServiceFee = flightSales.FlightTicketFare.Sum(t => t.ServiceFee);
                            x.PassengerName = flightSales.FlightPassenger.Name + " " + flightSales.FlightPassenger.Surname;
                            x.AirlineProviderName = flightLeg.Airline.Code;
                            x.Route = flightLeg.OriginCityCode + " - " + flightLeg.DestinationCityCode;
                        }
                    }
                }
            });

Open in new window

Avatar of Eduard Ghergu
Eduard Ghergu
Flag of Romania image

Hi,

SQL Server Profiler is a tool available on desktop/on-prem version of SQL Server Management Studio. By using this tool, you'll be able to see the query that it's executed based on the EF code transformation. Also, from SQL Server Management Studio, on the query execution, you're able to see the execution plan and see where are the most expensive parts.

https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, normally you would try to place the expensive calls not in the loop. So you should look into your data model, whether it is possible to move the DbContext calls out of the loops body.

Also what kind of performance problem do you have and how do you measure it?
When performance matters, why is your method async?