Forcing ODBC-Autocommit

[Marcel Czerny]

Hi,

I've got some troubles with exporting data base entries from
an "Access"-table (ver. 10) into an "Oracle"-table (ver. 9.2)
using the "Oracle-ODBC-driver".
At first I linked the corresponding tables in "Access" (via the odbc
driver) so
I can use the "Oracle"-table as a native "Access"-table.

tOracle - oracle table dummy
tAccess - access table dummy

Then I start the following sql command "INSERT INTO tOracle SELECT *
FROM tAccess;"

Now I've watched this using SQL-Trace.

- autocommit off - SQLSetConnectOp tion(SQL_AUTOCO MMIT, 0)
for each data set:
- bind parameter for each field - SQLBindParamete r
- execute the sql command - SQLExecDirect(" INSERT INTO "TORACLE"
(fields...) VALUES (?,?,...)")

after it's finished with executing the command this will appear
- Commit - - SQLTransact(SQL _COMMIT)
- switch autocommit on again - SQLSetConnectOp tion(SQL_AUTOCO MMIT, 1)


So here is my question:

If I try to insert more than a million data sets into the oracle-table
without any
commit, the undo-tables space grows and the destination table stays
empty until commit is called.
That's pretty slow (much too slow).


Is there any possibility to:
- let the autocommit enabled during the sql command is executed?
or
- how can I enable a commit after 100 data set or so (without using
any vbasic scripts)

where can I enable one of these options?




thanks a lot,

greets,

Marcel Czerny

[rude person]

Re: Forcing ODBC-Autocommit

On 15 Jul 2005 02:54:50 -0700, "Marcel Czerny"
<marcel.czerny@ list.smwa.sachs en.de> wrote:
[color=blue]
>Hi,
>
>I've got some troubles with exporting data base entries from
>an "Access"-table (ver. 10) into an "Oracle"-table (ver. 9.2)
>using the "Oracle-ODBC-driver".
>At first I linked the corresponding tables in "Access" (via the odbc
>driver) so
>I can use the "Oracle"-table as a native "Access"-table.
>
>tOracle - oracle table dummy
>tAccess - access table dummy
>
>Then I start the following sql command "INSERT INTO tOracle SELECT *
>FROM tAccess;"
>
>Now I've watched this using SQL-Trace.
>
>- autocommit off - SQLSetConnectOp tion(SQL_AUTOCO MMIT, 0)
>for each data set:
> - bind parameter for each field - SQLBindParamete r
> - execute the sql command - SQLExecDirect(" INSERT INTO "TORACLE"
>(fields...) VALUES (?,?,...)")
>
>after it's finished with executing the command this will appear
> - Commit - - SQLTransact(SQL _COMMIT)
> - switch autocommit on again - SQLSetConnectOp tion(SQL_AUTOCO MMIT, 1)
>
>
>So here is my question:
>
>If I try to insert more than a million data sets into the oracle-table
>without any
>commit, the undo-tables space grows and the destination table stays
>empty until commit is called.
>That's pretty slow (much too slow).
>
>
>Is there any possibility to:
> - let the autocommit enabled during the sql command is executed?
> or
> - how can I enable a commit after 100 data set or so (without using
>any vbasic scripts)
>
> where can I enable one of these options?
>
>
>
>
> thanks a lot,
>
> greets,
>
>Marcel Czerny
>[/color]

You could order the data by a field with strictly increasing values
and run a series of TOP 100 queries (or maybe 1000 or 10,000 if you
have a million rows) and keep track of where you have got to.

Is this in effect a new table in Oracle each time or are you adding to
a bigger table, or even worse deleting a million items and then adding
a million new ones? IE have you speed problems in addition to your
transaction log size?
David