Home » SQL & PL/SQL » SQL & PL/SQL » Need to optimize query (oracle 9i)
Need to optimize query [message #404159] Wed, 20 May 2009 11:23 Go to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Hi ,

Hi am facing performance issues while inserting 26000 records, it took me around 30 minutes to insert the records .


Below are test script
Inserted records are similar to this records. Please help me immediately if possible.

Create table test_insert
(
column1 number(10),
column2 number(10),
column3 date,
column4 date,
column5 varchar2(20),
column6 date,
column6 varchar2(20)
)

INSERT INTO X ( column1,
column2,
column3,
column4,
column5,
column6,
column6 )
VALUES ( 379127,
177284,
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
'sandeep',
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
'sandeep' )

Thanks
/
Re: Need to optimize query [message #404161 is a reply to message #404159] Wed, 20 May 2009 11:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the sticky post in the Performance Tuning section. Then have a look at the explain plan, if that doesn't give a clue trace the session and process the trace file with tkprof.
Re: Need to optimize query [message #404164 is a reply to message #404161] Wed, 20 May 2009 11:31 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
I do not have an access to that , i am using remote desktop connection.

Please help me any way.

thanks.
Re: Need to optimize query [message #404166 is a reply to message #404159] Wed, 20 May 2009 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put all inserts in a single INSERT ALL statement.
Do not execute 26000 statements.

If the data comes from a file use SQL*Loader and not INSERT statements.

Regards
Michel
Re: Need to optimize query [message #404167 is a reply to message #404159] Wed, 20 May 2009 11:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What indexes, triggers and constraints do you have on the table?

How are you inserting the data? Do you actually have list of 26,000 Insert statements, or is this something else that you're not telling us.?
Re: Need to optimize query [message #404169 is a reply to message #404167] Wed, 20 May 2009 11:37 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
i am having a list of 26000 individual insert statements.
Re: Need to optimize query [message #404178 is a reply to message #404169] Wed, 20 May 2009 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sanyadu wrote on Wed, 20 May 2009 18:37
i am having a list of 26000 individual insert statements.

As I said, go to a big INSERT ALL one.

Regards
Michel
Re: Need to optimize query [message #404180 is a reply to message #404169] Wed, 20 May 2009 12:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Where do you get this data from?

If you could use EXP to get the data out of the other table and IMP to bring it in (or even better EXPDP and IMPDP), then an import of this size should take about 30 seconds.
Re: Need to optimize query [message #404181 is a reply to message #404159] Wed, 20 May 2009 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
26000/(30*60) = 14.4 inserts per second
or 70 milliseconds per insert

Not entirely unreasonable.
Especially so if 1 complete network round trip is needed between client & server for every row.

[Updated on: Wed, 20 May 2009 12:10]

Report message to a moderator

Re: Need to optimize query [message #404328 is a reply to message #404181] Thu, 21 May 2009 06:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Particularly if there were indexes or constraints on the table.

Basically, you've picked a really slow way to get your data into the database.

It will run a little quicker if you can execute this on the server, as you'll reduce your network overhead, but it won't make a massive improvement.

If you'd ever answered about the indexes, constraints and triggers I could give you advice about what to do with them, but you decided not to bother answering.
Previous Topic: Calculating values from previous record on different column
Next Topic: Strange PLS-00302 behavoir
Goto Forum:
  


Current Time: Tue Dec 06 06:46:55 CST 2016

Total time taken to generate the page: 0.13866 seconds