Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DbColumn Look up question - Slow Performance

Posted on 2004-08-10
9
Medium Priority
?
462 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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.
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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

610 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