RE: Help improve performance of RPG program - Long Post

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Alan,

First off, why the Select Distinct?

Distinct is generally a bad performer.  Not too mention, particularly in the 
case of exporting the 1.5 million records, you would (normally) want every 
record including duplicates if any; which the distinct would lose.

I'd do the headers like so

Set dummyVar = WriteIFS( stringToWrite )

(note if using embedded SQL, I don't know that set would work... I think 
instead you'd need VALUES INTO like so)

/exec SQL values WriteIFS( :stringToWrite ) INTO :dummyVar


Now as far as writing the detail, I think you are correct about the limitations 
of the prepared statement.  However, I believe they can be worked around using 
parameter markers ( and the SQLDA )

stm = 'values Select MAX(WriteIFS(' + wDataFormatString + ') from ' + 
wInputFileName + ') into ?'

/exec SQL prepare s from :stm


I'm not sure about this, but going from the following:
Table 71. Untyped Parameter Marker Usage
As a value in the INTO clause of the VALUES INTO statement

I think it would work.  Note the use of the MAX function.  I think it would be 
required in order to limit the results to a single row.  Without the MAX, the 
Select WriteIFS(<...>) would normal return a result set with the same number of 
rows as the input file.

I wouldn't use a global temp table.  You'd lose mucho performance writing 
temporary records into the DB vs. writing directly to the IFS.

Every write into a RDBMS table involves "considerable" overhead ( at least when 
compare to not writing into an actual table).


HTH,  

I'm off for a four day weekend. <grin>

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
> Sent: Thursday, May 26, 2005 3:00 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: Help improve performance of RPG program - Long Post
> 
> 
> Damn, I think you have got it Charles concerning the SET. 
> That would solve my having to use update but it would have to 
> be in a program which it is going to be anyway so the program 
> would have the following:
> 
> Open the stream file.
> /Exec SQL Set :DummyField = 
> OpenIFS('/home/yourdirectory/yourfile.txt')
> 
> Write the first header.
> /Exec SQL Set :DummyField = Select Distinct 
> WriteIFS(Build_String_Here) From Tables
> 
> Write the second header.
> /Exec SQL Set :DummyField = Select Distinct 
> WriteIFS(Build_String_Here) From Tables.
> 
> Write the 1.5 million.
> /Exec SQL Set :DummyField = Select Distinct 
> WriteIFS(Build_String_Here From 1.5Table
> 
> Close the stream file.
> /Exec SQL Set :DummyField = CloseIFS()
> 
> Shoot, I just spotted a problem. Everything is variable so 
> Writes would have to be prepared and I don't think I can use 
> a prepared statement with a SET or, at least, I don't know 
> how to do it. 
> 
> Maybe the solution is to use EXECUTE IMMEDIATE with an INSERT 
> into a temp table. You cannot use SELECT with EXECUTE 
> IMMEDIATE but I can do a "EXECUTE IMMEDIATE INSERT INTO 
> temp_table SELECT DISTINCT WRITEIFS(Build_String_Here) FROM 
> Tables" and declare the temp_table using DECLARE GLOBAL 
> TEMPORARY TABLE so we would end up with:
> 
> Build the dummy file
> /Exec SQL DECLARE GLOBAL TEMPORARY TABLE Temp_Table 
> (DummyField Char(1))
> 
> Open the stream file.
> /Exec SQL SET :DummyField = OpenIFS('filename ')
> 
> Write the first header
> StringToExecute = 'INSERT INTO TEMP_TABLE SELECT DISTINCT 
> WRITEIFS(' + BuildStringHere + ') FROM ' + Tables
> /Exec SQL EXECUTE IMMEDIATE :StringToExecute
> 
> So on and so forth. Global table disappears at the end. 
> 
> As to your question about the performance, I think that 
> anytime you can have a system level process do the work, it 
> is going to be faster and since the records are never coming 
> into the HLL program, that makes it fast. To my way of 
> thinking, SQL is always going to be faster than bringing 
> records into a HLL program. In other words, Set at a time operations. 
> 
> Important point here is that the records are never coming 
> into the HLL. All the processing is going on in SQL. SQL will 
> read a record from the data base, format the string, call the 
> UDF and back to get the next record. 
> 
> I think we are almost there. Thanks so much for the help.
>