Poor performance due to multiple time DB hits (pc_assign_worklist & pc_assign_workbasket).
I have a requirement where I need to show list of cases for a particular user/caseowner. Now each case can have multiple assignments, where all the assignment owners should be same as the case owner. For this I am taking up each case and searching pc_assign_worklist & pc_assign_workbasket for assignments; then I am comparing all the pxAssignedOperatorID with caseowner. But if have 1000 cases in the list, I am hitting the two DBs 1000*2=2000 times. This is causing the final list to be populated after 10-15 minutes.
By saying case owner, did you mean to say the person who created the case? In that case, a workbasket cannot be the creator of a case. So that takes on table out of the equation. Then you can do a join of the work table and the assign-worklist table to get a list of cases that are having the same creator name (Inner join where work created operator == assignment created operator) This will give you a list of records that are pending with the user who created the case.