Home » SQL & PL/SQL » SQL & PL/SQL » Test-data insert execution time
Test-data insert execution time [message #325427] Fri, 06 June 2008 03:23 Go to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
Hi!

I am using large SQL insert-script to insert data for my application into oracle 10 database. I use TOAD.
My script consists of about 22000 insert statements. The problem is that it takes about 15 minutes. Sometimes TOAD even crashes on half way. When we used SQL Server 2005 before migrating it took about 30 seconds. I think TOAD inserts this script somehow row-by-row.

Do I have to make any changes in my sql-script or should I re-configure TOAD ? Or is there any other easy way/tool to quickly get my datainserts done?

In my script there are only insert statements, nothing else.
Re: Test-data insert execution time [message #325429 is a reply to message #325427] Fri, 06 June 2008 03:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How else could TOAD or whatever tool insert the records, but row by row, if you order it to?

Why would you use a GUI tool like TOAD to execute a plain sql-script?

Can't you use sql-loader or external tables to insert the data?
Re: Test-data insert execution time [message #325436 is a reply to message #325429] Fri, 06 June 2008 03:35 Go to previous messageGo to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
Frank wrote on Fri, 06 June 2008 03:29
How else could TOAD or whatever tool insert the records, but row by row, if you order it to?

Why would you use a GUI tool like TOAD to execute a plain sql-script?

Can't you use sql-loader or external tables to insert the data?


It is because I have no idea what I should use. I have experience with SQL Server 2000 and 2005 only till now.
Thank you for your answer, I can use google now to find out about using sql-loader. Should this tool make it this job in seconds like I have used to with SQL Server?

EDIT: I checked google for sql-loader but looks like using this needs some extra work. I have my insert script which I converted to PL/SQL syntax from T-SQL. I think I still want to use this script.

[Updated on: Fri, 06 June 2008 03:43]

Report message to a moderator

Re: Test-data insert execution time [message #325441 is a reply to message #325427] Fri, 06 June 2008 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first get rid of TOAD and use appropriate and reliable tool.

Regards
Michel
Re: Test-data insert execution time [message #325451 is a reply to message #325441] Fri, 06 June 2008 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL*PLus will probably run the insert script faster than TOAD.
It will run even faster if you run it on the server, rather than on a local client.
Re: Test-data insert execution time [message #325457 is a reply to message #325436] Fri, 06 June 2008 04:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where do the insert-statements originate from? I reckon they are generated from other data.
Can't you use that other data as source for sql-loader?
Re: Test-data insert execution time [message #325464 is a reply to message #325457] Fri, 06 June 2008 04:48 Go to previous message
daff24
Messages: 11
Registered: June 2008
Junior Member
Frank wrote on Fri, 06 June 2008 04:38
Where do the insert-statements originate from? I reckon they are generated from other data.
Can't you use that other data as source for sql-loader?


At tho moment I have only those insert scripts. In the future this could be an option, but not now.
Previous Topic: ORA-01502: index in unstable state
Next Topic: Using an array as a select condition
Goto Forum:
  


Current Time: Sat Dec 10 03:29:26 CST 2016

Total time taken to generate the page: 0.16138 seconds