Solved

DbColumn Look up question - Slow Performance

Posted on 2004-08-10
9
452 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lotus Domino - LKMgr Long Held Lock Dump 10 2,826
Lotus notes email code 13 94
Lotus notes VB code 4 208
NT Kernel & System Process CPU Usage Maxin out 8 56
For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

825 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