Update Statistics & temp tables

Hello wise ones I have a philosophical question... I've got an application that creates a relatively large temp table (850,000 rows). It is created "with no log" and in the tempdbspace etc. I suspect it is causing my application to slow down. The temp table is created, filled with data, and then the indexes are applied. Update statistics is then run (from the application, in this case a 4gl application) on the table. I tried changing the application to run update statistics medium, high etc, and this syntax is not supported from 4gl. (Only update statistics, which by default does it low). As important as it is to run a correct update statistics strategy (high on specific columns, medium on others), is it not equally important to have these same advantages on a temp table that is being read over the course of an 18 hour batch process? Against my better judgement (the fact that a temporarily needed table should be built as a "temp table", instead of a permanent one), I am going to try creating the table each time in the application as a permanent table, fill it, apply indexes, update statistics high/medium, use the table in my batch process, and then drop it. I realize there will be some initial overhead because of logging, but I'm willing to make the sacrifice. Has anyone else ever ran into the need to update statistics on a large temp table (in 4gl). If so, was this the solution you came up with? I appreciate any enlightenment... Bryan Sent via Deja.com http://www.deja.com/ Before you buy.

bwhite@deroyal.com wrote: > > Hello wise ones > > I have a philosophical question... > > I've got an application that creates a relatively large temp table > (850,000 rows). It is created "with no log" and in the tempdbspace etc. > I suspect it is causing my application to slow down. The temp table is > created, filled with data, and then the indexes are applied. Update > statistics is then run (from the application, in this case a 4gl > application) on the table. I tried changing the application to run > update statistics medium, high etc, and this syntax is not supported > from 4gl. (Only update statistics, which by default does it low). Either use PREPARE and EXECUTE for the more complex statement, or upgrade to 7.30 and write the more complex statement in an SQL block. > As important as it is to run a correct update statistics strategy (high > on specific columns, medium on others), is it not equally important to > have these same advantages on a temp table that is being read over the > course of an 18 hour batch process? Yes. Actually, I'm pleased you even know of doing UPDATE STATISTICS on temp tables at all; many have not thought of doing this. In your case, the benefits are clear cut; that is a non-trivial temp table. > Against my better judgement (the fact that a temporarily needed table > should be built as a "temp table", instead of a permanent one), I am > going to try creating the table each time in the application as a > permanent table, fill it, apply indexes, update statistics high/medium, > use the table in my batch process, and then drop it. I realize there > will be some initial overhead because of logging, but I'm willing to > make the sacrifice. > > Has anyone else ever ran into the need to update statistics on a large > temp table (in 4gl). If so, was this the solution you came up with? PREPARE and EXECUTE will pretty much always work. The SQL block is a recent enhancement. Either is better than using the overhead of a logged permanent table (and also allows concurrent running of the application, whereas a permanent table does not). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

bwhite@deroyal.com wrote: > > Hello wise ones > > I have a philosophical question... > > I've got an application that creates a relatively large temp table > (850,000 rows). It is created "with no log" and in the tempdbspace etc. > I suspect it is causing my application to slow down. The temp table is > created, filled with data, and then the indexes are applied. Update > statistics is then run (from the application, in this case a 4gl > application) on the table. I tried changing the application to run > update statistics medium, high etc, and this syntax is not supported > from 4gl. (Only update statistics, which by default does it low). You're just thinking too hard. Build the UPDATE STATISTICS HIGH/MEDIUM/LOW statement, PREPARE it, and EXECUTE it. > As important as it is to run a correct update statistics strategy (high > on specific columns, medium on others), is it not equally important to > have these same advantages on a temp table that is being read over the > course of an 18 hour batch process? Yes if the table and indexes are being hit frequently with multiple queries and it has a substantial amount of data and several similar index keys. If the table is being processed over 18 hours by a single SELECT statement it probably will make little difference. > Against my better judgement (the fact that a temporarily needed table > should be built as a "temp table", instead of a permanent one), I am > going to try creating the table each time in the application as a > permanent table, fill it, apply indexes, update statistics high/medium, > use the table in my batch process, and then drop it. I realize there > will be some initial overhead because of logging, but I'm willing to > make the sacrifice. > > Has anyone else ever ran into the need to update statistics on a large > temp table (in 4gl). If so, was this the solution you came up with? > > I appreciate any enlightenment... > > Bryan > > Sent via Deja.com http://www.deja.com/ > Before you buy.

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> "Art S. Kagel" wrote: <blockquote TYPE=CITE>bwhite@deroyal.com wrote: <br>> <br>> Hello wise ones <br>> <br>> I have a philosophical question... <br>> <br>> I've got an application that creates a relatively large temp table <br>> (850,000 rows). It is created "with no log" and in the tempdbspace etc. <br>> I suspect it is causing my application to slow down. The temp table is <br>> created, filled with data, and then the indexes are applied. Update <br>> statistics is then run (from the application, in this case a 4gl <br>> application) on the table. I tried changing the application to run <br>> update statistics medium, high etc, and this syntax is not supported <br>> from 4gl. (Only update statistics, which by default does it low). <p>You're just thinking too hard.&nbsp; Build the UPDATE STATISTICS <br>HIGH/MEDIUM/LOW statement, PREPARE it, and EXECUTE it. <p>> As important as it is to run a correct update statistics strategy (high <br>> on specific columns, medium on others), is it not equally important to <br>> have these same advantages on a temp table that is being read over the <br>> course of an 18 hour batch process? <p>Yes if the table and indexes are being hit frequently with multiple <br>queries and it has a substantial amount of data and several similar <br>index keys.&nbsp; If the table is being processed over 18 hours by a single <br>SELECT statement it probably will make little difference. <p>> Against my better judgement (the fact that a temporarily needed table <br>> should be built as a "temp table", instead of a permanent one), I am <br>> going to try creating the table each time in the application as a <br>> permanent table, fill it, apply indexes, update statistics high/medium, <br>> use the table in my batch process, and then drop it. I realize there <br>> will be some initial overhead because of logging, but I'm willing to <br>> make the sacrifice.</blockquote> If you are using 7.31 or 9.21 you could try this: <p>create table temp_tab ( ... ); <br>alter table temp_tab type (raw) -- Nonlogging table <br>insert into temp_tab select ... <br>alter table temp_tab type (standard) -- Standard table (logged) <br>create index ... <br>update statistics high for table temp_tab ... <br>&nbsp; <blockquote TYPE=CITE>&nbsp; <br>> <br>> Has anyone else ever ran into the need to update statistics on a large <br>> temp table (in 4gl). If so, was this the solution you came up with? <br>> <br>> I appreciate any enlightenment... <br>> <br>> Bryan <br>> <br>> Sent via Deja.com <a href="http://www.deja.com/">http://www.deja.com/</a>; <br>> Before you buy.</blockquote> Edgar <br>&nbsp;</html>