SQL Server 2008: Optimize Query Performance with known empty result

Question

I have an application in which a user has a mask which runs sql statements against a SQL Server 2008 database. In addition the user can set parameters in the mask. Consider a mask with one parameter, which is a Dropdown with 2 selections: "Planes" and "Cars".

When the User selects "Cars" and hits the "Execute" button, the following SQL statement which I configured before in the mask hits the database.

SELECT cars.id, cars.name
FROM cars
WHERE 'Cars' = 'Cars'

UNION ALL

SELECT planes.id, planes.name
FROM planes
WHERE 'Planes' = 'Cars'

(This a quite made up example, as the queries in my Application are far more complex with lots of JOINS and so on...)

Even if I take the second part and paste it into the SQL Server Management studio, set some parameters and hit execute, the query will take several seconds to complete... with an empty result.

My question now: How can I optimize the second part, so the SQL Server recognizes that in the second SELECT statement, there really is nothing to do?

EDIT:

The reason why my second ("dead") query executes for some time is the following: Inside the query there are JOINS, along with a Sub-SELECT in the WHERE clause. Let's say

SELECT planes.id, planes.name
FROM planes
INNER JOIN very_complex_colour_view colours
     ON colours.id = planes.colour.id
WHERE 'Planes' = 'Cars'

In fact even the "planes" table is a complex view itself.

Can you not use [IN](http://msdn.microsoft.com/en-us/library/ms177682.aspx) in your query? — acermate433s, Jun 06 '13 at 14:18

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

Depending on parameter it is selecting records from respective table. So no need of using UNION ALL.

Use IF ELSE construct -

DECLARE @Input VARCHAR(20) = 'Cars'

IF (@Input = 'Cars')
BEGIN
  SELECT cars.id, cars.name
  FROM cars
END 
ELSE IF (@Input = 'Planes')
BEGIN
  SELECT planes.id, planes.name
  FROM planes  
END

This would also help SQL Optimizer to use Parameter Sniffing technique and use best execution plan which would improve your query performance.

More on Parameter Sniffing -

edited May 23 '17 at 11:57

Community

1
1

answered Jun 06 '13 at 14:20

Parag Meshram

8,281
10
52
88

I tried your solution, the second query in a if(1=2)... construct, unfortunately it didn't solve the problem. Will take a look at the Parameter sniffing, thanks for the hint! – curmudgeon Jun 06 '13 at 15:36
@curmudgeon why it did not work? it wont even hit `planes` table if you are passing parameter as `Cars`. I believe your query is slightly more complicated than that of problem. – Parag Meshram Jun 06 '13 at 17:36
@curmudgeon can you put some code with number of dummy tables? I think you need to use `EXISTS` in `if` for checking. I could only guess till you put some code. – Parag Meshram Jun 06 '13 at 17:38

score 0 · Answer 2 · answered Jun 06 '13 at 14:28

When I run the following query on my system:

select *
from <really big table that is not in the cache>
where 'planes' = 'cars'

The results returned in about 1 second the first time and subsequent times immediately.

I suspect that your actual query is more complicated than this example. And, the simplification is eliminating the problem. Even when I try the above with a join, there is basically no performance hit.

Based on the execution plans I'm seeing, SQL Server recognizes that the constant is always false. The following query:

select *
from Published_prev2..table1 sv join
     Published_prev2..table2 sl
     on sv.id= sl.id
where 'planes' = 'cars'

Produces a constant scan for the execution plan. The same query with 'cars = cars' produces a more complex plan that has joins and so on.

SQL Server 2008: Optimize Query Performance with known empty result

2 Answers2