June 18, 2008 at 6:08 am
Hi all,
i am trying to optimisize a query. i would likee to know that in this case which one will be better. i feel in case 1 the select clause on ProductMaster table will be sending two hits to the d/b rather than case2. Just a little confused. am i write? does case2 work like a inner join.is inner join expensive?
thanks for the help.
CASE 1 :-
select ContractorCode,
A.ProjectID,
ProjectName,
(select ProductName from ProductMaster C where c.ProductCode = B.ProductCode) ProductName,
(select ProductType from ProductMaster C where c.ProductCode=B.ProductCode) ProductType,
(B.ProjectID+B.ProductCode) Item ,
B.ProductCode
from Projects A
inner join ProductAllocation B
ON A.ProjectID=B.ProjectID
where A.StatusOpen=1 and
A.ProjectID in (select ProjectID from UserProjectAccess where UserID=@userid)
CASE 2:-
select ContractorCode,
A.ProjectID,
ProjectName,
C.ProductName,
C.ProductType,
(B.ProjectID+ B.ProductCode) Item ,
B.ProductCode
from Projects A ,ProductAllocation B ,ProductMaster C
where A.StatusOpen=1 and
A.ProjectID=B.ProjectID and
c.ProductCode = B.ProductCode
A.ProjectID in (select ProjectID from UserProjectAccess where UserID=@userid)
June 18, 2008 at 7:05 am
Your first option uses correlated subqueries. It's a hidden row by row operation. It's gonna be slow, especially on larger result sets. The subqueries will run once for each row of the outer select statement.
The second is much better, though from a readability side I would strongly suggest you do the joins in the FROM clause rather than in the WHERE. SQL will run them identically, but joining in the WHERE is an older style and is not favoured. It's easier to miss a join out and get an expensive catresian product.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2008 at 11:04 pm
thanks gail for your help
June 19, 2008 at 2:33 am
SELECTa.ContractorCode,
a.ProjectID,
a.ProjectName,
d.ProductName,
d.ProductType,
b.ProjectID + b.ProductCode AS Item,
b.ProductCode
FROMProjects AS a
INNER JOINProductAllocation AS b ON b.ProjectID = a.ProjectID
INNER JOINUserProjectAccess AS c ON c.ProjectID = a.ProjectID
LEFT JOINProductMaster AS d ON d.ProductCode = b.ProductCode
WHEREa.StatusOpen = 1
AND c.UserID = @user-id
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply