Gaurang Majithiya


I have two options(Queries) for same operation and want to know which is faster and why

1. use of subquery.

2. use of temporary table.

Query no. 1

DECLARE @dteLastExecutionDateTime datetime

SELECT @dteLastExecutionDateTime = max(start_execution_date)
FROM msdb..sysjobactivity
WHERE job_id='914305D8-4DF5-4BA7-936E-46D587E2E997'
GROUP BY job_id

SELECT
j.Name,
j.job_id,
'Enabled' = CASE j.Enabled WHEN 0 THEN 'Disable' WHEN 1 THEN 'Enable' END,
'Last run outcome' = CASE js.last_run_outcome WHEN 0 THEN 'Fail' WHEN 1 THEN 'Succeed' WHEN 3 THEN 'Cancel' ELSE 'Unknown' END,
'Start execution date' = max(ja.start_execution_date),
'Stop execution date' = max(ja.stop_execution_date),
'Last executed step name' = jst.step_name,
'Last outcome message' = js.last_outcome_message,
'Database name' = isnull(jst.database_name, '---')
FROM
msdb..sysjobs j

INNER JOIN msdb..sysjobservers js
ON j.job_id = js.job_id

INNER JOIN msdb..sysjobactivity ja
ON j.job_id = ja.job_id

LEFT JOIN msdb..sysjobsteps jst
ON jst.job_id = ja.job_id
AND jst.step_id = ja.last_executed_step_id
WHERE
j.enabled = 0
OR
(
ja.start_execution_date > isnull(@dteLastExecutionDateTime, '1/1/1900')
AND
(
ja.start_execution_date = (select max(start_execution_date) FROM msdb..sysjobactivity subJA
WHERE subJA.job_id = ja.job_id
GROUP BY job_id)
)
AND
(
js.last_run_outcome = 0
OR
(datediff(minute, ja.start_execution_date, getdate()) > 5 AND ja.stop_execution_date IS NULL)
)
)
GROUP BY
j.name, j.job_id, j.enabled, js.last_run_outcome,
jst.step_name,
js.last_outcome_message,
isnull(jst.database_name, '---')

Query No. 2

DECLARE @dteLastExecutionDateTime datetime

SELECT @dteLastExecutionDateTime = max(start_execution_date)
FROM msdb..sysjobactivity
WHERE job_id='914305D8-4DF5-4BA7-936E-46D587E2E997'
GROUP BY job_id

DECLARE @tblSubJA Table(job_id uniqueidentifier, max_start_execution_date datetime)

INSERT INTO @tblSubJA (job_id, max_start_execution_date)
SELECT job_id, max(start_execution_date) FROM msdb..sysjobactivity
GROUP BY job_id

SELECT
j.Name,
j.job_id,
'Enabled' = CASE j.Enabled WHEN 0 THEN 'Disable' WHEN 1 THEN 'Enable' END,
'Last run outcome' = CASE js.last_run_outcome WHEN 0 THEN 'Fail' WHEN 1 THEN 'Succeed' WHEN 3 THEN 'Cancel' ELSE 'Unknown' END,
'Start execution date' = max(ja.start_execution_date),
'Stop execution date' = max(ja.stop_execution_date),
'Last executed step name' = jst.step_name,
'Last outcome message' = js.last_outcome_message,
'Database name' = isnull(jst.database_name, '---')
FROM
msdb..sysjobs j

INNER JOIN msdb..sysjobservers js
ON j.job_id = js.job_id

INNER JOIN msdb..sysjobactivity ja
ON j.job_id = ja.job_id

INNER JOIN @tblSubJA subja
ON ja.job_id = subja.job_id

LEFT JOIN msdb..sysjobsteps jst
ON jst.job_id = ja.job_id
AND jst.step_id = ja.last_executed_step_id
WHERE
j.enabled = 0
OR
(
ja.start_execution_date > isnull(@dteLastExecutionDateTime, '1/1/1900')
AND
ja.start_execution_date = subja.max_start_execution_date
AND
(
js.last_run_outcome = 0
OR
(datediff(minute, ja.start_execution_date, getdate()) > 5 AND ja.stop_execution_date IS NULL)
)
)
GROUP BY
j.name, j.job_id, j.enabled, js.last_run_outcome,
jst.step_name,
js.last_outcome_message,
isnull(jst.database_name, '---')




Re: I have two options(Queries) for same operation and want to know which is faster and why?

