[Openroad-users] speed issue OpenROAD application

[Durwin Wright]

Hello Allan,

 

The procedure that you are using sounds like it is using singleton
INSERTS to move the data from the client to the sert.  The Ingres
protocol uses one message to send the data and one message to return the
response.  This is the real reason that you see your 0.5 sec per row
being inserted.  (In a local area network I would expect the times to be
between 0.05 sec and 0.1 sec however)

 

There are a couple of things that you can do to try to understand what
is going on.   It sounds like you are in a wide area network
environment.  Try using PING to get the roundtrip TCP/IP timinings.

 

The first thing that I would do is to create a terminal monitor script
that inserts rows that look like the ones that you are inserting from
the table field.  Use this script to obtain timings that should give you
a feeling for how the inserts perform in your environment.  

 

After you have created the table using the terminal monitor script, use
COPY TABLE INTO to unload the table into a file.  Then use COPY TABLE
FROM to see how long it takes to load the data from the client to the
server.

 

The COPY TABLE FROM Ingres protocol streams the data from the client to
the server and is almost as fast as a SELECT LOOP (or CURSOR PREFETCH)
protocol going from the server to the client.

 

If the times for COPY TABLE FROM are appreciably faster than Singleton
Inserts then it may make sense to marterialize the tablefield data into
a file, then use COPY TABLE FROM to move the data into the server.

 

Feel free to contact me directly if you have additional questions.

 

Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com
<mailto:Durwin.Wright at ingres.com>  | Ingres | 500 Arguello Street |
Suite 200 | Redwood City | CA | 94063 | USA
<http://maps.google.com/maps?q=500+arguello+street,+94063&ll=37.487297,-
122.233200&spn=0.004602,0.012771&t=k&hl=en>   +1 650-587-5523 | fax: +1
650-587-5550 

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Antill, Jim
Sent: Tuesday, October 02, 2007 1: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/b6ca5c95/attachment.html