Andreas Mosmann wrote:
> Hi ng,
>
> At first I have to explain the situation a bit:
>
> There is an Oracle9.2i DB, that contains information about trees on
> roads, I call it now MyDB.
> There is another Oracle9.2i DB, that contains information about roads
> and objects on it. (It was a hard job to get permission to select data
> from there). In later Versions there will be implemented an official
> web- interface with XML- Datasets, that I have to use as even as it exists.
> I will call this DB OtherDB. I definitely will not get any help from its
> developers, they postpone me to the next version.
> [1]
>
> On MyDB works a Windows-Program written in Delphi6 by ODAC- direct- ACCESS
> (http://www.crlab.com/)
>
> In an overnight process I want/have to:
> 1. Connect to MyDB
> 2. kill Data in old "CopyTable"
>
> 3. connect to OtherDB
> 4. copy all data from a view in OtherDB into MyDB -> CopyTable
> 5. disconnect from OtherDB
>
> 6. something to be done in MyDB
>
> My questions relate to steps 3-5. (These steps have to be changed with
> next version of OtherDB, but I can not wait)
> For this OvernightProcess I wrote another D6- App.
> The amount of records is about 500 000. At the moment only these steps
> take about an hour. This seems to be very long for me and is fairly in
> time. I know that you probably cant tell my where I lose my time, but I
> hope you can tell me where and how to search! (It is easy to pick the
> times from each procedure in the D6-App, but so I only can decide which
> of the DBs takes how much time. What it is doing with this I can not
> find out that way.
>
> Related part of Application (Delphi- stuff, pseudo):
>
> prepare a query on OtherDB (TOraQuery, seems to be something like an
> open cursor)
> connect to a table in MyDB (TOraTable)
> while not OtherDB.OraQuery.eof do begin
> MyDB.OraTable.Insert;
> CopyData(OtherDB.OraQuery.ActiveRecord -> MyDB.OraTable.ActiveRecord);
> MyDB.OraTable.Post;
> OtherDB.OraQuery.NextRecord;
> end;
>
> Questions:
> 1.) 500 000 rows in 1 hour, is this too slow or is not
> 2.) in Oracle there it is not 1 long operation but 500 000 small. How to
> find out what happens
> 3.) Can I find out on MyDB, what takes time? (Writing redologs, undo,
> indexes etc.)
>
> Many thanks
> (and a happy new year to all)
>
> Andreas Mosmann
>
> [1]
> MyDB ran on a 4 processors- server
> OtherDB ran on a 2 processor- server
> D6-App ran on a 2GHz- Workstation that was not really busy with this (10%?)
>
>
I'm not in Delphi, but here's 2 cents worth:
- Is there a way to bypass your Delphi program, and
create a database link, and just do
'create table A as select * from table A_at_OtherDB;'?!?
It surely would be a heck of a lot faster!
Drop the table after you truncated it, if it's large
(500k records is *not* considered large).
- What type of connection do you have between MyDB and
OtherDB? A 33k6 kbps modem would explain a lot about
your 1 hour processing ;)
- Web interfaces with XML will probably as slow as
you are now. If you have a problem with time already,
be prepared.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Sat Jan 07 2006 - 08:10:23 CST
