Comparision of two queries.

Can you please help me?

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, '---')



Thanks,

Gaurang Majithiya

You should not add indexes to the system tables and I don't think you able add one if you want to...

Your two queries performance mostly based on your sysjobs and tables row count...set the following tow options before executing your query in Query window and test you see your self the difference...and I will suggest you to change the ISOLATION level to READ UNCOMMITTED for this type of queries..


Set statistics IO
Set statistics time

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.