From: GOLD@ILP.MIT.EDU (Mark D. Gold)
Subject: Re: Help - ABF/4GL questions - summary
Organization: National Radio Astronomy Observatory
Date: Mon, 11 Jan 1993 04:41:23 GMT

I would like to thank everyone who responded to my question regarding 
the use of temporary tables and repeated inserts in ABF/4GL and 
handling Ctrl-Y and Ctrl-C interrupts.

>>Subj:	Re: Help - ABF/4GL questions.
>>
>>I'm having several problems writing 4GL procedures.  I was hoping 
>>that an INGRES expert could point me in the right direction.
>>
>>1.  One 4GL procedure does the following work:
>>
>>	a.  create temporary table x
>>
>>	b.  3 large, complex inserts (with selects) into table x
>>
>>	c.  3 large, complex select loops which each have
>>            REPEATED inserts into table x within the loops
>>            (the select loops also call C procedures)
>>
>>      d.  create array z, in which is stored a large list of id#s
>>
>>	e.  unload array z statement with 7 REPEATED insert
>>            statements into table x within the unload statement
>>
>>      f.  modify table x to heapsort on....
>>
>>	g.  call report passing temporary table.
>>
>>
>>    If I use REPEATED INSERTS throughout the 4GL procedure, the 
>>procedure runs approximately 4 times faster than when I just use 
>>regular INSERTS (i.e., 15 minutes : 1 hour).  However, I need to be 
>>able to define the name of the temporary table ("table x" above) at 
>>the start of the 4GL procedure based on the username and sessionid 
>>using dbmsinfo.  This means (I assume) that I would have to store the 
>>temporary table name in a variable at the start of the 4GL procedure. 
>>Unfortunately, I can't use a REPEATED INSERT into a tablename that is 
>>stored in a variable, even though the variable will not be changing 
>>thoughout the 4GL procedure.
>>
>>This limitation means that we have to either (1) live with an 
>>extremely slow 4GL procedure, or (2) hard-code the temporary table 
>>name into the 4GL procedure, which means that each username will have a 
>>copy of that temporary table AND only one person at a time logged 
>>onto the same account will be able to use the temporary table.
>>Option #1 is unacceptable for us.  


Three items that I neglected to discuss in detail were:

1.  We need the ability to set up ONE account per company dialing-in 
    to our database.  Since any number of users can be using that one
    account at the same time, we needed to find a way that each of
    those users could create a temporary table without conflicting
    with other users logged in to the same account.

    In addition, creating an indexed column in this temporary table
    signifying the user's session_id (i.e., dbmsinfo('_bintim'))
    would have caused two problems: a) slowing down inserts on a 
    table that could get as big a 500,000 1800-byte rows; and
    b) some users waiting while one user sorts the table for reports.

2.  The select loops that I mention in "c." (above) which calls C-procedures
    simply make changes to non-keyed data from the select statements.
    Unfortunately, it is not practical (if possible) to replace this
    existing C-procedure with 4GL code.

3.  The 4GL procedure took a list of faculty ID#'s (parents) and gathered
    data regarding faculty and their projects (children) and project
    abstract lines, project keywords (children of projects) into a temporary
    table.  In item "d." above, I was putting the project ID#'s into
    an array and then looping through that array to obtain the children
    of the projects (keywords, abstract lines, etc., etc.)


I received some excellant ideas from "the net".  Since I had to use a 
unique temporary table name, I either had to put it in a variable 
(i.e., INSERT INTO :temp_table) or use the execellant suggestion to 
use the ReportWriter to create the SQL statements that I needed with 
the REPEATED INSERTs and hard-coded temporary table name.

>In other words, create an SQL file WHILE your application is
>running. (use .RW, or operating system text-manipulation
>facilities)  This file would include statements to create
>your temporary table with the parameters you specify
>(including a table name generated at run time).

I decided against the suggestion in this case for several reasons, 
but mainly because 1) I needed to use not only SQL statements, but 
4GL statements; and 2) the time it would take to generate a 3,000 
line file of SQL statements would be troublesome in cases where the 
user is just reporting on one or two faculty members.

What I ended up doing was abandoning the REPEATED INSERTS in my 
select loops.  I also eliminated the need for the REPEATED INSERTS
when I unloaded the array I created in "d." (see "d." in original
problem description)by creating INSERTs with SUBSELECT that each
retrieved faculty data, project data (children), and one group of 
children of projects (i.e., project co-investigators).  The SELECT 
statements were rather involved, but it seemed to work....

Unfortunately, some of these INSERTs with SUBSELECT inserted so many
rows into my temporary table, that my transaction log would fill up 
and the program would bomb.  For example, one of the INSERTs with 
SUBSELECT could fill up as much as 1/3-gigabyte in the transaction 
log, even though the table being inserted into was modified to cheap.

So, in the end, I decided to keep the transactions smaller by putting 
the whole procedure in a WHILE loop and running it on up to 100 faculty 
members at a time.

I have to admit that I am a little frustrated with the ReportWriter 
and 4GL, and plan to write any other complex reports in C.  But I am 
very grateful to all the respondants and plan to use the suggestions 
wherever possible.


As far as the questions I had with Ctrl-C, Ctrl-Y, and F6 (VAX) 
interrupts, I didn't receive any interrupt handlers.  I did get the 
suggestion to run a clean-up procedure on a nightly basis.  Several 
people suggested that I don't let Users use Ctrl-C or Ctrl-Y.  It was 
also suggested that I could add interrupt handlers to a 3gl program.

			- Mark
--------------------------------------------------------------
Mark D. Gold             Massachusetts Institute of Technology
gold@ilp.mit.edu         Industrial Liaison Program
(617) 253-0430           Cambridge, Massachusetts  02139
Newsgroups: comp.databases.ingres