Multiple inserts into JET / ORACLE

[Jo]

Hi,

In SQL Server I can batch many insert statements ie : Create one string that
looks thus :

szSQL = "insert into xxx (..., ..., ...) values (..., ..., ...)"
szSQL += "insert into xxx (..., ..., ...) values (..., ..., ...)"
szSQL += "insert into xxx (..., ..., ...) values (..., ..., ...)"

(obviously with correct values in)

and then run the command obj with (szSQL, oConn)

I am using the OleDBCommand / connection object.

Why can't I do this with JET or Oracle? Is there any way to batch to JET or
ORACLE?

PS : I have tried comma seperation and newlines but to no avail

Provider is MS JET 4.0 Ole DB Provider for Access
and MS Ole DB Provider for Oracle (using :Oracle Data Access Software for
Windows Release 9.2.0.4.0 November 2003)

thanks
Johan

 

[Nicholas Paldino [.NET/C# MVP]]

Johan,

As a general rule, you should not be constructing queries with embedded
values in them. You should be creating parameterized queries.

However, to address your concern, it's very possible that the JET and
Oracle providers do not support the batching of commands, and you would have
to send them one by one.

Hope this helps.

 

[Roy Fine]

Jo,

switch to the ODP.Net provider, then use the array bind - that will give you
a 5x improvement.

better yet, use client side arrays and server side stored procedures with
bulk collect - that will give you another 5x improvement.

here is a link that may help a bit:
http://www.onecustomer.com/TechArticles/TechArt17.html

regards
roy fine

 

[Jo]

I need to make this as fast possible and if you actually trace a
parameterised command you will see it actually passes more network traffic
than a standard insert statement. Some of the tables I work with have more
than 500 columns (not my design!!!! - Financial services industry) so the
size increases exponentially and hence the transfer is slower as there are
millions or rows.

thanks anyway

Johan,

As a general rule, you should not be constructing queries with embedded
values in them. You should be creating parameterized queries.

However, to address your concern, it's very possible that the JET and
Oracle providers do not support the batching of commands, and you would have
to send them one by one.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,

In SQL Server I can batch many insert statements ie : Create one string
that
looks thus :

szSQL = "insert into xxx (..., ..., ...) values (..., ..., ...)"
szSQL += "insert into xxx (..., ..., ...) values (..., ..., ...)"
szSQL += "insert into xxx (..., ..., ...) values (..., ..., ...)"

(obviously with correct values in)

and then run the command obj with (szSQL, oConn)

I am using the OleDBCommand / connection object.

Why can't I do this with JET or Oracle? Is there any way to batch to JET
or
ORACLE?

PS : I have tried comma seperation and newlines but to no avail

Provider is MS JET 4.0 Ole DB Provider for Access
and MS Ole DB Provider for Oracle (using :Oracle Data Access Software for
Windows Release 9.2.0.4.0 November 2003)

thanks
Johan

 

[Jo]

Read the article - very informative. However the code needs to be generic as
the app needs to connect to any datasource so I cannot switch to a specific
provider and handle some DB's differently as the client wants to connect to
"ANYTHING".
If there is a serious speed problem then I will start writing specific Data
layers for each provider that they wish to connect to...

thanks for your input.
J