SQL how can I measure the CTE execution time

Question

How can I measure the CTE execution time? I want to be able to log the time between CTEs. E.g like this :

--log time here 1 -> insert into log(...) values(...)

WITH cte1
AS (
    SELECT 1 AS id
    )
    --log time here 2-> insert into log(...) values(...)
    ,cte2
AS (
    SELECT 2 AS id
    )

--log time here 3-> insert into log(...) values(...)

SELECT *
FROM cte1

UNION ALL

SELECT *
FROM cte2

https://stackoverflow.com/questions/8247587/sql-server-management-studio-how-to-get-execution-time-down-to-milliseconds — jophab, Dec 11 '17 at 14:04
Possible duplicate of [How to get transaction execute time in SQL Server?](https://stackoverflow.com/questions/11474815/how-to-get-transaction-execute-time-in-sql-server) — justiceorjustus, Dec 11 '17 at 15:03

score 3 · Accepted Answer · answered Dec 11 '17 at 14:10

3

CTEs do not run as separate queries. The entire query is optimized and run as a single entity.

Some databases tend to materialize CTEs (that is, store the results as temporary tables). Other databases tend to incorporate them into execution plans where they are referenced.

However, nothing requires that CTEs run separately. So, if you want an estimate of the length of time, just run the CTE as a separate query.

answered Dec 11 '17 at 14:10

Gordon Linoff

1,242,037
58
646
786

Thank you @Gordon. I will replace the CTEs with the temp tables to make this idea possible. – chosenOne Thabs Dec 11 '17 at 15:01

SQL how can I measure the CTE execution time

1 Answers1