Batch Update Across Tables

Batch Update Across Tables

Postby Justin_H » Thu 22 Apr 2010 06:23

For a current project I need to execute 5 simple update statements "update table_x set a='b' where b='c'" or similar where each statement is for a different table. The work is performance sensitive so I would really like if I could put all 5 updates in a single batch and send them to oracle (single round trip) is this possible? If so could someone give a quick example or point me in the right direction?

Thanks,
Justin
Justin_H
 
Posts: 2
Joined: Wed 21 Apr 2010 22:51

Postby Shalex » Thu 22 Apr 2010 10:35

Please take advantage of using the OracleScript class to execute several SQL statements as one for the case when you are working with different tables.

If you load data to a single table, dotConnect for Oracle provides three possible ways to load data: conventional mode, DML array method, and direct path loading.
The conventional path loader essentially loads data using standard INSERT statements. Here is a sample of such insert.
DML array (ExecuteArray method of OracleCommand) allows to execute several SQL statements at once. The main advantage of using this method is that only one round trip to the server is used to execute several queries, which speeds up queries execution compared to consecutive invocation.
The direct path loader (OracleLoader) bypasses much of the logic involved in the SQL command processing, and loads directly into the Oracle data files. For more information about the direct path load engine, please refer to the Oracle documentation.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Postby Justin_H » Mon 26 Apr 2010 03:34

Thanks Shalex,

Quick Followup: why doesn't the oracle script class provide async methods like the oracle command class etc do (i.e. BeginExecute)? I've been rewriting a data layer after switching from the MS oracle provider to the devart version and was hoping to also introduce a set of async methods...
Justin_H
 
Posts: 2
Joined: Wed 21 Apr 2010 22:51

Postby Shalex » Mon 26 Apr 2010 10:42

We recommend you to use the OracleScript component in parallel thread (instead of using it via asynchronous methods).
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle