Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Constraints in Inner Join Expand / Collapse
Author
Message
Posted Tuesday, May 19, 2009 2:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:03 AM
Points: 13, Visits: 14
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.

Post #720158
Posted Tuesday, May 19, 2009 3:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720197
Posted Tuesday, May 19, 2009 4:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:03 AM
Points: 13, Visits: 14
No i killed the query as it was taking lot of time
Post #720199
Posted Tuesday, May 19, 2009 4:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720203
Posted Tuesday, May 19, 2009 4:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:03 AM
Points: 13, Visits: 14
yes, the first query is working fine and i get desired result.
Post #720208
Posted Tuesday, May 19, 2009 4:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
Then why worry about rewriting it in a different format?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720211
Posted Tuesday, May 19, 2009 4:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:03 AM
Points: 13, Visits: 14
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.
Post #720213
Posted Tuesday, May 19, 2009 4:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse