Performance Issue w/View

All, IDS 11.50.FC5 O/S AIX 5.3 I have a table with approx. 460 million rows in it that is fragmented by quarter back to q1-2007. I have pulled the data out by year and created tables (fragmented by quarter) for each year. I then created a view and try to just run a simple select (ie... select first 2 * from table) and that runs for quite some time and eventually dies with errors stating not enough disk space to write sorted rows. Sorry, didn't write down the error numbers but am rerunning now so will get them eventually. If I run the same query against the original table, it comes back immediately with 2 rows. I can also change the view to include only 2 of the tables and get the same results. If the view only looks at one table, then it is also fast. I am running upd stats against all of my new tables now but i created the indices after I loaded them so wouldn;t that distribution info already be good? Does anyone have any words of wisdom to speed things up with my view? thanx, dan

What happens if you run the query against all of the tables directly without the view? Is the query plan different? What is the definition of the view? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 14, 2011 at 1:03 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > All, > > IDS 11.50.FC5 > O/S AIX 5.3 > > I have a table with approx. 460 million rows in it that is fragmented by > quarter back to q1-2007. > > I have pulled the data out by year and created tables (fragmented by > quarter) > for each year. I then created a view and try to just run a simple select > (ie... select first 2 * from table) and that runs for quite some time and > eventually dies with errors stating not enough disk space to write sorted > rows. Sorry, didn't write down the error numbers but am rerunning now so > will > get them eventually. > > If I run the same query against the original table, it comes back > immediately > with 2 rows. I can also change the view to include only 2 of the tables and > get the same results. If the view only looks at one table, then it is also > fast. > > I am running upd stats against all of my new tables now but i created the > indices after I loaded them so wouldn;t that distribution info already be > good? Does anyone have any words of wisdom to speed things up with my view? > > thanx, > dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175cb156d019f2049c42a06c

What is the statement used to create the view? and the select statement used against the view? From: "DAN MUELLER" <dan.mueller@trnswrks.com> To: ids@iiug.org Date: 02/14/2011 10:05 AM Subject: Performance Issue w/View [22759] Sent by: ids-bounces@iiug.org All, IDS 11.50.FC5 O/S AIX 5.3 I have a table with approx. 460 million rows in it that is fragmented by quarter back to q1-2007. I have pulled the data out by year and created tables (fragmented by quarter) for each year. I then created a view and try to just run a simple select (ie... select first 2 * from table) and that runs for quite some time and eventually dies with errors stating not enough disk space to write sorted rows. Sorry, didn't write down the error numbers but am rerunning now so will get them eventually. If I run the same query against the original table, it comes back immediately with 2 rows. I can also change the view to include only 2 of the tables and get the same results. If the view only looks at one table, then it is also fast. I am running upd stats against all of my new tables now but i created the indices after I loaded them so wouldn;t that distribution info already be good? Does anyone have any words of wisdom to speed things up with my view? thanx, dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The view looks like this right now: create view "informix".maintenance_log_tst (maintenance_log,table,key_1,key_2,key_3,key_4,key_5,key_6,column,old_value,new_ value,stamp,username,action) as select x0.maintenance_log ,x0.table ,x0.key_1 ,x0.key_2 ,x0.key_3 ,x0.key_4 ,x0.key_5 ,x0.key_6 ,x0.column ,x0.old_value ,x0.new_value ,x0.stamp ,x0.username ,x0.action from "informix".maintenance_log_new x0 union select x1.maintenance_log ,x1.table ,x1.key_1 , x1.key_2 ,x1.key_3 ,x1.key_4 ,x1.key_5 ,x1.key_6 ,x1.column ,x1.old_value ,x1.new_value ,x1.stamp ,x1.username ,x1.action from "informix".maintenance_log_2010 x1 ; The simplest query I run that shows this behaviour is: select count(*) from maintenance_log_tst this is against the view select count(*) from maintenance_log_new union select count(*) frommaintenance_log_2010; this returns immediately. From looking at the query plans, the view takes a sequestial scan on both tables and uses ALL fragments regardless of pdqpriotity settings. The union statement takes an indexed path on both tables. The indexes are identical.

Hi, I wonder if the query is the problem. When you do "select first * from table....", the entire table is read, then sorted if there is an order by clause, and only then are the first 2 rows returned. So you may be scanning/sorting the entire table. For that size table, a sort could take quite a bit of space. Even without a sort, keeping the result set, even unsorted, could take a lot of space. That's why using just one or two fragments is quick. Much less space required. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: Madison Pruet/Dallas/IBM@IBMUS To: ids@iiug.org Date: 02/14/2011 02:19 PM Subject: Re: Performance Issue w/View [22761] Sent by: ids-bounces@iiug.org What is the statement used to create the view? and the select statement used against the view? From: "DAN MUELLER" <dan.mueller@trnswrks.com> To: ids@iiug.org Date: 02/14/2011 10:05 AM Subject: Performance Issue w/View [22759] Sent by: ids-bounces@iiug.org All, IDS 11.50.FC5 O/S AIX 5.3 I have a table with approx. 460 million rows in it that is fragmented by quarter back to q1-2007. I have pulled the data out by year and created tables (fragmented by quarter) for each year. I then created a view and try to just run a simple select (ie... select first 2 * from table) and that runs for quite some time and eventually dies with errors stating not enough disk space to write sorted rows. Sorry, didn't write down the error numbers but am rerunning now so will get them eventually. If I run the same query against the original table, it comes back immediately with 2 rows. I can also change the view to include only 2 of the tables and get the same results. If the view only looks at one table, then it is also fast. I am running upd stats against all of my new tables now but i created the indices after I loaded them so wouldn;t that distribution info already be good? Does anyone have any words of wisdom to speed things up with my view? thanx, dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I have ruin other queries w/o the "first" keyword. The fact that the "non-view" query taked an indexed path and the viel takes a sequential scan on all tables is probably what is getting me but I don;t seem to be able to alter that path yet. Thanx tho. I appreciate any assistance I can get on this one. dan

You need to set IFX_FOLDVIEW to 1 in your ONCONFIG file and bounce the engine and you have to change the VIEW definition to use UNION ALL instead of just UNION. Since the separate tables have completely disjoint data sets the results from the UNION and UNION ALL should be identical, but the UNION ALL permits optimizations on the VIEW definition that a UNION clause prevents, including folding the VIEW definition into any query that references it. With the UNION clause and with IFX_FOLDVIEW set to default (0), Informix has to produce a temp table with the results of the view's defining query and then query that temp table to satisfy the SELECT against the VIEW. Try these two changes and see if that resolves the problems. See also the description of IFX_FOLDVIEW in the Administrator's Reference manual. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 14, 2011 at 2:29 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > The view looks like this right now: > > create view "informix".maintenance_log_tst > > (maintenance_log,table,key_1,key_2,key_3,key_4,key_5,key_6,column,old_value,new_ value,stamp,username,action) > as > select x0.maintenance_log ,x0.table ,x0.key_1 ,x0.key_2 ,x0.key_3> > ,x0.key_4 ,x0.key_5 ,x0.key_6 ,x0.column ,x0.old_value ,x0.new_value > > ,x0.stamp ,x0.username ,x0.action from "informix".maintenance_log_new > > x0 union select x1.maintenance_log ,x1.table ,x1.key_1 , > > x1.key_2 ,x1.key_3 ,x1.key_4 ,x1.key_5 ,x1.key_6 ,x1.column > > ,x1.old_value ,x1.new_value ,x1.stamp ,x1.username ,x1.action > > from "informix".maintenance_log_2010 x1 ; > > The simplest query I run that shows this behaviour is: > > select count(*) from maintenance_log_tst this is against the view> > select count(*) from maintenance_log_new union select count(*) from> maintenance_log_2010; this returns immediately. > > >From looking at the query plans, the view takes a sequestial scan on both > tables and uses ALL fragments regardless of pdqpriotity settings. The union > statement takes an indexed path on both tables. The indexes are identical. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517503b0800ddef049c437d43

I was researching IFX_FOLDVIEW when I received this tip. I will definately try it ASAP.

