?
Solved

DbColumn Look up question - Slow Performance

Posted on 2004-08-10
9
Medium Priority
?
460 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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 150 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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