I have a report that takes over an hour to run. ( i have attached it to this post for anyone who wants to help out on this one)
The report is one i would like to run once a week to refresh the data on my website.
I was reading about the "Eval" function, but can't seem to figure out where to put it.
The options page is quite large (some of the items on the options page aren't used in the report but i use them for reference.) (Actually, The options page is contains a lot of mandatory criteria that isn't in Navision)
I'm not asking for someone to just fix my report so it runs better. Just some guidance and explination of what they changed in the report to run faster.
Help, make it finish faster!!!!
Date
Votes
3 comments
-
Jet Reports Historic Posts I can't really run it, but I can give some pointers
For the formula's like: (esp. when filtering on the primary key)
=NL("Rows";"Item Category";;"Code";I3)
=NL("Rows";"Manufacturer";;"code";H3)
It is better to leave the "rows" blank. So:
=NL(;"Item Category";;"Code";I3)
=NL(;"Manufacturer";;"code";H3)
From the help NL Function reference:
Blank or omitted
Returns the Field or record key from the first record that matches the other NL arguments.
Furthermore, it really is a simple report so I can't find why it runs so long.
Can you try to replace those formula's and get back to us with the result?
Regards,
Jan -
Jet Reports Historic Posts I took out the "ROWS" in H and I NL.
Still taking 56 minutes to run the report.
I just put together a test report that only lists Item > No_ and by just listing that one item, the report takes 7 minutes to run
21,593 rows listed.
Crystal Reports takes less than one minute to list the same list.
Is jet really that slow? -
Jet Reports Historic Posts I would start with analysing the key usage. One of the key elements to Jet is the way it selects indexes and keys. Here is the troubleshooting process I would start with
1) General approach
- Run the report showing details, see if any one particular series of statements takes a long time to run. You'll notice by the slow counting. If you see this, you may want to either modify your filtering elements to help jet choose a different key, or create a new one in Navision. There are a couple of good posts on key usage in this forum that will guide you.
2) More scientific approach
- Work from left to right, start with only one main NL formula. keep adding them until you see the run time increase. Then you will know which exact formula/query is causing you the grief. You can then look at the key its using, and decide if its the best one for the purpose.
The other key element of Jet is it si more or less bound by the Navision API. From my experience, Jet is generally significantly slower than retrieving data than a SQL query. I inherited a Jet extract being used by a DTS package to upload to an intranet web server. The Jet reports ran all night, and the DTS took 2 minutes. I re-wrote those to use SQL only and the entire process now runs in 2 minutes.
The point I'm making is Jet strenghts is in the ease of use for non DBA types to create reports. For my money, you can't beat the level of control you have over the formating, and how easy it is for regular users, especially compared with some of the SQL based reporting software.
Good luck!
Please sign in to leave a comment.