1

I have a SQL query like the one below. The database tables are not indexed and unfortunately I cannot do anything about it. In the application using this query I have experienced sporadic SQL timeouts.

When such a timeout was happening I used LinqPad to execute the exact same query with the same parameters and it also timed out. Although tableA and tableB were accessible and when I removed one of the output columns the query worked fine.

What can cause this behavior and what can I do about it? Thanks. 

SELECT 
    a.ID, 
    a.NAME,
    b.VALUE 
FROM 
    tableA as a
INNER JOIN 
    tableB bb 
ON 
    bb.OWNER = a.ID
INNER JOIN 
    tableB as b 
ON 
    b.OWNER = bb.ID 
WHERE
    a.OWNER = '1234567890' 
    AND b.NAME = 'XYZ' 
ORDER BY
    b.VALUE
Matt
  • 14,906
  • 27
  • 99
  • 149
SwissEngineer
  • 57
  • 2
  • 6
  • "and when I removed one of the output columns" -- doing that changes the [execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). In particular, removing `b.VALUE` can greatly simplify things since SQL Server doesn't need to bother with most of the logic accessing `b`, as long as it can prove a row from `a` would match, while removing `a.NAME` may make an existing index covering, and so on. – Jeroen Mostert Jul 27 '16 at 14:23
  • Your general question is "how do I troubleshoot why this query invocation is slow", which is very broad indeed. I don't think there's a SO question that gives you a good handle on that (if there is, I haven't found it), although there are plenty of resources, of course. – Jeroen Mostert Jul 27 '16 at 14:24

0 Answers0