Using Constraints in Inner Join

Question

Using Constraints in Inner Join

punsaonline

SSC Veteran

Points: 247
More actions
May 19, 2009 at 2:33 pm

#155727

Hi,
My Query previously looks like that
Select A.A1,B.B2
FROM A_TABLE A
INNER JOIN B_TABLE B
ON A.A1 = B.B1
WHERE B.B2 = 'XYZ'
This query executed in seconds but when i changed this query to
Select A.A1,B.B2
FROM A_TABLE A
INNER JOIN (SELECT B1,B2 FROM B_TABLE
WHERE B2 = 'XYZ'
)B
ON A.A1 = B.B1
then this query takes more than an hour. It's wired but i don't know the reason. Please help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

punsaonline (5/19/2009)
Hi,
My Query previously looks like that
Select A.A1,B.B2
FROM A_TABLE A
INNER JOIN B_TABLE B
ON A.A1 = B.B1
WHERE B.B2 = 'XYZ'
This query executed in seconds but when i changed this query to
Select A.A1,B.B2
FROM A_TABLE A
INNER JOIN (SELECT B1,B2 FROM B_TABLE
WHERE B2 = 'XYZ'
)B
ON A.A1 = B.B1
then this query takes more than an hour. It's wired but i don't know the reason. Please help.

Does the first query return the expected result set?

You might want to look at the actual execution plan for both queries and see if there is a difference. I wouldn't be surprised if they are different.

punsaonline SSC Veteran Points: 247 More actions · Answer 2

No i killed the query as it was taking lot of time

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

punsaonline (5/19/2009)
No i killed the query as it was taking lot of time

In your original post, you said the first query returned in seconds. Did it return the expected results?

punsaonline SSC Veteran Points: 247 More actions · Answer 4

yes, the first query is working fine and i get desired result.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Then why worry about rewriting it in a different format?

punsaonline SSC Veteran Points: 247 More actions · Answer 6

If Microsoft would have think that then you might not get 2005, then 2008 so on, as database service was working fine earlier with 2000 also.

I was just looking at different methods of writing a same query and i got fixed with that problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

punsaonline (5/19/2009)
If Microsoft would have think that then you might not get 2005, then 2008 so on, as database service was working fine earlier with 2000 also.
I was just looking at different methods of writing a same query and i got fixed with that problem.

You are comparing apples (your queries) to oranges (sql server engine).

If you want to know why one version of the query is better than the other, you will need to compare the actual execution plans of the queries which means letting them both run to completion.