Linq to SQL: Query runs fine in SQL Server Management Studio, but times out on the application

Question

I'm maintaining the code of a legacy application. It is using Entity Framework 3.5 and updating it to a newer version at the moment is not possible.

There is a query that lately has started to time out. This is the query in LINQ:

var compoQueryResult = (from composition in MyAppDataContext.Compositions
                            join compositionStatus in MyAppDataContext.CompositionStatus on composition.StatusID equals compositionStatus.CompositionStatusID
                            join compoUnit in MyAppDataContext.CompositionUnits on composition.CompositionID equals compoUnit.CompositionID
                            join unit in MyAppDataContext.Units on compoUnit.UnitID equals unit.UnitID
                            join carUnit in MyAppDataContext.Car_Units on unit.UnitID equals carUnit.UnitId
                            join car in MyAppDataContext.Cars on carUnit.CarId equals car.CarID
                            join location in MyAppDataContext.Locations on unit.ParkedLocationID equals location.LocationID
                            from road in MyAppDataContext.Roads.Where(r => r.RoadID == unit.RoadID).DefaultIfEmpty()
                            from ta in MyAppDataContext.Allocations.Where(ta => ta.CompositionId == composition.CompositionID).DefaultIfEmpty()
                            from arrivalLocation in MyAppDataContext.Locations.Where(al => al.LocationID == ta.EndLocationID).DefaultIfEmpty()
                            where (MyApp.IsAdministrator || MyApp.GetLineIds().Contains(car.LineId.Value))
                            && car.LineId.Value == LineId                           
                            && (statusId == null || statusId == compositionStatus.CompositionStatusID)
                            && (locationId == null || unit.ParkedLocationID == locationId)
                            && (!onlyATMS.HasValue
                                 || (onlyATMS == true && composition.CompositionName.Contains(Common.Constants.ATMSSymbol))
                                 || (onlyATMS == false && !composition.CompositionName.Contains(Common.Constants.ATMSSymbol)))
                            select new CompositionQueryResultItem
                            {
                                CompositionId = composition.CompositionID,
                                CompositionName = composition.CompositionName,
                                CompositionNumber = composition.CompositionNumber,
                                DateTimeModified = composition.DataModified,
                                Status = compositionStatus.CompositionStatusDesc,
                                Location = location.LocationName,
                                Road = road.Name,
                                Number = ta.Number,
                                DepartureTime = ta.DepartTime,
                                ArrivalLocation = arrivalLocation.LocationName,
                                ArrivalTime = ta.ArriveTime
                            }).Distinct().ToList();

After debugging the app I can see that the following query is being executed:

SELECT DISTINCT [t10].[CompositionID] AS [CompositionId], [t10].[CompositionName], [t10].[CompositionNumber], [t10].[value] AS [DateTimeModified], [t10].[CompositionStatusDesc] AS [Status], [t10].[LocationName] AS [Location], [t10].[value2] AS [Road], [t10].[value3] AS [Number], [t10].[value4] AS [DepartureTime], [t10].[value5] AS [ArrivalLocation], [t10].[value6] AS [ArrivalTime]
FROM (
    SELECT [t0].[CompositionID], [t0].[CompositionName], [t0].[CompositionNumber], [t0].[DataModified] AS [value], [t1].[CompositionStatusDesc], [t6].[LocationName], [t7].[Name] AS [value2], [t8].[Number] AS [value3], [t8].[DepartTime] AS [value4], [t9].[LocationName] AS [value5], [t8].[ArriveTime] AS [value6], [t5].[LineId]
    FROM [dbo].[Composition] AS [t0]
    INNER JOIN [dbo].[CompositionStatus] AS [t1] ON [t0].[StatusID] = [t1].[CompositionStatusID]
    INNER JOIN [dbo].[CompositionUnit] AS [t2] ON [t0].[CompositionID] = [t2].[CompositionID]
    INNER JOIN [dbo].[Unit] AS [t3] ON [t2].[UnitID] = [t3].[UnitID]
    INNER JOIN [dbo].[Car_Unit] AS [t4] ON [t3].[UnitID] = [t4].[UnitId]
    INNER JOIN [dbo].[Car] AS [t5] ON [t4].[CarId] = [t5].[CarID]
    INNER JOIN [dbo].[Location] AS [t6] ON [t3].[ParkedLocationID] = ([t6].[LocationID])
    LEFT OUTER JOIN [dbo].[Road] AS [t7] ON ([t7].[RoadID]) = [t3].[RoadID]
    LEFT OUTER JOIN [dbo].[Allocation] AS [t8] ON [t8].[CompositionId] = ([t0].[CompositionID])
    LEFT OUTER JOIN [dbo].[Location] AS [t9] ON [t9].[LocationID] = [t8].[EndLocationID]
    ) AS [t10]
WHERE ([t10].[LineId]) = @p0

If I execute that query on SQL Server Manager Studio I get no issues at all. It executes in less than one second. However, the application is timing out and I have no idea why.

I tried doing

MyAppDataContext.ObjectTrackingEnabled = false;

but I still get the same error.

I'm about to give up and create an stored procedure instead, but I can't grasp why the timeout is happening.

Did you try [this](http://stackoverflow.com/q/8969751/335858)? — Sergey Kalinichenko, Jan 25 '16 at 17:44
That doesn't solve the problem. The query on SQL Management Studio takes less than a second or a second at most. — paddingtonMike, Jan 25 '16 at 17:48
Does the query complete server side if you look at current batches or sql profiler? — Adam Tuliper, Jan 25 '16 at 17:48
Yes I receive the "Query executed successfully" message on SQL Management Studio. — paddingtonMike, Jan 25 '16 at 17:49
`@p0` - check about parameter sniffing and provide your execution plan (`*.sqlplan`) — Devart, Jan 25 '16 at 18:11
Are you sure this query is timing out with exactly the same parameters as you are using the debug/test it. For example, your generated Sql looks a lot simple than the Linq query. For example, it does not include anything relating to `composition.CompositionName.Contains` possibly because onlyATMS is null and it is optimised out. Hence there may be parameters that produce different sql. — sgmoore, Jan 26 '16 at 10:17
The reason why that is not getting included is because onlyATMS is false, so that expression will always be true regardless of what the right part of that expression says. I suppose LINQ is smart enough to drop that out of the query. The query is running fine today. I don't know if there was a problem with the server (the rest of the app was working fine) or if this is an intermittent problem. — paddingtonMike, Jan 26 '16 at 12:24

score -1 · Answer 1 · answered Nov 15 '19 at 15:38

The query execution plan that is being used by the SQL Server can be different when the same query is run through the application. Check your execution plans. You can use hints like option(recompile) at the end of the query or you can optimize it for a particular Lineid if for different id's the rows returned are significantly different. Sometimes, using such hints make it slow in SSMS but not in application. You can find a solution after some hit and trial.

Linq to SQL: Query runs fine in SQL Server Management Studio, but times out on the application

1 Answers1