ODAC Insert speed

h.hasenack · Post by **h.hasenack** » Thu 22 Apr 2010 11:51

Hi

Currently still fighting with oracle to get my records uploaded as quickly as possible.

Since my DBMover is somewhat generic , I use a TCLientDataset and ODAC to as middleware to update my data to an oracle 10g server.
Server is 6GB 64 bit machine, plenty diskspace, 1GB lan. CLient is 64 bit machine too.

Somehow it is hard to upload large batches of data, it seems like tmemDataset is -after actually performing the insert- taking a lot of time to locate the record in it buffer to update data. I did set the keyfields property as well as the indexfieldnames property to a unique index.

I have some output from my DBMover app, which is basically only used to move data from one DB to another. After that, the resulting DB is to be used from my main APp LCC-AMQM.

(Using RAD studio 2007)

Also I have output from AQTIme6 which I generated from the DBMover application.

Obviously there's no way to add attachments to this message, so I'll send it to the support address of devart...

Maybe devart can shed some light on why updating data takes so much time.

Regards - Hans

bork · Post by **bork** » Fri 23 Apr 2010 13:39

Hello

The point is that the TClientDataset usage when working with large amounts of data leads to significant performance loss. If you need to load large amounts of data, it's better to use ordinary TOraQuery with UniDirectional = True and to generate queries for inserting, updating, and deleting records manually.

h.hasenack · Post by **h.hasenack** » Mon 26 Apr 2010 06:16

Yes, I found it out too. Especially TCLientDataset Locate is very inefficient. (I managed to fix that by overriding it).
The reason for using TClientDataset is because it alows the 'Clonecursor' functionality. I need that because opening multiple TOraTable instances on the same table takes too much memory, and a change made to one instance wont automagicvally appear in the other TOraTable instance.

What really would help if there's a Clonecursor-like functionality in eg. TMemDataset or tVirtualtable that would allow me to stop using TClientDataset in the first place. If it's there please accept my humle apologies, and provide a keyword into the help.

Regards - Hans
--

PS I know the "right way" to go is to redesign my entire application round oracle. This wont happen since only one of our customers demands the app uses oracle als db server engine. The app is entirely buoit round the TTable/TDataset like functionality of old-school delphi.

bork · Post by **bork** » Tue 27 Apr 2010 12:40

TClientDataSet works slowly if you load a large amount of data to it. As you wrote, you are using CloneСursor to share these data for different forms and other visual components. But I suspect that the data are not used entirely in any place. To improve performance it is better to load the data when it is needed (when user requested it), and to load only the necessary data, but not all the table data. Most modern applications, that process large volumes of data work in that way. It allows to speed up the application work regardless of the database used.

h.hasenack · Post by **h.hasenack** » Tue 27 Apr 2010 13:05

Indeed, not all data is used in any place. Even not in my app

BUT, as stated before, we usually use the Nexus DB database engine which allows us to navigate the DB tables without actually copying all data to the client. The cursor resides on the server side.

We just have to deal with this customer that insists on using oracle rather than NexusDB as database server engine.

As my app is entirely built around this concept, I am looking for a drop-in replacement of this functionality for use with oracle. And TOratablke comes quite near to it.

I came around to using TCLientDataset because multiple TOratables dont share the downloaded data, and simply navigating in a TOraTable wont show changes made into another instance of TOraTable.
Apart from that, each TOraTable instance (even to the same table) has its own private copy of all the table's data, and this causes my memory to run out...

Thx for bearing with me.

bork · Post by **bork** » Wed 28 Apr 2010 09:13

TOraQuery and TOraTable have such feature as server cursors that navigate at the server without copying all data to the client.

You can use a cursor in the unidirectional mode. In this case you should set the UniDirectional property to True. Also you can use a cursor in the bidirectional mode (available since Oracle 9 only). In this case you should set the Options.ScrollableCursor property to True.

But you should remember that unidirectional and bidirectional mode of the dataset has a lot of restrictions.

You can find more detailed information about this in the ODAC documentation.

h.hasenack · Post by **h.hasenack** » Wed 28 Apr 2010 09:26

I like the scrollable cursor feature, but as you know it turns the dataset into readonly mode.

I believe I can't fix the latter using some TOraUpdateObject, the dataset still remains readonly for VCL controls.

Any tips regarding working around this?

Regards - Hans

bork · Post by **bork** » Thu 29 Apr 2010 11:18

Yes, you cannot edit dataset in the scrollable mode. But you can get selected record into separate dataset:

Code: Select all

  select * from table TABLE_NAME where ID=...

Edit this record in the separate dataset and refresh the main dataset after post data to database.

h.hasenack · Post by **h.hasenack** » Thu 29 Apr 2010 11:45

bork wrote:
Edit this record in the separate dataset and refresh the main dataset after post data to database.

So why don't you put this functionality in the TOraTable ? It seems to me that's just the right place to implement this! And it keeps my App transparent and logical. (from a Delphi point of view)

Regards

bork · Post by **bork** » Thu 29 Apr 2010 12:47

It is not easy because it depends on Oracle scrollable cursors implementation. We will investigate the possibility of adding this feature in one of the next builds/versions of ODAC.