Manivannan.D.Sekaran


The query 2 will be faster than your query 1.

Here you are eleminating one more filter & expensive group function on your sub query(for each row).

Even you can fine tune your second query as follow as,

DECLARE @tblSubJA Table(job_id uniqueidentifier primary key, max_start_execution_date datetime)

INSERT INTO @tblSubJA (job_id, max_start_execution_date)
SELECT job_id, max(start_execution_date) FROM msdb..sysjobactivity
GROUP BY job_id Order By job_id

query 1:

|--Compute Scalar(DEFINESad[Expr1016]=CASE WHEN [msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(0) THEN 'Disable' ELSE CASE WHEN [msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(1) THEN 'Enable' ELSE NULL END END, [Expr1017]=CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(0) THEN 'Fail' ELSE CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(1) THEN 'Succeed' ELSE CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(3) THEN 'Cancel' ELSE 'Unknown' END END END))
|--Stream Aggregate(GROUP BYSad[j].[job_id], [js].[last_run_outcome], [jst].[step_name], [js].[last_outcome_message], [Expr1013]) DEFINESad[Expr1014]=MAX([msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]), [Expr1015]=MAX([msdb].[dbo].[sysjobactivity].[stop_execution_date] as [ja].[stop_execution_date]), [j].[name]=ANY([msdb].[dbo].[sysjobs].[name] as [j].[name]), [j].[enabled]=ANY([msdb].[dbo].[sysjobs].[enabled] as [j].[enabled])))
|--Sort(ORDER BYSad[j].[job_id] ASC, [js].[last_run_outcome] ASC, [jst].[step_name] ASC, [js].[last_outcome_message] ASC, [Expr1013] ASC))
|--Compute Scalar(DEFINESad[Expr1013]=isnull([msdb].[dbo].[sysjobsteps].[database_name] as [jst].[database_name],N'---')))
|--Merge Join(Right Outer Join, MERGESad[jst].[job_id], [jst].[step_id])=([j].[job_id], [ja].[last_executed_step_id]), RESIDUALSad[msdb].[dbo].[sysjobsteps].[job_id] as [jst].[job_id]=[msdb].[dbo].[sysjobs].[job_id] as [j].[job_id] AND [msdb].[dbo].[sysjobsteps].[step_id] as [jst].[step_id]=[msdb].[dbo].[sysjobactivity].[last_executed_step_id] as [ja].[last_executed_step_id]))
|--Clustered Index Scan(OBJECTSad[msdb].[dbo].[sysjobsteps].[clust] AS [jst]), ORDERED FORWARD)
|--Sort(ORDER BYSad[j].[job_id] ASC, [ja].[last_executed_step_id] ASC))
|--Nested Loops(Left Semi Join, OUTER REFERENCESSad[j].[enabled], [js].[last_run_outcome], [ja].[job_id], [ja].[start_execution_date], [ja].[stop_execution_date]))
|--Nested Loops(Inner Join, WHERESad[msdb].[dbo].[sysjobactivity].[job_id] as [ja].[job_id]=[msdb].[dbo].[sysjobservers].[job_id] as [js].[job_id]))
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[j].[job_id]))
| | |--Clustered Index Scan(OBJECTSad[msdb].[dbo].[sysjobs].[clust] AS [j]))
| | |--Clustered Index Seek(OBJECTSad[msdb].[dbo].[sysjobservers].[clust] AS [js]), SEEKSad[js].[job_id]=[msdb].[dbo].[sysjobs].[job_id] as [j].[job_id]) ORDERED FORWARD)
| |--Clustered Index Scan(OBJECTSad[msdb].[dbo].[sysjobactivity].[clust] AS [ja]))
|--Concatenation
|--Filter(WHERESadSTARTUP EXPR([msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(0))))
| |--Constant Scan
|--Filter(WHERESad[msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]>isnull([@dteLastExecutionDateTime],'1900-01-01 00:00:00.000') AND ([msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(0) OR datediff(minute,[msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date],getdate())>(5) AND [msdb].[dbo].[sysjobactivity].[stop_execution_date] as [ja].[stop_execution_date] IS NULL) AND [Expr1021]=[msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]))
|--Assert(WHERESadCASE WHEN [Expr1020]>(1) THEN (0) ELSE NULL END))
|--Stream Aggregate(DEFINESad[Expr1020]=Count(*), [Expr1021]=ANY([Expr1011])))
|--Stream Aggregate(DEFINESad[Expr1011]=MAX([msdb].[dbo].[sysjobactivity].[start_execution_date] as [subJA].[start_execution_date])))
|--Clustered Index Scan(OBJECTSad[msdb].[dbo].[sysjobactivity].[clust] AS [subJA]), WHERESad[msdb].[dbo].[sysjobactivity].[job_id] as [subJA].[job_id]=[msdb].[dbo].[sysjobactivity].[job_id] as [ja].[job_id]))

