Solved

DbColumn Look up question - Slow Performance

Posted on 2004-08-10
9
455 Views
Last Modified: 2013-12-18
Hi Experts,

As mentioned in my earlier questions ... I am creating a report in a form with approximately 200 DbColumn lookup values from two views. When I click on the report link its takes approximately 30-60 seconds to generate the report as it is computing all those field values onto the form.

Is there a better way of writing the code to lookup values from a view?? Also to speed up the report generation.

Here is a sample code from one of the 200 lookup fields on the form:

a := @Unique(@Text(@DbColumn( "" : "NoCache" ; "" ; "VIEW-1" ; 8)));
b := @TextToNumber(a);
b

Thanks in advance.
0
Comment
Question by:chinnaji
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 11763006
It might not be the dbcolumn.. as formulas are much faster than scripts. The bottleneck in most cases is the conversion and calculations.. you have to minimize them so that you can get more speed.

~Hemanth
0
 

Author Comment

by:chinnaji
ID: 11763043
Hi hemantha,

How can I minimize the conversions?? Any suggestions.

The calculations fields have this code :

res := FIELDB / FIELDC;
@If(@IsError(res); ""; res)




0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11763312
You have 2 views, that's all? Then it should be possible to make things faster. Several alternatives:

1. make two new hidden views, and give them one column with all the values in it, separated by some character. I use "~" but it is a valid character. Alternative: Chr$(255). Then in your code, do only two @DbColumns, and split up the data using
    x:= @Explode(@DbColumn(...); "~");
    field4:= Subset(Subset(x; 4); -1)
This will retrieve the 4th value in the list x.

2. Only possible with R6, and I don't know what the gain in speed will be: don't use NoCache, but use "ReCache" the first time and then use the cache. That's what the cache is for.

3. Use LotusScript in the QueryOpen or PostOpen of  your form, to retrieve all the fields. Probably  s l o w . . .

4. Use a proper report generator, like NotesToPaper or Midas, because report generation in Notes will always lead to sub-sub-sub-standard reports.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 11764231
Post full code here, that might help
0
 

Author Comment

by:chinnaji
ID: 11765330
Here's an example from a field..

a := @Unique(@Text(@DbColumn( "" : "NoCache" ; "" ; "CP1-ALL-CAMDEN" ; 5)));
b :=@TextToNumber(a);
b
=====================================================
a := @Unique(@Text(@DbColumn( "" : "NoCache" ; "" ; "CP1-YES-CAMDEN" ; 5)));
b := @TextToNumber(a);
b
=====================================================
res := CP1_1B / CP1_1C;
@If(@IsError(res); ""; res)
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 11765744
can't you reduce the two dbcolumns into one by using view to filter content ?? That will make one lookup instead of 2

From the above formula that is what you can do to improve performance..
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 50 total points
ID: 11766957
Are all the lookups the same?  LotusScript mightbe more effcicinet in that case.  You can reduce time by glomming multiple values into a single lookup, but then you have to calculate them all out, not sure if that might in itself be inefficient.
0
 

Author Comment

by:chinnaji
ID: 11783688
One of the views has totals of ALL and the other View has totals of only YES's.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 11788355
For lookup purposes, that could be one view.  For UI, you might very well wantto due to the way totalling and per centages work.  It is generally a bad practice to use a UI view from lookups.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question