CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
Brian Webster
Brian Webster

Reputation: 30865

SQL - Is it better to do a subquery here or an extended WHERE clause?

The CUST table below will be joined with ~10 tables.

For this subquery in particular, am I better off simply joining up directly with the Customer table and moving the subquery's 4-part WHERE clause to the main query's WHERE clause?

I'm primarily wondering if it is possible to cut down on the amount of processing that SQL Server has to do if we localize portions of the master WHERE Clause by creating subqueries as below.

select * From 
(select CKey, CID, CName from MainDB.dbo.Customer 
     where 
        LOC = 'ARK' 
        and Status = 1 
        and CID not like 'KAN%' 
        and CID not like 'MIS%') as CUST

Upvotes: 1

Views: 351

Answers (3)

DaveWilliamson
DaveWilliamson

Reputation: 370

I suggest that if you are joining that many tables it would be better to build a view.

Upvotes: 0

gbn
gbn

Reputation: 432421

In older versions, yes, I've seen huge improvements using derived tables (not a subquery) rather then all in one JOIN/WHERE. It's less relevant now since SQL Server 2005

However, why not try both and see what happens?

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332661

Based on what you provided, there's no need for the subquery. Without the ~10 joins to the derived table CUST, it's extremely difficult to say what should or should not be done.

Upvotes: 3

Related Questions