473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Fastest way to insert several 1000 transactions into oracle?

i am looking for the fastest way to insert several 1000
trx int oracle. is there a faster way, then using a
DataAdapter? is there a possibilty to execute a bulk load
(or batchwise insert with transactionsize)? any
suggestions? thanks a lot!!

chris
Nov 13 '05 #1
5 6591
On Fri, 18 Jul 2003 05:59:36 -0700, "Chris" <ch*****@hotmail.com>
wrote:
i am looking for the fastest way to insert several 1000
trx int oracle. is there a faster way, then using a
DataAdapter? is there a possibilty to execute a bulk load
(or batchwise insert with transactionsize)? any
suggestions? thanks a lot!!

chris


If these are individual txns, my suggestion would be to use the
threadpool and run txns simultaneously. I do this quite often. The
threadpool gives you up to 25 threads per CPU so you can get the job
done much faster.

Jonathan Schafer
Nov 13 '05 #2
On 18 Jul 2003 16:33:12 -0700, m@exquisitor.com (Lucean Morningside)
wrote:
Jonathan Schafer <jschafer@*NOSPAM*brierley.a.b.c.com> wrote in message news:<2p********************************@4ax.com>. ..
On Fri, 18 Jul 2003 05:59:36 -0700, "Chris" <ch*****@hotmail.com>
wrote:
>i am looking for the fastest way to insert several 1000
>trx int oracle. is there a faster way, then using a
>DataAdapter? is there a possibilty to execute a bulk load
>(or batchwise insert with transactionsize)? any
>suggestions? thanks a lot!!
>
>chris


If these are individual txns, my suggestion would be to use the
threadpool and run txns simultaneously. I do this quite often. The
threadpool gives you up to 25 threads per CPU so you can get the job
done much faster.

Jonathan Schafer


I don't know about thread pooling, but I wouldn't use a DataAdapter object
when performance is an issue. You should get better performance by using
the Command object's ExecuteNonQuery with parameterized queries and stored
procedures.

-LM


Perhaps I didn't make that part clear. That is exactly what I am
talking about. Using SqlCommand (or OleDbCommand) ExecuteNonQuery to
do the insert. By using the ThreadPool (QueueUserWorkItem), you can
execute the Insert query simultaneously in approximately 25 threads
per cpu.

Jonathan Schafer
Nov 15 '05 #3
Jonathan thank you very much for your suggestion. it is a
cool idea. i will try to implement and test ist asap.
thanks!

chris
-----Original Message-----
On 18 Jul 2003 16:33:12 -0700, m@exquisitor.com (Lucean Morningside)wrote:
Jonathan Schafer <jschafer@*NOSPAM*brierley.a.b.c.com> wrote in message
news:<2p********************************@4ax.com>. ..
On Fri, 18 Jul 2003 05:59:36 -0700, "Chris" <ch*****@hotmail.com> wrote:

>i am looking for the fastest way to insert several 1000 >trx int oracle. is there a faster way, then using a
>DataAdapter? is there a possibilty to execute a bulk load >(or batchwise insert with transactionsize)? any
>suggestions? thanks a lot!!
>
>chris

If these are individual txns, my suggestion would be to use the threadpool and run txns simultaneously. I do this quite often. The threadpool gives you up to 25 threads per CPU so you can get the job done much faster.

Jonathan Schafer
I don't know about thread pooling, but I wouldn't use a DataAdapter objectwhen performance is an issue. You should get better performance by usingthe Command object's ExecuteNonQuery with parameterized queries and storedprocedures.

-LM


Perhaps I didn't make that part clear. That is exactly

what I amtalking about. Using SqlCommand (or OleDbCommand) ExecuteNonQuery todo the insert. By using the ThreadPool (QueueUserWorkItem), you canexecute the Insert query simultaneously in approximately 25 threadsper cpu.

Jonathan Schafer
.

Nov 15 '05 #4
Jonathan Schafer <jschafer@*NOSPAM*brierley.a.b.c.com> wrote in message news:<sb********************************@4ax.com>. ..
Perhaps I didn't make that part clear. That is exactly what I am
talking about. Using SqlCommand (or OleDbCommand) ExecuteNonQuery to
do the insert. By using the ThreadPool (QueueUserWorkItem), you can
execute the Insert query simultaneously in approximately 25 threads
per cpu.

Jonathan Schafer


I'm sorry, and I stand corrected.

-LM
Nov 15 '05 #5
On 19 Jul 2003 13:34:47 -0700, m@exquisitor.com (Lucean Morningside)
wrote:
Jonathan Schafer <jschafer@*NOSPAM*brierley.a.b.c.com> wrote in message news:<sb********************************@4ax.com>. ..
Perhaps I didn't make that part clear. That is exactly what I am
talking about. Using SqlCommand (or OleDbCommand) ExecuteNonQuery to
do the insert. By using the ThreadPool (QueueUserWorkItem), you can
execute the Insert query simultaneously in approximately 25 threads
per cpu.

Jonathan Schafer


I'm sorry, and I stand corrected.

-LM


No need to be sorry. I was unclear in my response and so the message
didn't provide the help that it should have! Thanks for pointing it
out.

Jonathan Schafer
Nov 15 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Red_Viking | last post by:
Hello, in Oracle Enterprise Manger Console i declared an array-type. Then i created a table which uses this type. How can i now insert an array to this table? Thanks in advance!
11
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to...
6
by: Hennie de Nooijer | last post by:
Hi, Currently we're a building a metadatadriven datawarehouse in SQL Server 2000. We're investigating the possibility of the updating tables with enormeous number of updates and insert and the...
3
by: Juan | last post by:
I need to do some processing from my app after a row is inserted in an Orcale db, how can i do that? Thanks, Juan.
1
by: kumarmanoj_pat | last post by:
hi, i am trying to insert a picture in my database through SQL. i have already tried but could not get success. if any one know about this then please helpnme.
1
by: siujean | last post by:
Hi All, I met a problem when insert over 1000 records to db2 via servlet, it show me the error message is "DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 68 DB2ConnectionCorrelator:...
3
by: raveesh | last post by:
When i try to insert a date field to Oracle DB through INSERT command, i am getting an error "Invalid Month". I am using ASP.NET 2 (VB). Please advise how to tackle this issue.
1
by: charlesa | last post by:
Hi all I have a table for job. In that table having some data. But I want insert one more row. Job_id job_title min_sal max_sal 10 cse 1000 ...
2
Inbaraj
by: Inbaraj | last post by:
HI.. I want to perform Bulk Insert of data using C# and oracle can any one help me how to perform this operation.... Plz help me with sample code..... with reg Inba
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.