Design brainstorm needed - looping through docs to get value

HCL Domino Domino Forum
1

I’m at a bit of a crossroads on this one and thought I would reach out for help to see if anyone had a better idea.

I have a script right now that essentially does the following:

  1. Loops through contractor profiles

  2. For each contractor, count up their hours worked in their corresponding timesheet docs for every timesheet created after a tenure date that is stored in their contractor profile (the date will be different for every contractor).

The bottleneck is obviously in step 2. I’m doing a db.search to get this value (keying off of their social security number and tenure date) and this is taking about 12 seconds per profile. This is great, but the script is now running for over 3 hours due to the increasing number of profiles.

I’m thinking that it might be faster to use an @DBColumn in an Evaluate statement. The problem with that is obviously that I only need to count the timesheets created after the tenure discount date.

Any thoughts would be appreciated. I know it might seem easier to just look for timesheets created since the last run and simply append to the totals but this isn’t feasible as older timesheets could have been adjusted.

2

Subject: Design brainstorm needed - looping through docs to get value

What I sometimes do in cases like this is put a mark on the timesheets, say a field called TimeSheetAgent, and give it a time/date stamp when you process it.

The next time you run, you only look for time sheets for this person which have not been updated since the time on that field.

This allows you to make a view to grab the time sheet docs, because you aren’t really worried about the timesheet’s date anymore. That takes care of itself after the first time the agent runs.

Of course, I’d stamp all the old ones just to get them out of the way so the first run won’t be bogged down too much. Also, I might put some date checking into the agent so that if an old timesheet gets thrown into the view for random reasons that you don’t necessarily process it. But having a few extra docs to check isn’t a big deal, and is certainly still faster than grabbing with db.search.

regards,

raphael

3

Subject: RE: Design brainstorm needed - looping through docs to get value

Thanks Raphael, I appreciate your response.

I’m a little leery of this approach… if I understand you, I’d have to juggle both the last timesheet modified date and the tenure date. I’d then have to have two sets of totals, it seems… one for the timesheets before the updated date, and ones for the timesheets after the updated date.

I’m considering a new approach. Have a separate script run to look at contractor profiles that have been modified. If modified, stamp all timesheets with the tenure date. This would allow me to just do an @DBcolumn and an @Sum to get the total hours

4

Subject: RE: Design brainstorm needed - looping through docs to get value

Have you looked into using NotesViewNavigator, NotesViewEntryCollection and NotesViewEntry classes in your code?

The reason I ask is that you mention using Evaluate with @DbColumn, so the idea of skimming the view isn’t anathema.

I’ve done some quite fun stuff in the past manipulating data in the view index, and it is FAST.

5

Subject: RE: Design brainstorm needed - looping through docs to get value

Thank you for your response. Would the response time really be that much better? Right now I’m getting a document collection using search… can you give me a little more information on what you are saying? Once again, I’m getting a different collection for each contractor.

6

Subject: RE: Design brainstorm needed - looping through docs to get value

If you use a NotesViewEntryCollection, you’re working off the view INDEX which is far more efficient than walking a collection of NotesDocument objects – reading from or writing to a NotesDocument is expensive in comparison. Now, what you;re trying to do may not be possible with views, but what I’m getting at is that you could have a categorised view by contractor, with various required bits of info in the view, and you could then walk each category (i.e. contractor name) in the view, doing your thing. It would be faster than building a NotesDocumentCollection object and then iterating that, for sure.

7

Subject: Design brainstorm needed - looping through docs to get value

Something a peer of mine stumbled on is that with certain types of document collections, accessing the “.Count” property KILLS performance. If you can write your code in a way that avoids using the “.Count” property you may get much better results.

8

Subject: RE: Design brainstorm needed - looping through docs to get value

Thanks so much! that is another excellent point. Sure enough, the guy that wrote this used this through every iteration rather than passing the value to a property. They also used “getnthdocument” too, which is another no-no