Comparision of two queries. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
Do have proper indexes to address the performance issue, if you have any.
If not I suggest you to look at Estimated execution plan from Quer Analyzer to get to know before we simply give you to the solution or differences between the approach you are following. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
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.
]]>