Items with @DbLookup "Cache" / "NoCache" + Subforms + One Embedded view. Performance

HCL Domino Domino Forum
1

Dear all,as indicated in the object of the post i wish to ask you some things about my application performance.

I think this is a common situation of each lotus notes developer during his/her lotus notes “life”.

I have in my form

Many Items with @DbLookup “NoCache” option,

3 simple Subforms,

1 Subform with One Embedded view (take note about this)

my form enable the users to create a

and add on it many (response docs) that will be displayed step by step in the embedded view with a “Show Single Category” option with the value of the @universalid of the document.

Lotus Notes does not support to create a dinamic table with dinamic rows (with line separator). Each document contains a very long description text field (more 200 characters)…so, this is the explanation which i have used the embedded view instead the multivalue text field…it is the only solution. The embedded view is in a Subform (not computed subform).

  1. Purchase Request and Items documents have Authors and Readers field. This is very important for the application, i cannot remove this functionality.

  2. There are 100 Purchase Request create in the database daily, with 10 Item (response docs) avarage associated to it.

Now, the system, after 1 year of work have many many many documents (Purchase Request+Items).

I have cleaned all @dblookup duplication and let on the form only the @dblookup necessary.

For @dblookup function i have try to use the “Cache” method instead of “NoCache”…but i need to have every real time information, and the “Cache” methos is not a good approach for all fields (eg: in the purchase request document i have a approval cycle workflow with person from N&AB and relate Deputies)

The most @dblookup is on a computed for display field.

I think i can use “Cache” method only for configuration information in a computed for display field (the configuration document is not updated frequently,one or two times for year).

After this explanation, i tell you the REAL problem:

The problem is that the application performance is not very fast.

a) First time when i open the document is slowly, second time is more fast. (12’’ second first time, 5’’ second time)

b) When i stay in read/edit mode the emebedded view is in a collapse section, when i expand the section the embedded view (to show the of the ) is slowly to load (remember that the show single category option is available)

c) When i save the document i have a simple code in the QuerySave event, and a simple code in the PostSave event…so, i have 10 second everage when finish QuerySave event and start PostSave event… i think is the time for refreshing the document fields after the QuerySave ?

I have also try to use “ReCache” method instead of “NoCache” to test the performance, but on production server seems that the “ReCache” method is to slowly.

I work with lotus notes from many years, and we have a good team of developers with many years experience.

i think the most work for the server is related on the @dblookup + embedded view… in particular for the embedded view.

Do you have some idea for increase the performance on the database ?

Actually:

  • The embedded view have Refresh: Auto, after first use, and Discard: if inactive after 45 days.

  • The embedded view have also the InViewEdit event enabled with lotus script code (is a simple lotus script code)

  • as indicated before i have reduce the @dblookup on document where necessary and possible…The field (computed for display) actual make a @dblookup “NoCache” on Configuration Document, the column formula have 30 parameters item separate from “%#” value, the result on document is:

Field = “serverarchive%#patharchive%#dbadministrator%#” etc…

