Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
English term: I'd vote for "reduced", as in "I reduced this equation from this form to this form" –  Geoff Mar 20 '13 at 16:38
1  
You can do this with OR -- You don't need a subquery. –  Brendan Long Mar 20 '13 at 16:40
    
@Brendan Long: thanks, but I don't think so I can use OR because the real query is not just select *. Please wait, I will modify my question. –  suud Mar 20 '13 at 16:42
    
@BrendanLong: I had modified my query –  suud Mar 20 '13 at 17:09
    
This would seem more on topic on Database Administrators. Voting to migrate. –  Andriy M Mar 20 '13 at 19:37
add comment

migrated from stackoverflow.com Mar 21 '13 at 8:25

This question came from our site for professional and enthusiast programmers.

6 Answers

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)

share|improve this answer
    
hi, I had modified my query. Actually, the query is not just select * from a single table, I tried to make the query more simple to read that's why I removed the complex part. –  suud Mar 20 '13 at 17:13
add comment

There is no hard and fast rule in performance tuning queries. If you compare the simple queries A1 and A2 with A2 being a "reduced" version of A1, you might find that A2 is faster than A1. That however does not mean that two complex queries B1 and B2 with B2 being a reduced version of B1 using the same reduction process will show the same performance relationship.

Your query above uses the table aliases a and c. that suggests that there is a table b involved too. Once you add additional joins the optimizer will take a completely different path and you cant compare the optimization outcome from the one to the other anymore.

In a perfect world any "reduction" of a query won't change the outcome of the optimization as the goal is to find the perfect query plan to answer the question. However, as the search for the plan takes time and that time can easily outgrow the time required to execute the query in the first place, SQL Server settles for a "good enough" plan. To get there it uses a set of heuristics.

Usually the SQL Server optimizer gets very close to the best plan. However, with very complex queries it sometimes gets "stuck" in a dead-end search path. By rewriting the query we might be able to change the starting point for the optimizer so that it does not end up in that same rabbit hole. However, in general this is not required (at least in the last three SQL Server versions).

Now, if your query is a performance problem, the first thing to look at is appropriate indexing. If you give us the complete query we might be able to help with that.

If indexing does not help enough, we can start looking at rewrite possibilities, but again we would need the complete query for that and probably also a significant amount of sample data.

If you looked at indexes already and still need help, post your query and an actual (post execution) XML query plan.

share|improve this answer
    
hi, the real query is long enough, it's around 500 lines and has more than 15 tables. So I'm kinda lazy to rename all the names (for privacy reason), but maybe I can show the query to you in private if there is a way to send you private message here. –  suud Mar 27 '13 at 11:22
    
I am not aware of a contact option on SO. However, if you head over to my site at sqlity.net you should be able to find a way to contact me. –  Sebastian Meine Mar 27 '13 at 21:53
    
hi, I had sent email to you. –  suud Mar 28 '13 at 4:41
add comment

It does not seem to me that you really simplified the query. And performance could be even worse if union subqueries give a lot of results. It's better like this.

SELECT 
    *
FROM jobs
where 1=1
    and type in (15,17,19)
    and (1=2        
        or (1=1
            and status IN (0,1,7) 
            AND startDate > getdate() 

        )
        or (1=1
            and status = 5 
            AND startDate = getdate() 
        )
    )

Besides startDate = getdate() part seems tricky to me - it's not very likely to 'hit' startDate since getdate() gives you current time with precsion yot to 3/thousands of a second.

share|improve this answer
1  
What's with the 1=1, 1=2 clauses? –  RobJohnson Mar 20 '13 at 16:48
    
Improves readability. –  frikozoid Mar 20 '13 at 16:49
    
please ignore the date part because the "real query" has dates compared without time part. The query I provided in the question has already been simplified for sake of brevity (the real query will take a lot of lines). My question is about whether taking out of type IN (15,17,19) where-condition could be better or not. –  suud Mar 20 '13 at 17:03
    