You should try the following: # IFX_FOLDVIEW - Enables (1) or disables (0) folding views that # have multiple tables or a UNION ALL clause. # Disabled by default. This is an onconfig parameter that changes how the optimizer handle's views. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/14/2011 11:51:33 AM: > [image removed] > > Re: Performance Issue w/View [22764] > > DAN MUELLER > > to: > > ids > > 02/14/2011 11:52 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I have ruin other queries w/o the "first" keyword. The fact that the > "non-view" query taked an indexed path and the viel takes a > sequential scan on > all tables is probably what is getting me but I don;t seem to be > able to alter > that path yet. > > Thanx tho. I appreciate any assistance I can get on this one. > > dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I believe you don't need IFX_FOLDVIEW (althouth it may be a good idea to use it), but you WILL NEED "UNION ALL" instead of UNION. UNION eliminates duplicates, so that's why you're running out of temporary space (and time). The fact that you probably don't have duplicate rows is irrelevant since the engine doesn't know it. Regards. On Mon, Feb 14, 2011 at 8:05 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > I was researching IFX_FOLDVIEW when I received this tip. I will definately > try > it ASAP. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c3b3a19d9d3049c485cf4

Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I have executed the query three ways now and still have my issue with the view pointing to multiple tables. I am also now using 'union all'. 1. If I execute the statement 'select count(*) from table1 union all select count(*) from table 2', an indexed path is taken on both tables and the results are shown instantaneously. 2. If I execute the following statement 'select count(*) from myview', a sequential scan is used in both tables in the view and the query eventually runs out of temp space. The actual view looks like 'create view myview (column list) as select (same column list) from table1 x0 union all select (same column list) from table2 x1 ; ' 3. Coincidently, if I execute the query 'select count(*) from anotherview' where anotherview is a view pointing only to the original giant table, indexed paths are taken and the results are also instantaneous. So my problem occurs when I have a view that is pointing to more than one large table. I guess here is where I would have to ask myself why am I bothering to even do this but there is good reason so I really need to get somewhere. Thanx, Dan

Hi Dan, Have you tried to put an optimizer directive in the select in the view? Jeff -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAN MUELLER Sent: Tuesday, February 15, 2011 8:24 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22779] Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I have executed the query three ways now and still have my issue with the view pointing to multiple tables. I am also now using 'union all'. 1. If I execute the statement 'select count(*) from table1 union all select count(*) from table 2', an indexed path is taken on both tables and the results are shown instantaneously. 2. If I execute the following statement 'select count(*) from myview', a sequential scan is used in both tables in the view and the query eventually runs out of temp space. The actual view looks like 'create view myview (column list) as select (same column list) from table1 x0 union all select (same column list) from table2 x1 ; ' 3. Coincidently, if I execute the query 'select count(*) from anotherview' where anotherview is a view pointing only to the original giant table, indexed paths are taken and the results are also instantaneous. So my problem occurs when I have a view that is pointing to more than one large table. I guess here is where I would have to ask myself why am I bothering to even do this but there is good reason so I really need to get somewhere. Thanx, Dan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Have you read the notes suggesting to create the view with "UNION ALL" instead of "UNION" or am I missing something? UNION will try to eliminate duplicate records. That means a large sort and that's because you run out of temp space. Regards. On Tue, Feb 15, 2011 at 2:23 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I have > executed the query three ways now and still have my issue with the view > pointing to multiple tables. I am also now using 'union all'. > > 1. If I execute the statement 'select count(*) from table1 union all select > count(*) from table 2', an indexed path is taken on both tables and the > results are shown instantaneously. > > 2. If I execute the following statement 'select count(*) from myview', a > sequential scan is used in both tables in the view and the query eventually > runs out of temp space. The actual view looks like 'create view myview > (column > list) as > select (same column list) from table1 > > x0 union all select (same column list) > > from table2 x1 ; ' > > 3. Coincidently, if I execute the query 'select count(*) from anotherview' > where anotherview is a view pointing only to the original giant table, > indexed > paths are taken and the results are also instantaneous. > > So my problem occurs when I have a view that is pointing to more than one > large table. I guess here is where I would have to ask myself why am I > bothering to even do this but there is good reason so I really need to get > somewhere. > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd6d29cdc4281049c5490e8

