0

speedup NL

Jet Reports Historic Posts

August 19, 2011 11:53

None

Follow

Based on my Jetreports knowlegde and skills, I have created an NL which is working great ,
As it is in dutch, I hope you guys can understand what it means.

started as a playground to learn about JET functionality , but turned into the most used report I have

Now i'm looking for a way to speed it up,
the sheets contains +30K of this calculation type of NL
together with +30k simple NL Lookups

the reports runs for about 40-45 minutes.
switching from viewer to desinger mode takes about 30 min

=((NL("eerste";"artikel";"Voorraad";"nr.";$C46)+NL("sum";"inkoopregel";"Openstaand aantal";"Nr.";$C46;"Verwachte ontvangstdatum";AI$6)-(NL("sum";"Materiaalregel";"resterend aantal";"Artikelnr.";$C46;"Status";"<>gereedgemeld";"Resterend aantal";">0";"Vervaldatum";AI$6;"bedrijf=";"bedrijf")+NL("sum";"Verkoopregel";"Aantal";"verzenddatum";AI$6;"Nr.";$C46;"Openstaand aantal";">0"))))-($AG46*AI$3)

it is creating a stock requirments reports, bij collecting data from 4 tables

thanks

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts August 19, 2011 15:30

  Hi Jan,
  
  I'm doing the math on this report. 30k cells with this formula * 4 Jet functions per formula + 30k more Jet functions = 150k Jet functions. 45 minutes * 60 seconds = 2700 seconds. 150k Jet functions / 2700 seconds = 55 Jet functions per second! that is actually pretty great performance for Jet. I have real doubts that you'll be able to make it run significantly faster than that. You're not doing anything crazy in the functions themselves, so I don't know of much you could do to them.
  
  The only thing I can think of in this case that could possibly speed up the report is adding NAV keys and sumindexes. Add keys in NAV with the exact filter fields you're using on each table. Then for the NL(Sum) formulas, make sumindexes in NAV for the fields you're summing for the matching keys. However, considering how fast this report is running already, I bet you already have keys with sumindexes for at least some of those NL(Sum) formulas. It would be worth checking into though.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 22, 2011 07:33

  Hi Hughes,
  
  Thanks for your quick answer,
  NAV keys and sumindexes will be a complete new world for me to discover,
  
  I will try to find some resources about using NAV keys and sumindexes
  and see what I can learn from it
  At the moment i'm not familair with NAV key's at all
  
  For now, a dedicated workstation just arrived which I will use to run this and other jetreports, at least it wo'nt interfere with other daily tasks :D
  
  one other question,
  I was told that if you create Jetreports with a runtime over 20min you should reconsider the design? does this make sence?

• 0
  
  Jet Reports Historic Posts August 22, 2011 14:30

  Hi Jan,
  
  NAV keys and sumindexes are definitely an advanced NAV topic. If you are really serious about making reports run faster, it is a topic you should visit because it can make a huge difference.
  
  As far as the report taking longer than 20 minutes, I think it is fairly subjective. I have met people who were unhappy with a report that ran for longer than 10 minutes and other people who were very happy when a report ran in 3 hours (hint, the report had previously taken 20+ hours so getting it down to 3 was good!). With the Jet Scheduler, you can run longer reports at night or when you're not otherwise using a computer which makes it much easier to run longer reports.
  
  There is definitely also a school of thought that if a report is taking a very long time, that report is probably really huge and do you really need to see all that information in one place? Will you really look at all 20+ pages of numbers you are producing with the report? You probably won't, although for various reasons people do sometimes need to produce such huge reports. Jet is really not a tool that was designed for "data dumping". It's a much better tool for creating smaller, more detailed reports with calculations you could not otherwise do easily outside Excel. So I suppose if a report is taking a long time, you could ask yourself, "Do I really need to see ALL the information I'm getting" and if the answer is yes, a second question might be "Do I need to see all of this information together in a single report or could it be separate reports that I run at different times?" There's no real right answers here, it's all about personal preference. :-)
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 23, 2011 12:20

  Hughes,
  
  After reading your input which made a lot of sence.
  I had a close look again at the report to see what else I could do,
  and found a first solution to eliminate 25k of NL's which I converted into NF's
  
  Now the runtime is 23 min :D
  
  second solution wil be to split the report in two,
  But here I have to see if it is suitable to work with an optionsheet using the NP eval first, otherwise I will spilt the report using a hardcoded linked filter
  This I have to workout first.
  
  
  thanks
  Jan

• 0
  
  Jet Reports Historic Posts August 23, 2011 16:14

  Jan,
  
  Good catch. Converting NL's to NF's can definitely speed things up in some situations, so I'm glad you caught that. :-) Actually, if you are using the latest version of Jet 10.5, you may gain some performance increase by putting each of the NF functions in their own cells as well as there is a performance optimization around NF functions that only takes place if they are by themselves in the cell. It might be something else for you to try.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 23, 2011 16:46

  Jan,
  
  Good catch. Converting NL's to NF's can definitely speed things up in some situations, so I'm glad you caught that. :-) Actually, if you are using the latest version of Jet 10.5, you may gain some performance increase by putting each of the NF functions in their own cells as well as there is a performance optimization around NF functions that only takes place if they are by themselves in the cell. It might be something else for you to try.
  
  Regards,
  Hughes

  
  
  Will check for the currentJet version,
  and the NF's are already in their own cells
  
  and for now i will put tis topic to solved