I have a simple SQL query that when run from C# takes over 30 seconds then times-out every time, whereas when run on SQL Server Management Studio successfully completes instantly. In the latter case, a query execution plan reveals nothing troubling, and the execution time is spread nicely through a few simple operations.
I've run 'EXEC sp_who2' while the query is running from C#, and it is listed as taking 29,000 milliseconds of CPU time, and is not blocked by anything.
I have no idea how to begin solving this. Does anyone have some insight?
The query is:
SELECT
c.lngId,
...
FROM tblCase c
INNER JOIN tblCaseStatus s ON s.lngId = c.lngId
INNER JOIN tblCaseStatusType t ON t.lngId = s.lngId
INNER JOIN [Another Database]..tblCompany cm ON cm.lngId = cs.lngCompanyId
WHERE t.lngId = 25
AND c.IsDeleted = 0
AND s.lngStatus = 1