Join performance
Rohit Singh asked how best to join a 4-million-row table to a 1-million-row table with no filters, and whether an outer join helps. Art Kagel said outer join would not be faster and the optimizer picks the join order if UPDATE STATISTICS is run properly. Jack Parker and Uri Haham suggested a hash join, with Uri adding PDQ and big memory and read-ahead.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi All, What is the best way (Performance wise) to join two tables (No filters in where clause). One table contains roughly 4 million rows and the other contains roughly 1 million rows. The current program uses following SQL. Select A.*, B.* from A, B where A.col1 = B.col1 Whether making the join on the second table an outer join helps in such scenarios? I was also wondering whether after selecting rows from one table, selecting rows from the second table will be faster (As the second table is a reference table, col1 in tab B is uniquely indexed). Any other suggestions (apart from changing the DB Schema) Thanks Rohit
----- Original Message ----- From: Rohit Singh <rohit34@rediffmail.com> At: 11/10 18:39 > Hi All, Hi. > What is the best way (Performance wise) to join two tables (No filters in where > clause). One table contains roughly 4 million rows and the other contains > roughly 1 million rows. > The current program uses following SQL. > > Select A.*, B.* > from A, B > where A.col1 = B.col1 Looks good to me. > Whether making the join on the second table an outer join helps in such > scenarios? No, it will just cause unmatched rows to return a NULL partner record from the OUTER table. May be a little slower, can't be faster. > I was also wondering whether after selecting rows from one table, selecting rows > from the second table will be faster (As the second table is a reference table, > col1 in tab B is uniquely indexed). The optimizer will decide the best order in which to join the tables (ie a first then b, b first then a). It can only make good decisions however if UPDATE STATISTICS has been run properly. Follow the instructions in the Performance Guide or get my dostats utility (in the package utils2_ak) which implements the protocol described in the guide. You can download utils2_ak from the IIUG Software Repository. > Any other suggestions (apart from changing the DB Schema) No. Art S. Kagel > Thanks > Rohit
Hash join. It doesn't matter what the order of the columns in the select statement - the optimizer will figure it out. Of course you have to figure out how to get that. Holler if you need help. j. ----- Original Message ----- From: "ROHIT SINGH" <rohit34@rediffmail.com> To: <ids@iiug.org> Sent: Wednesday, November 10, 2004 7:22 PM Subject: Join performance [3662] > Hi All, > > What is the best way (Performance wise) to join two tables (No filters in where clause). One table contains roughly 4 million rows and the other contains roughly 1 million rows. > > The current program uses following SQL. > > Select A.*, B.* > from A, B > where A.col1 = B.col1 > > Whether making the join on the second table an outer join helps in such scenarios? > > I was also wondering whether after selecting rows from one table, selecting rows from the second table will be faster (As the second table is a reference table, col1 in tab B is uniquely indexed). > > Any other suggestions (apart from changing the DB Schema) > > Thanks > Rohit > > >
Hi > > Try doing Hash Join and Use PDQ for the job (you can put directive to > avoid index) > You should configure PDQ to use a lot of memory and configure large RA > parameters > > Uri > > ROHIT SINGH wrote: > >> Hi All, >> >> What is the best way (Performance wise) to join two tables (No >> filters in where clause). One table contains roughly 4 million rows >> and the other contains roughly 1 million rows. >> >> The current program uses following SQL. >> >> Select A.*, B.* >> from A, B >> where A.col1 = B.col1 >> >> Whether making the join on the second table an outer join helps in >> such scenarios? >> >> I was also wondering whether after selecting rows from one table, >> selecting rows from the second table will be faster (As the second >> table is a reference table, col1 in tab B is uniquely indexed). >> >> Any other suggestions (apart from changing the DB Schema) >> >> Thanks >> Rohit >> >> >> >> *********************************************** >> This Mail Was Scanned By Mail-seCure System in Matrix >> Herzeliya >> *********************************************** >> >> >> >> >
What a truly crappy answer. In the sense that it doesn't deal with the nuances at all. Ah well, I did at least write up this whole sort of thing some years back - here's the link. http://www-106.ibm.com/developerworks/db2/zones/informix/library/techarticle/par ker/part-1.pdf j. ----- Original Message ----- From: "Jack Parker" <vze2qjg5@verizon.net> To: <ids@iiug.org> Sent: Wednesday, November 10, 2004 9:23 PM Subject: Re: Join performance [3664] > > Hash join. It doesn't matter what the order of the columns in the select > statement - the optimizer will figure it out. Of course you have to figure > out how to get that. Holler if you need help. > > j. > > ----- Original Message ----- > From: "ROHIT SINGH" <rohit34@rediffmail.com> > To: <ids@iiug.org> > Sent: Wednesday, November 10, 2004 7:22 PM > Subject: Join performance [3662] > > > > Hi All, > > > > What is the best way (Performance wise) to join two tables (No filters in > where clause). One table contains roughly 4 million rows and the other > contains roughly 1 million rows. > > > > The current program uses following SQL. > > > > Select A.*, B.* > > from A, B > > where A.col1 = B.col1 > > > > Whether making the join on the second table an outer join helps in such > scenarios? > > > > I was also wondering whether after selecting rows from one table, > selecting rows from the second table will be faster (As the second table is > a reference table, col1 in tab B is uniquely indexed). > > > > Any other suggestions (apart from changing the DB Schema) > > > > Thanks > > Rohit > > > > > > > > > >