Unanswered: Stored Proc returns in 0 sec, but app gives time out error
I am having this issue where the stored procedure looks good, it returns data in 0 seconds. When I call the proc from Visual Studio, it takes a long time or times out completely. Here's what I've noticed so far:
If I recompile the stored procedure, VS will return the data as expected. Update statistics did not help.
I have narrowed the issue down to a few tables because the same problem exists with another stored procedure that joins these same tables.
Tables are joined on primary key = foreign key. (nonclustered index on pk)
Joins result in table scan even though the tables are joined on keys.
Here's the query, any help would be greatly appreciated:
SELECT DISTINCT TCBF.Flightnumber
FROM tblCBillFlight TCBF
INNER JOIN tblCBillFlightInfo TCBFI
ON TCBF.CBillFlight_PK = TCBFI.FK_Cbillflight
JOIN tblCBillClass TCBC
ON TCBFI.CBillFlightInfo_PK= TCBC.FK_CBillFlightInfo
WHERE TCBF.FK_KitchenID = @FK_KitchenID
AND 'y' =
WHEN TCBF.FK_CustomerID = @FK_CustomerID THEN 'y'
WHEN @FK_CustomerID = -1 THEN 'y'
AND TCBFI.FlightDate BETWEEN @FromDate and @ToDate
AND ISCALCULATED = 'TRUE'
ORDER BY TCBF.Flightnumber
Do you mean like another query running at the same time that would cause blocking?
I have not tried looking for blocking as the app is in development right now, so there would be no other queries running by another user.
Even when I take the case statement out and just run a select from table where someColWithIndex = @Something I get a table scan.
If I take the variable away and hard code it, it hits the index. But that is too narrow. I need stuff like date between @Start and @End and a few other filters in my query.