query 2:

|--Compute Scalar(DEFINESad[Expr1014]=CASE WHEN [msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(0) THEN 'Disable' ELSE CASE WHEN [msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(1) THEN 'Enable' ELSE NULL END END, [Expr1015]=CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(0) THEN 'Fail' ELSE CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(1) THEN 'Succeed' ELSE CASE WHEN [msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(3) THEN 'Cancel' ELSE 'Unknown' END END END))
|--Stream Aggregate(GROUP BYSad[j].[job_id], [js].[last_run_outcome], [jst].[step_name], [js].[last_outcome_message], [Expr1011]) DEFINESad[Expr1012]=MAX([msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]), [Expr1013]=MAX([msdb].[dbo].[sysjobactivity].[stop_execution_date] as [ja].[stop_execution_date]), [j].[name]=ANY([msdb].[dbo].[sysjobs].[name] as [j].[name]), [j].[enabled]=ANY([msdb].[dbo].[sysjobs].[enabled] as [j].[enabled])))
|--Sort(ORDER BYSad[j].[job_id] ASC, [js].[last_run_outcome] ASC, [jst].[step_name] ASC, [js].[last_outcome_message] ASC, [Expr1011] ASC))
|--Compute Scalar(DEFINESad[Expr1011]=isnull([msdb].[dbo].[sysjobsteps].[database_name] as [jst].[database_name],N'---')))
|--Nested Loops(Left Outer Join, OUTER REFERENCESSad[ja].[job_id], [ja].[last_executed_step_id]))
|--Nested Loops(Inner Join, OUTER REFERENCESSad[j].[enabled], [ja].[start_execution_date], [ja].[stop_execution_date], [subja].[job_id], [subja].[max_start_execution_date]))
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[subja].[job_id]))
| | |--Nested Loops(Inner Join, OUTER REFERENCESSad[ja].[job_id]))
| | | |--Clustered Index Scan(OBJECTSad[msdb].[dbo].[sysjobactivity].[clust] AS [ja]))
| | | |--Clustered Index Seek(OBJECTSad@tblSubJA AS [subja]), SEEKSad[subja].[job_id]=[msdb].[dbo].[sysjobactivity].[job_id] as [ja].[job_id]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECTSad[msdb].[dbo].[sysjobs].[clust] AS [j]), SEEKSad[j].[job_id]=@tblSubJA.[job_id] as [subja].[job_id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECTSad[msdb].[dbo].[sysjobservers].[clust] AS [js]), SEEKSad[js].[job_id]=@tblSubJA.[job_id] as [subja].[job_id]), WHERESad[msdb].[dbo].[sysjobs].[enabled] as [j].[enabled]=(0) OR [msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]>isnull([@dteLastExecutionDateTime],'1900-01-01 00:00:00.000') AND [msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date]=@tblSubJA.[max_start_execution_date] as [subja].[max_start_execution_date] AND ([msdb].[dbo].[sysjobservers].[last_run_outcome] as [js].[last_run_outcome]=(0) OR datediff(minute,[msdb].[dbo].[sysjobactivity].[start_execution_date] as [ja].[start_execution_date],getdate())>(5) AND [msdb].[dbo].[sysjobactivity].[stop_execution_date] as [ja].[stop_execution_date] IS NULL)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECTSad[msdb].[dbo].[sysjobsteps].[clust] AS [jst]), SEEKSad[jst].[job_id]=[msdb].[dbo].[sysjobactivity].[job_id] as [ja].[job_id] AND [jst].[step_id]=[msdb].[dbo].[sysjobactivity].[last_executed_step_id] as [ja].[last_executed_step_id]) ORDERED FORWARD)







Re: I have two options(Queries) for same operation and want to know which is faster and why?

Gaurang Majithiya

Thank you very much for your response and suggestion.