[Openroad-users] speed issue OpenROAD application

[Bodo Bergmann]

Using a StringObject for collecting the data in memory and then write it
to a file and do a COPY using this file is a good idea,
so you don't have too many disk access operations and don't need a 3GL
Proc.
But be aware, that the StringObject operations ConcatVarchar() /
ConcatString() will become exponentially slower
if the size of the StringObjects exceeds 32KByte.
Therefore I recommend to use the AppendToFile() method for the
StringObject once its size exceeds around 30KByte
(could differ, depending on the size of data for each row), example:

	str.Value = '';
	str.WriteToFile(filename = mytempfilename);
	FOR i=1 TO tf.LastRow DO
	    // construct your row data for tf[i]
	    ... 
	    str.ConcatVarchar(text = currentrowdata);
	    IF str.Length > 30000 THEN
	        str.AppendToFile(filename = mytempfilename);
	        str.Value = '';
	    ENDIF;
	ENDFOR;
	str.AppendToFile(filename = mytempfilename);
	COPY TABLE mytablename ( ...) FROM :mytempfilename;
	COMMIT;

If the problem is really the network and not the sum of individual
insert statements
(did you do a performance test to insert 100 rows on the server?) then
you could also think about passing the whole tablefield array to an
OpenROAD Server application
using the "compressed" routing (available with OpenROAD 2006).
The server app could then use one of the different approaches:
- direct insert
- insert into global temporary table then either
    - "INSERT  INTO targettable() SELECT * FROM temptable"    or
    - "COPY temptable INTO tempfile" and "COPY targettable from
tempfile"
- use StringObject -> tempfile -> COPY targettable from tempfile (as
above)
 
 
Hope this helps,
Bodo.
 
Bodo Bergmann
Senior Software Engineer
OpenROAD Worldwide Development
Ingres Corp.
 

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Antill, Jim
Sent: Tuesday, October 02, 2007 10:32 AM
To: International OpenROAD Users
Subject: Re: [Openroad-users] speed issue OpenROAD application


Allan,
 
One way you might improve performance is to get your OpenROAD app to
create a temporary session table, insert rows into that, and then copy
the whole lot into the "real" table using one SQL statement when you're
finished. That might be quicker than doing loads of inserts into a
database table.
 
It might also be worth checking the structure of the table you're
inserting into. Some structures handle inserts differently than others.
Try inserting into a heap table.
 
You could also use a StringObject to write the data to a file. If you
need to put it on a remote machine from the Windows PC before loading
you could then FTP it over programmatically.
 
Hope that helps.
 
Regards,
Jim

	-----Original Message-----
	From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com]On Behalf Of Allan Biggs
	Sent: 02 October 2007 09:19
	To: openroad-users at peerlessit.com
	Subject: [Openroad-users] speed issue OpenROAD application
	
	
*************************************

This e-mail has been received by the Revenue Internet e-mail service.
(IP)

*************************************
	

	I have a speed issue made worse by a slowish connection to the
database server from the users PC. 
	
	We are unloading (sorry ABF terminology; obviously using a for
loop) a table field and on each cycle storing the values in a table.
This is taking 0.5 seconds per row. and there can be 50 to 100 rows in
the table field. 
	
	I am wondering how to speed this up and am considering writing
each row to a file (using a 3GL procedure) and then using  'copy table'
to load the table. 
	
	Can anyone think of a better way of approaching this 
	
	thanks 
	Allan
	This communication is for use by the intended recipient and
contains
	information that may be Privileged, confidential or copyrighted
under
	applicable law. If you are not the intended recipient, you are
hereby
	formally notified that any use, copying or distribution of this
e-mail,
	in whole or in part, is strictly prohibited. Please notify the
sender by
	return e-mail and delete this e-mail from your system. Unless
explicitly
	and conspicuously designated as "E-Contract Intended", this
e-mail does
	not constitute a contract offer, a contract amendment, or an
acceptance
	of a contract offer. This e-mail does not constitute a consent
to the
	use of sender's contact information for direct marketing
purposes or for
	transfers of data to third parties.
	
	Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese
Korean
	
	           http://www.DuPont.com/corp/email_disclaimer.html

************************

This message has been delivered to the Internet by the Revenue Internet
e-mail service (OP)

*************************
	
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20071002/6b707f81/attachment.html