-2

I have query that uses a few CTEs. The final output query joins the CTEs together. When I ran the query with no ORDER BY, it runs in seconds. When I ran it with an ORDER BY, it takes several minutes to return.

I tried the same query with a large number of records. This time, it ran and ran (still running now), even with no ORDER BY.

I changed the CTEs to temp tables, and the query ran quickly.

I'm not a DBA and am wondering what type of problem this behavior points to. This query I'm using is part of a stored procedure to be used in an application. Are there any considerations I should think about before making this switch? Thanks.

4
  • 1
    can you provide table layouts (keys/indexes only will suffice)? If you have permissions to display the execution plan that would also help in pinpointing the issue. – bbaird Jul 16 '20 at 19:04
  • Don't have permission in this database: SHOWPLAN permission denied in database There are roughly 15 tables in use. I'm hoping a description of the behaviour will point to something that is causing the differences. Also hoping there is no concern with using temp tables in this SP rather than CTEs. – jabs Jul 16 '20 at 19:17
  • 3
    Please consider reading about Asking query performance questions – mustaccio Jul 16 '20 at 20:04
  • 1
    Thanks for the link - downvotes without explanation isn't helpful, but your link was!. Thank you. I understand this is an overly-broad question, but hoping someone would be able to help despite my inability to get to more detailed info. – jabs Jul 16 '20 at 20:17
1

When you join your CTE with another table, SQL may (depending on the execution plan) execute the code within your CTE multiple time.

Ex : Running this against the stackoverflow database:

set statistics io on;

with cte as (
select ID from Users where Location ='Canada'
)
select top 100 title from Posts p 
join cte on cte.Id=p.ParentId


select ID into #user from Users where Location ='Canada'
select top 100 title from Posts p 
join #user u on u.Id=p.ParentId
drop table #user

The CTE query made a lot of read on the user table:

Table 'Users'. Scan count 0, logical reads 123369, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 9588, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The other query with a temp table only read the data once from users so it made way less read (causing the query to be way faster)

Table 'Users'. Scan count 1, logical reads 44573, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(3687 rows affected)

(1 row affected)

(100 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 7183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#user_______________________________________________________________________________________________________________000000000045'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

1
  • Thank you. I went with the temp table option. – jabs Jul 22 '20 at 0:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.