Re: Performance

> Subject: Re: Performance > Date: 27 Mar 1995 01:15:41 -0600 > Reply-To: john@gagme.wwa.com (pinoy_ako) > Organization: WorldWide Access - Chicagoland Internet Services > > In article <3kt1jh$hui@dodge.eng.sc.rolm.com>, > Peter Levine <peterl@minerva.robadome.com> wrote: > >Thanks Helen and Jack for the quick response. > > > >I restructured the query and have considerably > >improved its performance. Query time has gone from over 8 minutes to less > >than 8 seconds. > > > ... > > > >To improve performance I broke the original query into 4 separate > >queries using the technique of substituting sorting in temporary tables > >to avoid nonsequential disk access. This technique is described in > >Chapter 4 of the INFORMIX "Guide to SQL Tutorial" - Using a Temporary > >Table to Speed Queries. (Lots of other good stuff in this chapter too.) > > > This is good. I've read about creating temporary tables and use them > for faster queries. But what about if two or more persons are using > the same data-entry screen? Will making a 'fixed' temporary table > name let one user succeed and the other one fail with a > 'table already exists' error? > -- > - J - Each separate user process will "own" a separate set of temp tables, so the re-use of the name should not cause a problem. If you would prefer to use permanent scratch tables, then you might prefer the following technique: 1. Define your scratch table(s) the same as the temp table(s) to be replaced, EXCEPT include also an additional 'user_id' column which is either char(8) or integer, depending on which id type you use below. 2. Modify the selects into your scratch table to include as one column either the SQL USER function (a char(8) value on most Unix systems) or the process-id [pid] (an integer). Depending on your application language, ESQL/C or 4GL, it may be easy or difficult to get the pid of the current process. Which ever type of id you use, the id will separate one user's data from another's. 3. Include the user_id column in the subsequent queries. 4. Remember to delete all of the rows for the current user_id from the scratch table(s) when your application is done with them. Probably a good idea to do it at the beginning as well, in case you retry the query after interrupting or aborting it. USER is easier to use, but a single user running two queries at the same time, or multiple users using a generic login, will get garbled results. Using the pid avoids this problem, but is a little harder to do. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+