Hi Nadya,

>Sorry, not sure what do you mean. Are you suggesting the same as Gregory?

More or less.

The bottom line is that you are doing too many requeries, and that is slowing the procedure down. I don't think there is much use into trying to optimize those queries more. But instead try get all the data you need for this procedure to run into one or a few cursors and work from them locally.

In these cases indexing the intermediate results and using SEEK() to lookup records might be the best solution.

IOW, it is time to look for another algorithm...

Walter,





>>Hi Nadya,
>>
>>I don't think reformatting the SQL view would be the best strategy. It will always take quite some time to execute it, esspecially if I see the definition.
>>
>>The best strategy would be to take the alias you're scannning through and this view and create a solution so that only one SQL query has to be made that provides all info and scan through the result
>>
>>IOW try to get out the SQL SELECT command out of intensive loops.
>>
>>Walter,
>>
>>
>>
>>
>>>>I would run the coverage profiler to see which lines are consuming the most time, then start working from there. At least you will see how many hits each line had and the total time it took to execute the lines of code. Then, as Calvin would say, take out the slow parts. < bg >
>>>
>>>UPDATE After I modified this view, it took 3 seconds to get the whole result. I may change the view a little bit more for speed.
>>>
>>>Thanks. I found that the v_Commisioned_Owner eats all the time. It takes 0.13 sec. to execute the select statement.
>>>
>>>Here is its definition:
>>>

>>>SYS(3054,11,'cOpt')
>>>vp_cTrans_Fk = "7087769BA5F948B2"
>>>SELECT Trans_employees_queues.ctrans_fk,;
>>>  Employee_queue_schedules.cemployee_queue_schedules_pk, Users.cuserid;
>>> FROM ;
>>>     mmviscollect!usgrlink ;
>>>    INNER JOIN mmviscollect!trans_employees_queues ;
>>>   ON  Usgrlink.cusgrlink_pk = Trans_employees_queues.ccommission_owner_usgrlink_fk ;
>>>    INNER JOIN mmviscollect!employee_queue_schedules ;
>>>   ON  Trans_employees_queues.ctrans_employees_queues_pk = Employee_queue_schedules.ctrans_employees_queues_fk ;
>>>    INNER JOIN mmviscollect!users ;
>>>   ON  Users.iid = Usgrlink.iuserid;
>>> WHERE  Trans_employees_queues.ctrans_fk = ( ?vp_cTrans_Fk );
>>>   AND  Trans_employees_queues.iactive_flag = ( 1 );
>>> GROUP BY Trans_employees_queues.ctrans_fk, Users.cuserid,;
>>>  Employee_queue_schedules.cemployee_queue_schedules_pk
>>> =MESSAGEBOX(m.cOpt)

>>>
>>>May be I need to split it.