topic Re: CURSOR method in Application Development and Automation Discussions

CURSOR method

Former Member — Thu, 11 Feb 2010 10:59:21 GMT

Hi,

Im using cursor method in my sql query. For two tables, im putting the cursor. Im populating data from two tables into third internal table.

Now, the data from third internal table is inserted into a ztable.

During this entire process, my first cursor will be opened and second cursor will be open and closed till it gets the complete records based on first cursor. data insertion happens into a ztable and finally closing the cursor for the first one.

{

open cursor dbcur1

open cursor dbcur2

close cursor dbcur2

inserting into ztable

close cursor dbcur1. }

My problem is that a huge time is consumed while inserting the rows into dbtable. Here, im trying to do a batch processing. So for each batch process, my ztable will be inserted with new records.

So, what the best case of reducing the time for processing this activity.

thanks

rohith

Re: CURSOR method

Former Member — Thu, 11 Feb 2010 11:08:50 GMT

Hi Rohit,

Use WAIT 5 seconds statement for each batch processing.It means after uploading the first recod into dbtable then only next process has to be done.It reduces the time.

Regards,

Sri Ramya.

Re: CURSOR method

Former Member — Thu, 11 Feb 2010 11:18:56 GMT

Hi Ramya,

But this is a database commit and this will cause a run time error. Since, the cursor is still not completed and the program is trying to commit work on the database table.

Thanks

Rohith

Re: CURSOR method

Sandeep_Panghal — Thu, 11 Feb 2010 13:58:05 GMT

you may try with limiting the selection using PACKAGE SIZE and hence less no. of db commits at one time.

Re: CURSOR method

vinod_vemuru2 — Thu, 11 Feb 2010 14:08:55 GMT

Hi,

The best method i can think of is update the data outside the SQL. Collect data into an internal table. Once all the data is collected, close all the cursors, then INSERT the data into ZTABLE at single shot.

Logic behind this is, it reduces the number of database hits. As we know database operations works on queing principles. So instead of waiting in Q for each INSERT statement, update the data at once and COMMIT.

Hope you got the logic.

Thanks,

Vinod.