Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Constraints in Inner Join


Using Constraints in Inner Join

Author
Message
punsaonline
punsaonline
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
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.

Cool
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)
punsaonline
punsaonline
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 14
No i killed the query as it was taking lot of time
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
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?

Cool
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)
punsaonline
punsaonline
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 14
yes, the first query is working fine and i get desired result.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
Then why worry about rewriting it in a different format?

Cool
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)
punsaonline
punsaonline
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search