• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Insert statement very heavy

 
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I am calling an Oracle stored procedure which runs an Insert statement on a 23 column table. The values supplied to the Insert are through a select statement that collects required data from different tables, does some string concatenation, uses TO_CHAR, Decode, NVL functions and a mixture of all to create values for individual columns. The Select statement also contains a Where clause which does 3 comparisons all of which should be true i.e. (condition AND condition AND condition). One of the conditions looks like this

TO_CHAR(purhc_dt,'DDMMYYYY')||accountnum||corp = trans_id.

The select may return several records, potentially 10-100, which are then inserted.

The result, as expected, the statement is very heavy on the Oracle db and it took ages to do anything before finally running out of temporary tablespace. Any comments/suggestions how to improve this?

Regards,
Murad.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Murad,
Have you tried running SQL explain to see where all the time goes?
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Murad Iqbal:

One of the conditions looks like this

TO_CHAR(purhc_dt,'DDMMYYYY')||accountnum||corp = trans_id.

The select may return several records, potentially 10-100, which are then inserted.

The result, as expected, the statement is very heavy on the Oracle db and it took ages to do anything before finally running out of temporary tablespace. Any comments/suggestions how to improve this?



It would be to quick to give any suggestion until we don't see your query but using any function on a column then a comparsion is definetely going to make your select very slow, you need to tune to query first consult your DBA.

For a instance let say you have 10K rows in your table and you want to insert 5 out of them which has some desired purhc_dt.
now using TO_CHAR(purhc_dt,'DDMMYYYY') will convert the all 10,000 rows to string them compare to your desire date.

You need to avoid using function this way.

Think about a exaple below



A query like this will be a number of times faster than a query you sampled above.

Shailesh
 
reply
    Bookmark Topic Watch Topic
  • New Topic