on each computed for display fields i retrive the parameters with @Word(Configuration;“%#”;1) - @Word(Configuration;“%#”;2)…and so on !

Excuse for my english, i hope someone can help me.

Thank you very much at all

Dadoo

2

Subject: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

This may help: @Dbfunction caching strategies

Have you considered archiving old requests?

Have you considered “condensing” old requests? Once they have been fulfilled, convert them to use a different form which, instead of an embedded view, contains a rich text field. Write an agent to create a table in the rich text field, insert the data from the response documents, and delete the response documents. This will immediately reduce the number of documents 90%, with a great increase in performance.

Delete any unnecessary views. Delete unneeded columns from views. Simplify complex selection and column formulas.

Think about changing those CFD fields to Computed.

You most certainly can have a table on the form with a variable number of rows, and give the user help in filling in the fields. It’s a bit of work to customize the solution to your application, but there are several ways people have developed to do this. See Betsy Thiede’s Dynamic Tables Article in The View (sample download is free). The Domino Design Library Examples also includes a “dynamic table” tool for the Notes client. Betsy’s solution uses a real table, but it is flawed in that it saves the document every time you modify the data in the table, as a way to get the rich text to update. To get the updated data to display without saving, you could use the Update rich text technique (though this will work a lot faster if you can speed up those CFD fields somehow, since you have to close and reopen the document.

3

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

Thank you Andre.

Yes, i have an archive database of the application.

When a document is in status “Approved” we archive this document and the related (showed in the embedded view) in the archive database.

My application is Corporate and replicated on 4 server.

Now, on 27 March we release a new version of the application with the new performance that i have described in my first post (minor @dblookup, embedded view without “Automatic” refresh…we set the default value “Auto, after first use”, and somo other…)

I will check the performance with this new functionality.

i will try to change some CFD fields in computed, in this way i think that i have no @dblookup calculating during the open the document from the view.

i know the tool and scirpt of Betsy Thiede for create dinamic table in RichText field, but this approach is not completely applicable on my application.

Now, i check the “dynamic table” in the Library. do you have an example for ND6 releae ?

4

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

Yes, here is an example: Dynamic table on a Notes form (Steal This Code)It should work with 6.0 on up (though I edited it most recently with 8.0.1).

Why is the “approach not completely applicable”? Possibly there’s a solution for whatever is preventing you from doing this.

5

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

Oh my God !this example seems perfect.

and it refer on a same/similar situation of my application (Price, Quatity, etc…)

  • it is very good, it use a RichTextItem without saving the document for each insert ?

  • it is very good to create a button for each insert in order to re-edit the item line

  • the code seems all in javascript language.

the only “problem” is that the graphic interface is not veru good…but i think i can change.

So, do you think that this approach is good for my situation ?

however i try it in the development envirement.

6

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

Well, as you may have gathered from his comments on my blog, Nathan Freeman would probably tell you to leave them as separate documents and seek elsewhere for performance gains, but I think it’s a good way to go.Actually, the code is mostly in LotusScript – the JavaScript piece is just to trigger the LotusScript agents.

7

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

ok, i will try to modify this example in order to add the interaction with a separate document.While add and modify each line i add and modify the related item document.

The only thing is that, i suppose, to change the agent redrawtable in order to cycle on the document and not on items… if feasible and possible.

8

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

I think you’re missing the point of this. The reason for creating the rich text table is to avoid having separate line item documents.

If you’re still going to have a separate document for each item, you might as well use one of the UIs based on an embedded view, as mentioned in my blog entry and in the comments people have posted to it. I linked to one that uses an embedded view and an embedded editor.

9

Subject: RE: Items with @DbLookup “Cache” / “NoCache” + Subforms + One Embedded view. Performance

Yes Andre,i see the example to use embedded view and an embedded editor.

But i need to have also separate document Item because we transfer this document to Oracle with LEI Replication activity.

i think that the major problem of the application is to have this embedded view, with show single category, always available on the form.

as describe before, actually, while one user inserti each item, other users do the same action. The Item view (and the embedded view) is always populate.

Actually in production environment the users is not happy of the open, edit, populate item of the perfomance… we think also that the major problem is on the server machine… for the next future, as soon as possible, all clients and server machine will be migrate to the ND7.x version.

(NBB:

Now, we still trouble with the problem of the attachments in read mode for ND6.5.3 version. )

So,

if i remove the embedded view from the form, and use the uidoc.import method instead the embedded view togheter the separate line items documents, i will always the Items documents in my application (until will be archived) but the document will not use the embedded view.

NBB: i inform you that now, with the release 6.5.3 and 6.5.5 we have also the problem of WRONG category showed in the embedded view…if the user click F9 or uidoc.refresh the embedded view is reloaded.

For your information i tell you that one user have reported to us that also with the release 6.5.6 this problem still remain.

But in this fix list say that was “solved”:

http://www-1.ibm.com/support/docview.wss?rs=3025&context=SSPQ69&dc=DA400&uid=swg27009492&loc=en_US&cs=UTF-8&lang=en&rss=ct3025lotus

Design AMOR6JT5JH SPR# AMOR6JT5JH

Prior to this fix, an embedded view with a single category jumped to another category without user’s intervention, when the database on a server was updated by other users.