| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2012-02-21 : 15:34:46
|
| I have query which uses full outer join and had performance issue which is taking 4 hours to process which 10 millions records..Is there a better way instead full outer join in query..thanks for you help in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-02-21 : 16:10:16
|
| Here is the extract of a query..select stuid,stu_date,case when datediff(hh,st_date,getdate())>0 then st_date else '' end as st_dateinto tableAfrom studa a full outer join studb bon a.stuid = b.stuidand a.stustatus=b.stustatusand a.rank = b.rank where a.stustatus is not null |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 16:16:47
|
| You don't need a full outer join there because the where clause has already turned it into a left outer join.This should be equivalentselect stuid,stu_date,case when datediff(hh,st_date,getdate())>0 then st_date else '' end as st_dateinto tableAfrom studa a left outer join studb bon a.stuid = b.stuidand a.stustatus=b.stustatusand a.rank = b.rankwhere a.stustatus is not nullHow many rows in those tables? What indexes do you have on them?--Gail ShawSQL Server MVP |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-02-21 : 16:23:39
|
| there are nearly 10 millions rows in both tables and there is no index defined on the table. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 17:14:30
|
| Then this is going to be slow regardless of join type. There's no row-limiting predicate, so SQL has to process both tables in their entirety. That means two table scans and a hash join. With 10 million rows it's not going to be fast.How many rows get returned? Most of the 10 million?--Gail ShawSQL Server MVP |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-02-21 : 17:39:51
|
| It is returning the 9 million rows..Please advise to improve performance as it is taking longer longer time to process. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-22 : 06:02:11
|
| what are you actually trying to do?are you trying to weed out duplicates?There may be better ways if you describe the actual goal you want.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-22 : 06:23:06
|
| Where is it returning those 9 million rows to? What's the client going to do with 9 million rows?--Gail ShawSQL Server MVP |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-22 : 07:03:05
|
quote:
Originally posted by GilaMonster
Where is it returning those 9 million rows to? What's the client going to do with 9 million rows?--Gail ShawSQL Server MVP
Well it's going into another table -- I think it may be a clean-up exercise...Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-02-22 : 07:30:25
|
| You are correct it will cleanup the execerise basic the tables data will be used to generate the reports. |
|
|
|
|
Full outer join