Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

DbColumn Look up question - Slow Performance

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
chinnaji
Asked:
chinnaji
  • 3
  • 3
  • 2
  • +1
1 Solution
 
HemanthaKumarCommented:
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
 
chinnajiAuthor Commented:
Hi hemantha,

How can I minimize the conversions?? Any suggestions.

The calculations fields have this code :

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




0
 
Sjef BosmanGroupware ConsultantCommented:
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
Technology Partners: 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!

 
HemanthaKumarCommented:
Post full code here, that might help
0
 
chinnajiAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
qwaleteeCommented:
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
 
chinnajiAuthor Commented:
One of the views has totals of ALL and the other View has totals of only YES's.
0
 
qwaleteeCommented:
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

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!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now