hi, I had modified my query. Actually, the query is not just "select * from a single table", I tried to make the query more simple to read that's why I removed the complex part –  suud Mar 20 '13 at 17:16
add comment

Avoid the union and go with:

SELECT * 
FROM jobs
WHERE type in (15,17,19)
  AND ((status IN(0,1,7) AND startDate > getdate()) 
   OR (status = 5 AND startDate = getdate())) 
share|improve this answer
    
hi, I had modified my query. Actually, the query is not just select * from a single table, I tried to make the query more simple to read that's why I removed the complex part. –  suud Mar 20 '13 at 17:13
add comment

You did your subquery a bit reversed, you really want to filter out the results that are going to be common across your results:

;WITH filtered_jobs AS
(
    SELECT 
        j.type, 
        j.name, 
        j.startDate, 
        j.endDate,
        j.status
     FROM jobs j
     WHERE j.type in (15,17,19)
     AND j.startDate > getdate() 
)
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 filtered_jobs a
     LEFT JOIN gates b ON a.pk_job = b.fk_job
     WHERE a.status IN (0,1,7)
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 filtered_jobs a   
     LEFT JOIN pages c ON a.pk_job = c.fk_job  
     WHERE a.status = 5

However, you're really not going to get better performance this way, it just looks a bit cleaner.

You could get better speed (at the expense of some tempdb overhead) by taking the filtered_jobs CTE above, and instead inserting those values into a temp table and using the temp table in your union all instead.

For what it's worth, I recommend the UNION ALL approach that you're using over using an OR since you know you'll always have results in either pages or gates but not together.

share|improve this answer
1  
the comparison operator on the data is different in the two parts of the query, so this is not going to work. –  Sebastian Meine Mar 21 '13 at 17:13
    
@SebastianMeine Ah, I had missed that. You could still use it, I suppose, with doing >= then filtering the date on your unions. Would really incline me, at least, to try the temp table approach so that I could hit the data once, index the temp table, then pull as I needed. –  Jason Whisman Mar 21 '13 at 17:56
add comment

Assuming you have Management Studio available, one way to get some quick insight into it is to put both variants into one batch, and use Query > Display Estimated Execution Plan.

If I simplify your query down, with two variants:

declare @tableA table(col1 int)

insert into @tableA values (1)
insert into @tableA values (2)
insert into @tableA values (3)
insert into @tableA values (4)

select * from @tableA where col1 > 2 
union all 
select * from @tableA where col1 < 2

select * from (
    select * from @tableA where col1 > 2 
    union all 
    select * from @tableA where col1 < 2
) t where t.col1 >= 2

...then the execution plans as displayed are: query cost

Whether the above simplification (reduction?) makes those results incorrect for you or not is immaterial; the point is, you should be able to do the same thing with your query as-is.

If you want to start to interpret the execution plan, there's lots of resources online for that; this question might be a good place to start.

share|improve this answer
    
hi, to use this feature, I just need to put both queries into one editor and click "Display Estimated Execution Plan" ? Then the one who has the lowest Query Cost should be the better one, right? –  suud Mar 21 '13 at 6:38
    
This is a very dangerous approach. The query cost percentage does not have anything to do with the actual query performance. (I have seen queries where the one had 5% and the other 95%, yet the 5% query ran 1000 times slower than the 95% query) The only way to compare performance reliably is to actually measure it. –  Sebastian Meine Mar 21 '13 at 16:40
    
@SebastianMeine: Yeah, that's why I labelled it as 'quick insight' - should have bolded 'quick'. I also suggested understanding the actual results, not just looking at the percentages. –  Geoff Mar 21 '13 at 18:02
    
@suud: Not necessarily - as Sebastian said, the cost doesn't necessarily indicate real world performance, but IMO the results from the query plan give you some real insight into what the actual difference between the queries is. –  Geoff Mar 21 '13 at 18:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.