| Simple inserts Slow (merged 6) [message #527429] |
Tue, 18 October 2011 02:20  |
zulfiqaralimemon
Messages: 10
Registered: April 2010
Location: Pakistan
|
Junior Member |
|
|
I am inserting 200,000 records from excel file using vb application. It takes 45 minutes to load the data. How can improve the loading time. My database is in no archive mode. Is that due to redolog file size?
Regards,
Zulfiqar
|
|
|
|
|
|
| Re: Simple inserts Slow [message #527445 is a reply to message #527441] |
Tue, 18 October 2011 02:58  |
zulfiqaralimemon
Messages: 10
Registered: April 2010
Location: Pakistan
|
Junior Member |
|
|
This is how i am inserting fixed columns from excel file.
Insert into table abc select col1,col2,.....col10 from dual union all select col1,col2,.....col10 from dual
Zulfiqar
|
|
|
|
| Re: Simple inserts Slow [message #528673 is a reply to message #527445] |
Wed, 26 October 2011 06:23 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I am guessing that you are doing ODBC from EXCEL? or that you are using an EXCEL extension to work with the database? In any event, the issues are likely the same as always:
Quote:1) row at a time is slow at a time.
2) your insert is wicked big? Contains 200,000 select statements and union all? WOW!
You solution is likely to use BULK INSERT. I postulate that you have two options:
Quote:1) change the way you are doing the insert in EXCEL so that you have one insert statement and you use an ARRAY SIZE setting of something like 1000.
2) dump the data to a text file and use SQLOADER which will load 200,000 rows in just a few seconds.
The fact that it takes 45 minutes to load a pitifully small number of rows should clue you that your method inside EXCEL is horribly flawed.
Good luck, Kevin
[Updated on: Wed, 26 October 2011 07:51] by Moderator Report message to a moderator |
|
|
|
| Re: Simple inserts Slow [message #529036 is a reply to message #528673] |
Fri, 28 October 2011 07:18 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
If you can issue the sql statements, and it's not being done vai some vb mapping object, then an additional option would be to use the append hint.
i.e.
insert /*+ APPEND */ into table_name values (x,y,z)
That should speed up the inserts quite dramatically.
|
|
|
|
|
|
| Re: Simple inserts Slow [message #531516 is a reply to message #531438] |
Wed, 16 November 2011 06:58 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
so you have a home grown VB program and it reads the Excel sheet and loads to Oracle?
If so the have the developer do #1. Let the change their VB so that it is doing some form of bulk load. I would presume that the VB program has an array size parameter?
OR
Do #2. Toss away the VB program and have this developer write scripts to dump the Excel file to an ASCII file and use SQLLOADER to load it.
Kevin
[Updated on: Wed, 16 November 2011 07:00] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
