BorlandTalk.com Forum Index BorlandTalk.com
Borland discussion newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

TADOQuery - Multiple inserts - Oracle

 
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO)
View previous topic :: View next topic  
Author Message
danilo z.
Guest
PostPosted: Fri May 04, 2007 7:38 pm    Post subject: TADOQuery - Multiple inserts - Oracle Reply with quote


Hi All,

How i make multiples inserts inside a TADOQuery object in Oracle?
i've tried

INSERT INTO <TABLE> (<FIELDS>) VALUES (<LIST>);
INSERT INTO <TABLE> (<FIELDS>) VALUES (<LIST>);
INSERT INTO <TABLE> (<FIELDS>) VALUES (<LIST>);
INSERT INTO <TABLE> (<FIELDS>) VALUES (<LIST>);
INSERT INTO <TABLE> (<FIELDS>) VALUES (<LIST>);
..
..
..

it didnt worked....
Back to top
Vitali Kalinin
Guest
PostPosted: Fri May 04, 2007 9:20 pm    Post subject: Re: TADOQuery - Multiple inserts - Oracle Reply with quote


Surround it with Begin End;
Back to top
danilo z.
Guest
PostPosted: Fri May 04, 2007 10:21 pm    Post subject: Re: TADOQuery - Multiple inserts - Oracle Reply with quote


"Vitali Kalinin" <vitkalinin (AT) yahoo (DOT) com> wrote:
Quote:
Surround it with Begin End;



Ok, it worked. I am doing a test case with 12.000 records using prepared queries and non-prepared batch queries (100 records each). Look at these results:

SQL Server, batch 12.000 records (100 records on each batch):

30 seconds

Oracle, batch 12.000 records (100 records on each batch):

10 minutes and 36 seconds (!!!!!)

Same database/table
Back to top
Dmitry Arefiev
Guest
PostPosted: Sat May 05, 2007 1:28 am    Post subject: Re: TADOQuery - Multiple inserts - Oracle Reply with quote
Quote:
Look at these results:

Both DBMS's have API's to send big sets of the data to the
DB in optimal way. But ADO does not allow you to use them.
Your result for Oracle just shows, that you are using far from
optimal API to upload data.

If possible, then consider to switch from ADO (even for MSSQL)
to other data access libraries.

Regards,
Dmitry

--
Dmitry Arefiev - www.da-soft.com
AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase
ASA, DbExpress, ODBC freeware data access framework
ThinDAC - mtier data access engine
Back to top
danilo z.
Guest
PostPosted: Sat May 05, 2007 1:30 am    Post subject: Re: TADOQuery - Multiple inserts - Oracle Reply with quote
"Dmitry Arefiev" <darefiev@da-soft.com> wrote:
Quote:
Look at these results:

Both DBMS's have API's to send big sets of the data to the
DB in optimal way. But ADO does not allow you to use them.
Your result for Oracle just shows, that you are using far from
optimal API to upload data.

If possible, then consider to switch from ADO (even for MSSQL)
to other data access libraries.

Regards,
Dmitry

--
Dmitry Arefiev - www.da-soft.com
AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase
ASA, DbExpress, ODBC freeware data access framework
ThinDAC - mtier data access engine


Thanks for the advice. Do you have any engine suggestions?
Back to top
Display posts from previous:   
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.