question

gauranga avatar image
gauranga asked

Better solution to replace a sub query.

Hello, Data in my table looks like below : > ID PId AId > 1 101 1001 > 2 102 1002 > 3 103 1001 > 4 104 1004 > 5 105 1001 > 6 106 1004 Here is my code. DECLARE @PId varchar(20) SELECT ID,AId, PId FROM Table1 WHERE AId = (SELECT AId FROM Table1 WHERE PId = @PId) If @PId = 101 then the result will be > ID PId AId > 1 101 1001 > 3 103 1001 > 5 105 1001 My question : is there a better way to achieve the same instead of using Sub query. Thanks in advance
sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
A lot depends on other information, not least of which is what do yuo mean by 'better'? Are we to assume this relates to a situation in your system where there is a similar circumstance but with much more data? Having a varchar variable and matching that to an INT column is a conversion that is unnecessary. Not having any indexes on the table is not good but I would hope the real tables you are working with would have one or two. You can get the same results with DECLARE @PId int set @PId = 101 SELECT t.ID,t.AId, t.PId FROM Table1 as t join Table1 as t1 on t.AID=t1.aid WHERE t1.pId = @PId but whether it is better depends on running it as a comparison and seeing the results. Until an index is added to the table then the estimated plan show my version as being more work than your code. Once an index of `create clustered index IX_AID on Table1 (AID)` is added however the cost changes. Can you give us more information or is this all the sort of information you need? [Edit] Trying this on a larger set of data (500k rows in Table1) brings up what i think might be an oversight in your code. You dont allow for more than one AId. Do you need to change your code to be ` WHERE AId in (SELECT AId FROM Table1 WHERE PId = @PId)` or do we need to work to a different rule? It's also showing that the activity on disc between the two options is the same....YMMV
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.