x

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

more ▼

asked Aug 19, 2011 at 04:06 PM in Default

avatar image

gauranga
13 1 1 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Aug 20, 2011 at 02:37 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x54

asked: Aug 19, 2011 at 04:06 PM

Seen: 955 times

Last Updated: Aug 19, 2011 at 07:32 PM

Copyright 2018 Redgate Software. Privacy Policy