CODEWITHSUNDEEP
sqlsql-serversql-server-2008
PowerUser
PowerUser

Reputation: 11791

Inefficient if/then loop in a SQL query

I'd like to clean up the results of a SQL query that is always run manually from with Management Studio. But my if/then loop is taking much longer than the individual elements.

Currently, this Select statement runs instantly (less than 1 second) and is usually empty:

Select * from A join B on A.id=B.id

Instead of an empty result set, I wanted to display a message if there were no results (this is part of a larger multi-part query so the clarity would help). I changed it to this:

If (Select count(*) from A join B on A.id=B.id)>0
begin
    Select * from A join B on A.id=B.id
end
else
    Select 'No Results'

Since both Select statements in there run near instantly (I checked), I expect this entire snippet to run in the same amount of time. Instead, it takes EIGHT seconds. Why is this taking so much longer and is there a simple way around it?

Upvotes: 2

Views: 112

Answers (2)

jmc
jmc

Reputation: 393

I'd suggest checking the result count after the query. This has the down side of giving you a second result set in your output, but it has the upside of not querying the data twice. 

Select * from A join B on A.id=B.id
IF @@ROWCOUNT = 0 Select 'No Results'

Upvotes: 1

Ranjit Singh
Ranjit Singh

Reputation: 3735

use if exists

If exists (Select * from A join B on A.id=B.id)
begin
    Select * from A join B on A.id=B.id
end
else
    Select 'No Results'

Upvotes: 5

Related Questions