Sql-server – Performance about this simplified where-condition query

sql-server-2008

I have a query in SQL Server 2008 like this:

SELECT 
        a.type, 
        a.name, 
        a.startDate, 
        a.endDate,
        b.key          AS key,
        b.is_locked    AS is_locked,
        NULL           AS idx,
        NULL           AS page_count
FROM
    jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

WHERE 
    a.status IN (0,1,7) 
    AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) 
    AND a.type in (15,17,19)

UNION ALL

SELECT 
        a.type, 
        a.name, 
        a.startDate, 
        a.endDate,
        NULL           AS key,
        NULL           AS is_locked,
        c.idx          AS idx,
        c.page_count   AS page_count
FROM
    jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

WHERE 
    a.status = 5 
    AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) 
    AND a.type in (15,17,19)

I simplified the query into like this:

SELECT * FROM
(
    SELECT 
            a.type, 
            a.name, 
            a.startDate, 
            a.endDate,
            b.key          AS key,
            b.is_locked    AS is_locked,
            NULL           AS idx,
            NULL           AS page_count
    FROM
        jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

    WHERE 
        a.status IN (0,1,7) 
        AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

    UNION ALL

    SELECT 
            a.status, 
            a.name, 
            a.startDate, 
            a.endDate,
            NULL           AS key,
            NULL           AS is_locked,
            c.idx          AS idx,
            c.page_count   AS page_count
    FROM
        jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

    WHERE 
        a.status = 5 
        AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
) t
WHERE t.type IN (15,17,19)

I would like to know if the simplified query has better performance or not than the original one?

Note: if there is syntax error, please let me know in the comment section because I typed this query by my hand.

A bit of additional OOT question:
What is the english term for the process of simplifying an expresion or logic like above which by reducing the the two same where condition (t.type IN (15,17,19)) into one ?

Another example is like this:

R = (x+2)/2 + (x+3)/2

you can simplify it by removing the duplicate "/2" become one like this:

R = ( (x+2) + (x+3) ) / 2

You can answer the OOT question in the comment section.

Thanks in advance.

Best Answer

You dont need subquery, make it one-time I/O, you will get better performance: 


select * from jobs 
where (
       (status IN (0,1,7) and startDate > getdate()) 
       or (statu=5 and startDate=getdate())
       )
and type in (15,17,19)

Related Question