Sorry... I missed the point... If you run the UNION ALL in the query it's ok, but if you create the view with UNION ALL it's not? If that's the case I suggest a PMR... Regards. On Tue, Feb 15, 2011 at 4:22 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Have you read the notes suggesting to create the view with "UNION ALL" > instead of "UNION" or am I missing something? > UNION will try to eliminate duplicate records. That means a large sort and > that's because you run out of temp space. > Regards. > > > > On Tue, Feb 15, 2011 at 2:23 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > >> Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I have >> executed the query three ways now and still have my issue with the view >> pointing to multiple tables. I am also now using 'union all'. >> >> 1. If I execute the statement 'select count(*) from table1 union all >> select >> count(*) from table 2', an indexed path is taken on both tables and the >> results are shown instantaneously. >> >> 2. If I execute the following statement 'select count(*) from myview', a >> sequential scan is used in both tables in the view and the query >> eventually >> runs out of temp space. The actual view looks like 'create view myview >> (column >> list) as >> select (same column list) from table1 >> >> x0 union all select (same column list) >> >> from table2 x1 ; ' >> >> 3. Coincidently, if I execute the query 'select count(*) from anotherview' >> where anotherview is a view pointing only to the original giant table, >> indexed >> paths are taken and the results are also instantaneous. >> >> So my problem occurs when I have a view that is pointing to more than one >> large table. I guess here is where I would have to ask myself why am I >> bothering to even do this but there is good reason so I really need to get >> somewhere. >> >> Thanx, >> Dan >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c3e16f764fb049c54962c

Tried that - see my last post from this morning -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Tuesday, February 15, 2011 11:22 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22783] Have you read the notes suggesting to create the view with "UNION ALL" instead of "UNION" or am I missing something? UNION will try to eliminate duplicate records. That means a large sort and that's because you run out of temp space. Regards. On Tue, Feb 15, 2011 at 2:23 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I > have executed the query three ways now and still have my issue with > the view pointing to multiple tables. I am also now using 'union all'. > > 1. If I execute the statement 'select count(*) from table1 union all > select > count(*) from table 2', an indexed path is taken on both tables and > the results are shown instantaneously. > > 2. If I execute the following statement 'select count(*) from myview', > a sequential scan is used in both tables in the view and the query > eventually runs out of temp space. The actual view looks like 'create > view myview (column > list) as > select (same column list) from table1 > > x0 union all select (same column list) > > from table2 x1 ; ' > > 3. Coincidently, if I execute the query 'select count(*) from anotherview' > where anotherview is a view pointing only to the original giant table, > indexed paths are taken and the results are also instantaneous. > > So my problem occurs when I have a view that is pointing to more than > one large table. I guess here is where I would have to ask myself why > am I bothering to even do this but there is good reason so I really > need to get somewhere. > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd6d29cdc4281049c5490e8 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

That's the one I replied to, but I missed the important bit... sorry! On Tue, Feb 15, 2011 at 4:24 PM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote: > Tried that - see my last post from this morning > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Tuesday, February 15, 2011 11:22 AM > To: ids@iiug.org > Subject: Re: Performance Issue w/View [22783] > > Have you read the notes suggesting to create the view with "UNION ALL" > instead of "UNION" or am I missing something? > UNION will try to eliminate duplicate records. That means a large sort and > that's because you run out of temp space. > Regards. > > On Tue, Feb 15, 2011 at 2:23 PM, DAN MUELLER <dan.mueller@trnswrks.com > >wrote: > > > Okay, I now have IFX_FOLDVIEW set to 1 with the engine recycled. I > > have executed the query three ways now and still have my issue with > > the view pointing to multiple tables. I am also now using 'union all'. > > > > 1. If I execute the statement 'select count(*) from table1 union all > > select > > count(*) from table 2', an indexed path is taken on both tables and > > the results are shown instantaneously. > > > > 2. If I execute the following statement 'select count(*) from myview', > > a sequential scan is used in both tables in the view and the query > > eventually runs out of temp space. The actual view looks like 'create > > view myview (column > > list) as > > select (same column list) from table1 > > > > x0 union all select (same column list) > > > > from table2 x1 ; ' > > > > 3. Coincidently, if I execute the query 'select count(*) from > anotherview' > > where anotherview is a view pointing only to the original giant table, > > indexed paths are taken and the results are also instantaneous. > > > > So my problem occurs when I have a view that is pointing to more than > > one large table. I guess here is where I would have to ask myself why > > am I bothering to even do this but there is good reason so I really > > need to get somewhere. > > > > Thanx, > > Dan > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd6d29cdc4281049c5490e8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c3e16195070049c54ab95

