Oracle Prepared statement, taking too long for complex calculations.

[Ankit]

Hi All,

I have two tables A30DayUsage and A90DayUsage.
Based on these source table i have to populate another table called
resCalculationTable.

Now for the values in resCalculation table are very complex.

for example i have one value called quarter, where i will be doing
SysDate - a field from A30DayUsage table and then convert it to
quarter string. all the fields have very complex date and String
manipulation.

My java program to do this works fine but it takes a lot of time,
around 8 hours to load 57000 records.

Is there a better way of doing this ??
Thanks
Ankit.

 

[Daniel Pitts]

Hi All,

I have two tables A30DayUsage and A90DayUsage.
Based on these source table i have to populate another table called
resCalculationTable.

Now for the values in resCalculation table are very complex.

for example i have one value called quarter, where i will be doing
SysDate - a field from A30DayUsage table and then convert it to
quarter string. all the fields have very complex date and String
manipulation.

My java program to do this works fine but it takes a lot of time,
around 8 hours to load 57000 records.

Is there a better way of doing this ??
Thanks
Ankit.

You might try doing it in batches. It could be that you have a lot of
overhead if you call update or insert for every row and you're not in
a batch.

Alternatively, you might be able to do the work in all SQL. This
might be a much better approach.

 

[Chris]

Hi All,

I have two tables A30DayUsage and A90DayUsage.
Based on these source table i have to populate another table called
resCalculationTable.

Now for the values in resCalculation table are very complex.

for example i have one value called quarter, where i will be doing
SysDate - a field from A30DayUsage table and then convert it to
quarter string. all the fields have very complex date and String
manipulation.

My java program to do this works fine but it takes a lot of time,
around 8 hours to load 57000 records.

Is there a better way of doing this ??

You haven't provided enough detail to suggest a better approach. My only
thought is that 57,000 records might be able to fit into memory easily,
in which case you could read the whole thing into memory quickly, do
your calcs there, and then dump it all back to the database.

My guess is that if this process is currently taking 8 hours with such a
small amount of data, you're probably doing very inefficient database
access. So get it out of the database altogether.

Whether this will work or not depends on your app, of course.

My only other thought: temporary tables are your friend.

 

[Greg R. Broderick]

Hi All,

I have two tables A30DayUsage and A90DayUsage.
Based on these source table i have to populate another table called
resCalculationTable.

Now for the values in resCalculation table are very complex.

for example i have one value called quarter, where i will be doing
SysDate - a field from A30DayUsage table and then convert it to
quarter string. all the fields have very complex date and String
manipulation.

My java program to do this works fine but it takes a lot of time,
around 8 hours to load 57000 records.

Is there a better way of doing this ??

Have you analyzed the performance of the query? EXPLAIN PLAN is your friend,
as are the more sophisticated analysis tools available in Oracle.

How long does the same query, or set of queries take when run outside of
Java, in SQL*Plus? You need to determine if it is database performance that
is your bottleneck or if it is java performance that is your bottleneck.

If you are doing calculation or processing (in Java) of the rows returned by
your queries on these two tables, then you should perhaps also look at
splitting up the result sets from the two queries into chunks and processing
multiple chunks in multiple threads in your program.

Without knowing the structure of your database schema (tables and indexes)
and the specifics of your queries, it would be very difficult to advise you
further, though.

Cheers!

--
---------------------------------------------------------------------
Greg R. Broderick (e-mail address removed)

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

 

[GArlington]

50g2000hsm.googlegroups.com:









Have you analyzed the performance of the query? EXPLAIN PLAN is your friend,
as are the more sophisticated analysis tools available in Oracle.

How long does the same query, or set of queries take when run outside of
Java, in SQL*Plus? You need to determine if it is database performance that
is your bottleneck or if it is java performance that is your bottleneck.

If you are doing calculation or processing (in Java) of the rows returned by
your queries on these two tables, then you should perhaps also look at
splitting up the result sets from the two queries into chunks and processing
multiple chunks in multiple threads in your program.

Without knowing the structure of your database schema (tables and indexes)
and the specifics of your queries, it would be very difficult to advise you
further, though.

Cheers!

--
---------------------------------------------------------------------
Greg R. Broderick (e-mail address removed)

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

8 hours = 28800 seconds
That means that you are processing just under 2 records per second, I
would say this is NOT just inefficient DB access, there is something
wrong with your calculations too.

 

[GArlington]

Hi All,

I have two tables A30DayUsage and A90DayUsage.
Based on these source table i have to populate another table called
resCalculationTable.

Now for the values in resCalculation table are very complex.

for example i have one value called quarter, where i will be doing
SysDate - a field from A30DayUsage table and then convert it to
quarter string. all the fields have very complex date and String
manipulation.

My java program to do this works fine but it takes a lot of time,
around 8 hours to load 57000 records.

Is there a better way of doing this ??
Thanks
Ankit.

BTW: did you know that most DBs will let you do a lot of different
calculations (including date/time) in SQL?