programmerist 1983
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.
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;
}
}
}
});
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Also what kind of performance problem do you have and how do you measure it?
When performance matters, why is your method async?
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