UPDATE I enabled IFX_FOLDVIEW - no help. I changed my union to union all - no help. I added an index directive to a uniquely indexed serial column, a little help but still sux. I have opened a PMR. Bottom line on this one is: view to one table - great performance. query to multiple tables w/union and union all - great performance. view to same tables as above - sux. I am willing to try what comes my way on this one so if there are any additional items, please reply. thanx, dan

For our "education" could you post the explains of the various situations? Unless they're too big or complex... in that case maybe it's better to wait for tech support. Regards. On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > UPDATE > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - no > help. I > added an index directive to a uniquely indexed serial column, a little help > but still sux. > > I have opened a PMR. > > Bottom line on this one is: > > view to one table - great performance. > query to multiple tables w/union and union all - great performance. > view to same tables as above - sux. > > I am willing to try what comes my way on this one so if there are any > additional items, please reply. > > thanx, > dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd6d29c3723f6049c67829c

I will certainly try to explain in as few words as possible (-; 1. I have a view that points to a single table that has 460 million rows fragmented by quarters back to 2007. The majority of these fragments are in the same dbspace. 2. I have created separate tables for each year's worth of data and fragmented each of those tables by quarter in separate dbspaces. The indexes are also fragmented the same way but live in separate dbspaces than their associated data. 3. I can select count(*) from the old single table view with great performance. Takes indexed path. 4. I can select count(*) from the multiple new tables using a union or a union all with great performance. Takes indexes path. 5. A select count(*) from the new multi table view will run until it uses all of it's sort space and dies. I don't have the errors handy but can repro in about an hour. It does take sequential paths on both tables in the view at this point. 6. Adding an index directive to the multi table view allows it to finish but takes over ab hour to do so. It now takes indexed paths to the data using the directives. Hope this makes it understandable. Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, February 16, 2011 9:58 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22801] For our "education" could you post the explains of the various situations? Unless they're too big or complex... in that case maybe it's better to wait for tech support. Regards. On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > UPDATE > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - no > help. I added an index directive to a uniquely indexed serial column, > a little help but still sux. > > I have opened a PMR. > > Bottom line on this one is: > > view to one table - great performance. > query to multiple tables w/union and union all - great performance. > view to same tables as above - sux. > > I am willing to try what comes my way on this one so if there are any > additional items, please reply. > > thanx, > dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd6d29c3723f6049c67829c ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Ups... I already understood the issue. I was referring to the SQL SET EXPLAIN output :) Can you run the queries in the several situations with SET EXPLAIN ON and post here the query plans (if these isn't too big/complex). Regards. On Wed, Feb 16, 2011 at 3:19 PM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote: > I will certainly try to explain in as few words as possible (-; > > 1. I have a view that points to a single table that has 460 million rows > fragmented by quarters back to 2007. The majority of these fragments are in > the same dbspace. > 2. I have created separate tables for each year's worth of data and > fragmented > each of those tables by quarter in separate dbspaces. The indexes are also > fragmented the same way but live in separate dbspaces than their associated > data. > 3. I can select count(*) from the old single table view with great > performance. Takes indexed path. > 4. I can select count(*) from the multiple new tables using a union or a > union > all with great performance. Takes indexes path. > 5. A select count(*) from the new multi table view will run until it uses > all > of it's sort space and dies. I don't have the errors handy but can repro in > about an hour. It does take sequential paths on both tables in the view at > this point. > 6. Adding an index directive to the multi table view allows it to finish > but > takes over ab hour to do so. It now takes indexed paths to the data using > the > directives. > > Hope this makes it understandable. > > Thanx, > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Wednesday, February 16, 2011 9:58 AM > To: ids@iiug.org > Subject: Re: Performance Issue w/View [22801] > > For our "education" could you post the explains of the various situations? > Unless they're too big or complex... in that case maybe it's better to wait > for tech support. > Regards. > > On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com > >wrote: > > > UPDATE > > > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - no > > help. I added an index directive to a uniquely indexed serial column, > > a little help but still sux. > > > > I have opened a PMR. > > > > Bottom line on this one is: > > > > view to one table - great performance. > > query to multiple tables w/union and union all - great performance. > > view to same tables as above - sux. > > > > I am willing to try what comes my way on this one so if there are any > > additional items, please reply. > > > > thanx, > > dan > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd6d29c3723f6049c67829c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c1038960659049c681f62

I just did it for support and it is pretty large. I would be happy to email the output if you want to provide your address but I don't think I should clutter up the forum??? Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, February 16, 2011 10:42 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22803] Ups... I already understood the issue. I was referring to the SQL SET EXPLAIN output :) Can you run the queries in the several situations with SET EXPLAIN ON and post here the query plans (if these isn't too big/complex). Regards. On Wed, Feb 16, 2011 at 3:19 PM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote: > I will certainly try to explain in as few words as possible (-; > > 1. I have a view that points to a single table that has 460 million > rows fragmented by quarters back to 2007. The majority of these > fragments are in the same dbspace. > 2. I have created separate tables for each year's worth of data and > fragmented each of those tables by quarter in separate dbspaces. The > indexes are also fragmented the same way but live in separate dbspaces > than their associated data. > 3. I can select count(*) from the old single table view with great > performance. Takes indexed path. > 4. I can select count(*) from the multiple new tables using a union or > a union all with great performance. Takes indexes path. > 5. A select count(*) from the new multi table view will run until it > uses all of it's sort space and dies. I don't have the errors handy > but can repro in about an hour. It does take sequential paths on both > tables in the view at this point. > 6. Adding an index directive to the multi table view allows it to > finish but takes over ab hour to do so. It now takes indexed paths to > the data using the directives. > > Hope this makes it understandable. > > Thanx, > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, February 16, 2011 9:58 AM > To: ids@iiug.org > Subject: Re: Performance Issue w/View [22801] > > For our "education" could you post the explains of the various situations? > Unless they're too big or complex... in that case maybe it's better to > wait for tech support. > Regards. > > On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com > >wrote: > > > UPDATE > > > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - > > no help. I added an index directive to a uniquely indexed serial > > column, a little help but still sux. > > > > I have opened a PMR. > > > > Bottom line on this one is: > > > > view to one table - great performance. > > query to multiple tables w/union and union all - great performance. > > view to same tables as above - sux. > > > > I am willing to try what comes my way on this one so if there are > > any additional items, please reply. > > > > thanx, > > dan > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd6d29c3723f6049c67829c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c1038960659049c681f62 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

What's the PMR number ? Regards, Vaibhav S Dantale ___________________________________________________________ If you think you can't do, you can't. If you think you can do, still you are right Competitive Technology Enablement, Informix Dynamic Server Information Management Address: IBM India Software Labs, Cell: +91-9503161335 Email: vdantale@in.ibm.com My Blue page Informix Competitive Wiki (IBM Internal) "Dan Mueller" <Dan.Mueller@trnswrks.com> Sent by: ids-bounces@iiug.org 02/16/2011 09:41 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: Performance Issue w/View [22804] I just did it for support and it is pretty large. I would be happy to email the output if you want to provide your address but I don't think I should clutter up the forum??? Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, February 16, 2011 10:42 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22803] Ups... I already understood the issue. I was referring to the SQL SET EXPLAIN output :) Can you run the queries in the several situations with SET EXPLAIN ON and post here the query plans (if these isn't too big/complex). Regards. On Wed, Feb 16, 2011 at 3:19 PM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote: > I will certainly try to explain in as few words as possible (-; > > 1. I have a view that points to a single table that has 460 million > rows fragmented by quarters back to 2007. The majority of these > fragments are in the same dbspace. > 2. I have created separate tables for each year's worth of data and > fragmented each of those tables by quarter in separate dbspaces. The > indexes are also fragmented the same way but live in separate dbspaces > than their associated data. > 3. I can select count(*) from the old single table view with great > performance. Takes indexed path. > 4. I can select count(*) from the multiple new tables using a union or > a union all with great performance. Takes indexes path. > 5. A select count(*) from the new multi table view will run until it > uses all of it's sort space and dies. I don't have the errors handy > but can repro in about an hour. It does take sequential paths on both > tables in the view at this point. > 6. Adding an index directive to the multi table view allows it to > finish but takes over ab hour to do so. It now takes indexed paths to > the data using the directives. > > Hope this makes it understandable. > > Thanx, > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, February 16, 2011 9:58 AM > To: ids@iiug.org > Subject: Re: Performance Issue w/View [22801] > > For our "education" could you post the explains of the various situations? > Unless they're too big or complex... in that case maybe it's better to > wait for tech support. > Regards. > > On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com > >wrote: > > > UPDATE > > > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - > > no help. I added an index directive to a uniquely indexed serial > > column, a little help but still sux. > > > > I have opened a PMR. > > > > Bottom line on this one is: > > > > view to one table - great performance. > > query to multiple tables w/union and union all - great performance. > > view to same tables as above - sux. > > > > I am willing to try what comes my way on this one so if there are > > any additional items, please reply. > > > > thanx, > > dan > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd6d29c3723f6049c67829c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c1038960659049c681f62 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

39540,500,000 ________________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Vaibhav S Dantale [vdantale@in.ibm.com] Sent: Wednesday, February 16, 2011 11:26 PM To: ids@iiug.org Subject: RE: Performance Issue w/View [22812] What's the PMR number ? Regards, Vaibhav S Dantale ___________________________________________________________ If you think you can't do, you can't. If you think you can do, still you are right Competitive Technology Enablement, Informix Dynamic Server Information Management Address: IBM India Software Labs, Cell: +91-9503161335 Email: vdantale@in.ibm.com My Blue page Informix Competitive Wiki (IBM Internal) "Dan Mueller" <Dan.Mueller@trnswrks.com> Sent by: ids-bounces@iiug.org 02/16/2011 09:41 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: Performance Issue w/View [22804] I just did it for support and it is pretty large. I would be happy to email the output if you want to provide your address but I don't think I should clutter up the forum??? Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, February 16, 2011 10:42 AM To: ids@iiug.org Subject: Re: Performance Issue w/View [22803] Ups... I already understood the issue. I was referring to the SQL SET EXPLAIN output :) Can you run the queries in the several situations with SET EXPLAIN ON and post here the query plans (if these isn't too big/complex). Regards. On Wed, Feb 16, 2011 at 3:19 PM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote: > I will certainly try to explain in as few words as possible (-; > > 1. I have a view that points to a single table that has 460 million > rows fragmented by quarters back to 2007. The majority of these > fragments are in the same dbspace. > 2. I have created separate tables for each year's worth of data and > fragmented each of those tables by quarter in separate dbspaces. The > indexes are also fragmented the same way but live in separate dbspaces > than their associated data. > 3. I can select count(*) from the old single table view with great > performance. Takes indexed path. > 4. I can select count(*) from the multiple new tables using a union or > a union all with great performance. Takes indexes path. > 5. A select count(*) from the new multi table view will run until it > uses all of it's sort space and dies. I don't have the errors handy > but can repro in about an hour. It does take sequential paths on both > tables in the view at this point. > 6. Adding an index directive to the multi table view allows it to > finish but takes over ab hour to do so. It now takes indexed paths to > the data using the directives. > > Hope this makes it understandable. > > Thanx, > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, February 16, 2011 9:58 AM > To: ids@iiug.org > Subject: Re: Performance Issue w/View [22801] > > For our "education" could you post the explains of the various situations? > Unless they're too big or complex... in that case maybe it's better to > wait for tech support. > Regards. > > On Wed, Feb 16, 2011 at 2:55 PM, DAN MUELLER <dan.mueller@trnswrks.com > >wrote: > > > UPDATE > > > > I enabled IFX_FOLDVIEW - no help. I changed my union to union all - > > no help. I added an index directive to a uniquely indexed serial > > column, a little help but still sux. > > > > I have opened a PMR. > > > > Bottom line on this one is: > > > > view to one table - great performance. > > query to multiple tables w/union and union all - great performance. > > view to same tables as above - sux. > > > > I am willing to try what comes my way on this one so if there are > > any additional items, please reply. > > > > thanx, > > dan > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --000e0cd6d29c3723f6049c67829c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c1038960659049c681f62 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.