Query Time execution

Question

Just want to ask which takes more time.

Example scenario below:

SELECT *
FROM 
    (SELECT * 
     FROM t1 
     WHERE [Date] BETWEEN "20151012" AND "20151018" 

     UNION ALL 

     SELECT * 
     FROM t2 
     WHERE [Date] BETWEEN "20151012" AND "20151018"

     UNION ALL

     SELECT * 
     FROM t3 
     WHERE [Date] BETWEEN "20151012" AND "20151018" 
) AS T

or this statement:

SELECT * 
FROM 
    (SELECT * FROM t1 

     UNION ALL 

     SELECT * FROM t2 

     UNION ALL

     SELECT * FROM t3) AS T
WHERE 
    [Date] BETWEEN "20151012" AND "20151018"

Thanks a lot!

Use an EXPLAIN to check the execution plan or actually run the queries. — Mr. Llama, Oct 14 '15 at 20:56
The 1st query doesn't need the outer `SELECT * FROM`, it is just redundant. As far as query execution time goes it depends on different factors (how large the tables are, what indexes you have on each table, etc). Try Mr.Llama's suggestion to see what the execution plan will look like. — John Odom, Oct 14 '15 at 21:01
Will try that, thanks. but I assume second query will take more time cause it will combine all records from those tables then filter the combined records. Is my understanding have some sense? — r-r, Oct 14 '15 at 21:13
Logically yes but most SQL Engines should automatically optimize queries such as this so it's likely the explain output will be the same. — Robin Gertenbach, Oct 14 '15 at 21:23
SQL Server doesn't have EXPLAIN, please see, e.g., http://stackoverflow.com/q/3449814/1115360 for the equivalent. — Andrew Morton, Oct 14 '15 at 21:33
You're asking about something called a _predicate push_. If you search on that term you'll find some more information. — Nick.Mc, Oct 14 '15 at 21:34

score 0 · Answer 1 · answered Oct 14 '15 at 21:28

I believe safest and most explicit version is:

SELECT * 
FROM t1 
WHERE [Date] BETWEEN "20151012" AND "20151018" 

UNION ALL 

SELECT * 
FROM t2 
WHERE [Date] BETWEEN "20151012" AND "20151018"

UNION ALL

SELECT * 
FROM t3 
WHERE [Date] BETWEEN "20151012" AND "20151018"

There is no need to hope that the query plan is going to figure out which indexes can be used in the sub-query. You might get equal performance, but you're not going to get worse from this one.

score 0 · Answer 2 · answered Oct 14 '15 at 21:38

SQL Server will have no problems pushing the predicates down to the base tables here.

You can check the execution plans to verify this.

I'd be more concerned about the double quotes around string literals. You should use single quotes so the query works correctly under default quoted_identifier settings and it doesn't interpret 20151012 as an attempted column reference.

Query Time execution

2 Answers2