SQL Server Performance

Comparision of two queries.

Discussion in 'Getting Started' started by gaurang_gr, Apr 30, 2007.

  1. gaurang_gr New Member

    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
  2. satya Moderator

    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.
  3. MohammedU New Member

    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.

Share This Page