Board index » off-topic » Re: URGENT Performance problem

2007-04-03 10:58:50 PM
off-topic11
Hi,
You are having a serious latency problem imo.
As far as I understand, you fetch record by record from the Oracle database
using a 'thin client' and posting into firebird from the thin client.
You are better off either to fetch batches of records at once, process those
with the alterations that are needed, and send them back to the firebird
based application server as a batch, or add some functionality in your
application servers that fetches directly from Oracle and inserts directly
into FB.
--
best regards
Kim Madsen
XXXX@XXXXX.COM
www.components4developers.com
www.myc4d.com - coming soon
The best components for the best developers
Application server enabling technology for developers
"Herbert Schlup" < XXXX@XXXXX.COM >skrev i en meddelelse

Hi all
I have an application which gets data from Oracle an insert it into Firebird using TSocketConnection and TClientDataset.
I have no Oracle Client installed on my local machine. Now I have a long download time. For about 40000 records, I use about 20 minutes.... downloading row by row.
How can I speed up this download time? What is the fastest way to get data from Oracle To Firebird without having an Oracle client on my machine?
Herbert
 

And how can I do this? Are there special components for Firebird?
Herbert
"Kim Madsen" < XXXX@XXXXX.COM >wrote:

{smallsort}

Hi,
I suppose you have 5 apps right now:
- An Oracle database
- An application server infront of the Oracle database based on
Midas/Datasnap.
- A Firebird database
- An application server infront of the Firebird database based on
Midas/Datasnap.
- A client application connecting to the two application servers and doing
the copying.
Supposing you want to stay in your current framework (Datasnap/Midas) I
would think about
adding functionality in the Firebird application server enabling it to
directly connect to the Oracle database, or alternatively (but slower)
connect via the Oracle application server as a Datasnap client.
The first suggestion eliminates 2 x latency and the 2nd suggestion only 1 x
latency as you still have one TClientDataset connection to the Oracle
application server/provider.
Loading 40000 records (without huge blobs though) from the Oracle database
into a memory storage shouldnt take more than 30 secs to a minute. However
TClientDataset is not too happy about largish data sizes. An alternative is
kbmMemTable which you can download from our site, but it wont be a direct
replacement for the TClientDataset as it doesnt understand the provider
functionality.
But I would think you would be able to speed up your current operations
within Datasnap considerably with the suggestions above without replacing
TClientDataset.
--
best regards
Kim Madsen
XXXX@XXXXX.COM
www.components4developers.com
www.myc4d.com - coming soon
The best components for the best developers
Application server enabling technology for developers
"Herbert.schlup" < XXXX@XXXXX.COM >skrev i en meddelelse

Hi Kim
Yes, it looks like this
Laptop with Firebird and Firebird application Server based on Midas(no Oracle Client)
Oracle Database on a central server
Application server with Oracle client based on Midas
On my Oracle Application I use TOracleDataSet. On the client side I use TSocketConnection to get data from the server and the IBO Components to insert the data into the local Firebird server.
How would you now do this in concrete? I can't find any component like BatchMove for BDE. Thank you for any hint
Herbert
 

Hi,
Ok
One thing more to check is if you start and commit a transaction for each
single record you insert into the fb database.
If so, then change to only committing after every say 200 records and after
the last.
--
best regards
Kim Madsen
XXXX@XXXXX.COM
www.components4developers.com
www.myc4d.com - coming soon
The best components for the best developers
Application server enabling technology for developers
"Herbert Schlup" < XXXX@XXXXX.COM >skrev i en meddelelse

Try to use the PacketRecords, setting it to 30, for example...
This will improve the performance...30 in 30 records will be fetched as is
needed!
"Herbert Schlup" < XXXX@XXXXX.COM >escreveu na mensagem

Fetching 40000 records 30 at a time is *slower* than fetching 40000
records all at once. You're presuming he doesn't want all of them, but
that's not in evidence from his post. Moreover, it comes with serious
side effects (i.e., makes your server stateful) which you haven't
mentioned.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
blogs.teamb.com/craigstuntz/category/21.aspx