IDS - VIEW unsing UNION of tables with
Hi, Has any one experiece very ... very slow execution time when querying a VIEW of the UNION of two large tables (9 and 10 millions rows) with 50 bytes/row? Any experience with the performance of a UNION for tables with millions of rows? Thanks! Reyna _________________________________________________________ Reyna Sabina Phone: (305) 361-4324 NOAA/AOML/PHOD Fax: (305) 361-4392 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov Miami, FL 33149-1087 "Things do not get better by being left alone." -Winston Churchill
If you run a SELECT that applies a filter or join to a VIEW build on a UNION the view will build a temp table containing ALL of the rows in the VIEW to which is applied the filter. The latest versions of IDS (IB 11.10 and later) are capable of folding the view into the outer query, but not always and earlier versions are not. [This is why it is important to ALWAYS post your version and platform information when you ask a question!] You can try running the final query under SET EXPLAIN ON; and see what's happening under the hood. Art On Fri, Jul 25, 2008 at 3:31 PM, Reyna.Sabina <Reyna.Sabina@noaa.gov> wrote: > Hi, > > Has any one experiece very ... very slow execution time when querying > a VIEW of the UNION of two large tables (9 and 10 millions rows) > with 50 bytes/row? > > Any experience with the performance of a UNION for tables with > millions of rows? > > Thanks! > > Reyna > _________________________________________________________ > Reyna Sabina Phone: (305) 361-4324 > NOAA/AOML/PHOD Fax: (305) 361-4392 > 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov > Miami, FL 33149-1087 > > "Things do not get better by being left alone." > > -Winston Churchill > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Reyna, If you used "UNION" defined the view, can you replace it with "UNION ALL"? Frank On Fri, Jul 25, 2008 at 4:08 PM, Art Kagel <art.kagel@gmail.com> wrote: > If you run a SELECT that applies a filter or join to a VIEW build on a > UNION > the view will build a temp table containing ALL of the rows in the VIEW to > which is applied the filter. The latest versions of IDS (IB 11.10 and > later) are capable of folding the view into the outer query, but not always > and earlier versions are not. [This is why it is important to ALWAYS post > your version and platform information when you ask a question!] You can try > running the final query under SET EXPLAIN ON; and see what's happening > under > the hood. > > Art > > On Fri, Jul 25, 2008 at 3:31 PM, Reyna.Sabina <Reyna.Sabina@noaa.gov> > wrote: > > > Hi, > > > > Has any one experiece very ... very slow execution time when querying > > a VIEW of the UNION of two large tables (9 and 10 millions rows) > > with 50 bytes/row? > > > > Any experience with the performance of a UNION for tables with > > millions of rows? > > > > Thanks! > > > > Reyna > > _________________________________________________________ > > Reyna Sabina Phone: (305) 361-4324 > > NOAA/AOML/PHOD Fax: (305) 361-4392 > > 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov > > Miami, FL 33149-1087 > > > > "Things do not get better by being left alone." > > > > -Winston Churchill > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >