Re: Update Statistics & temp tables

From: bwhite@deroyal.com > >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). UPDATE STATISTICS on it's own should be fine, unless the data is veryskewed. Have you considered using PSORT_DBTEMP, PSORT_NPROCS and PDQPRIORITY for this batch process? >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? LET updatestatsstr = "UPDATE STATISTICS HIGH FOR TABLE ", temptabname CLIPPED PREPARE exupdstats FROM updatestatsstr EXECUTE exupdstats ? Just a guess. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com