April 19, 2006 at 9:27 pm
Hi,
I have a query where I am retrieving data using
the IN Clause. I am planning to rewrite the same query
using Joins. But before doing that, I want to know if
I can gain any performance using Join rather than IN
clause.
Here is the sample query
select company, department, division, sum(Amount)
from tableA
where company in ('A', 'B', 'C', 'D', 'E', 'F',
'J')
and division in ('H', 'I', 'J','K')
and department in ('L', 'M', 'N')
group by company, department, division
tableA contains millions of rows.
Instead of doing like above, I am thinking of
creating three temp tables. one for company, one for
division and department and join these temp tables
with main table like this
select A.company, A.department, A.division,
sum(Amount) from tableA A JOIN #company C ON
A.Company = C.Company
JOIN #department DE ON A.Department = DE.Department
JOIN #division DI ON A.Division = DE.Division
GROUP BY A.company, A.department, A.division
I am guessing that if I use IN clause, then SQL SERVER
will internally use JOINS to do the processing so that
there won't be any performance difference.
Please let me know.
Thanks,
Sridhar
April 20, 2006 at 12:58 am
The only way for you to know for sure is to try it out on your data.
Check the plan for your existing IN variant, and compare it with your alternate method.
Usually an IN clause will be converted to a join internally, but that's still an assumption. If it's true for you, only you can find out by actually testing it.
/Kenneth
April 20, 2006 at 8:42 am
That is because few things are faster than INNER JOIN using the FROM and ON clause, most SQL Server users still use the WHERE clause but the query processor ignores the WHERE clause when there is an ON clause. Check out SQL Performance by Peter Gulutzan for more info. A quick way to find out is click on show execution plan before running both queries. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply