Dear all,
I've tried to retreive per product, the tax amount (not VAT) from sale invoice line (and Sale Cr. memo line), tax is associated to a product but not on the same line in Sale invoice line table, but it is a line after. I can retreive tax with the folowing formula, but it is too long (1 minute per tax per product). this formula has to be run for each product (more than 100 product), and the total calculation time is over several hours.
HOW TO IMPROVE RESPONSE TIME IN THIS CASE (another way to retreive this tax amount, indexes, sql formula, …) ?
=NL("sum";"Sales invoice Line";"Amount";"Posting Date";$D$6;"Item Charge Type";"TAX";"Gen. Prod. Posting Group";"TAX-01";"Link=";"Sales Invoice Line";"Document No.";"=Document No.";"Line No.";"=Attached to Line No.";"No.";E10)
in $D$6 the date period (01/01/2011..31/01/2011)
in E10 the product code
Nav database : SQl 2005
Nav client : Nav 5 SP1
Best regards
Michel
Date
Votes
8 comments
-
Jet Reports Historic Posts Hi Michel,
So I think probably you need to create some Nav keys to speed this up. Nav keys do create SQL indexes behind the scenes (indirectly, it's not an exact 1-1 correlation). Jet can't directly use a SQL index, but it can directly use a Nav key (we have to pick what key to use when querying).
So both your main query and the link are to the Sales Invoice Line table, so we should create 2 keys on that table which should help speed up this query. Jet can't use a sumindex to do the sum since there is a link filter present. Also, sorting doesn't matter when you're doing a sum, so only the filter fields are really relevant for creating the appropriate keys.
For the first key, you want a key that contains all your filter fields in the primary query, preferably in the same order specified in Jet. So the key would have the fields "Posting Date", "Item Charge Type", and "Gen. Prod. Posting Group."
For the second key, you want a key that contains all the filter fields in the link portion of your query. So the key would have the fields "Document No.", "Line No.", and "No.". However, Document No. and Line No. are part of the primary key of this table, which gets appended to all secondary keys, so you can't add them to a key explicitly. So the key would just contain the "No." field.
If you add those 2 keys to the Sales Invoice Line table, it should help speed up this query. Of course, your report could be slow for other reasons (1 minute per line is a REALLY slow report). If you connecting to your Nav server over a VPN going over the internet, that can be really slow. Also depending on how the rest of your report is structured, other parts of the report could be really slow. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Than for your advices.
YES ! performance is better after creation of the two indexes: 30% of time saved (40 secondes per line instead of 1 minute).
I've added "Key=";{"Posting Date";"Item Charge Type";"Gen. Prod. Posting Group"}; … in nthe formula to be sure that the new right key is used.
The report is run from a terminal server (Citrix) connected to the Nav server on the same LAN. I will check if some specififc setting to be done on these kind of architecture to improve performance.
At now I'm using Jet 2010 version 10.1.10314.0, do you know if new version of Jet (2011 or 2010 R2) can be usefull to improve response time ?
Best regards -
Jet Reports Historic Posts Michel,
I'm glad that helped! As far as your report time, obviously it does depend on what else you're doing on the report. 40 seconds seems like a long time per line, but I guess if you had 50 sum functions like the one you describe on each line and the table they are summing data from is huge then it wouldn't be so unreasonable. Volatile functions like NOW() and TODAY() also slow things down because they cause other functions to be recalculated more often. You can post your report (in design mode obviously so your data doesn't show up) and I might be able to make some recommendations to speed it up.
The new version of Jet has a performance optimization for NF functions so if you are using a lot of NF functions on your report then you may be able to improve the performance somewhat by upgrading.
Regards,
Hughes -
Jet Reports Historic Posts Please find in attachement the file with formula to retreive tax from sale invoice line and sale cr. memo line. There are not a lot of record in sale cr. memo, so it is not a problme (calculation in some second), but for sale invoice line, it is still too slow (50000 records for one month in sale invoice line).
Database on SQL serve 2005 on Win 2003 svr x32, client Jetreports 2010 10.1.10314.0 both connected on the same LAN 100 Mbps.
Best regards -
Jet Reports Historic Posts Michel,
So you have some NL(Sum) formulas which you could probably add a key with a SUMINDEX for which would make them really fast. Any of the NL(Sum) formulas which do not have a Link= in them on this report should be elligible. Basically if you have this formula:=NL("Sum","Sales Invoice Line","Quantity (Base)","Posting Date",$D$6,"No.",$E10)
You would add a key with your filter fields, Posting Date and No. Then on that key you would add the Quantity (Base) field as a sumindex field. When there is a sumindex which includes all the filter fields, Jet should be able to use the sumindex to do the sum really fast. If you added those for each of your NL(Sum) formulas which do not have a Link= in them it should hopefully make this report a lot faster. Warning: it may take a little time for Nav to build the sum indexes since the tables are probably quite large. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
all other NL formula with sum and wihout link= are not a problem: response time is very fast and it seems that indexe already exists (see list of indexes below).
The only one problem are the two following formulas=NL("sum";"Sales invoice Line";"Amount";"Posting Date";$D$6;"Item Charge Type";"TAX";"Gen. Prod. Posting Group";"<>OM";"Link=";"Sales Invoice Line";"Document No.";"=Document No.";"Line No.";"=Attached to Line No.";"No.";E10)and=NL("sum";"Sales invoice Line";"Amount";"Posting Date";$D$6;"Item Charge Type";"TAX";"Gen. Prod. Posting Group";"OM";"Link=";"Sales Invoice Line";"Document No.";"=Document No.";"Line No.";"=Attached to Line No.";"No.";E10)
Even if indexe has been created (Posting Date,Item Charge Type,Gen. Prod. Posting Group and No), response time is still slow for these two NL sum with link= formulas. Any idea to improve it again ?
Indexes on Sales invoice line :
Enabled Key SumIndexFields
Yes Document No.,Line No. Amount,Amount Including VAT
Yes Blanket Order No.,Blanket Order Line No.
Yes Sell-to Customer No.
Yes Sell-to Customer No.,Type,Document No.
Yes Shipment No.,Shipment Line No.
Yes Job Contract Entry No.
Yes Document No.,Attached to Line No.,Is Item Charge Line Amount
Yes Document No.,WHT Business Posting Group,WHT Product Posting Group
Yes No.,Posting Date,Description,Description 2 Quantity,Quantity (Base),Line Amount,Line Discount Amount
Yes Sell-to Customer No.,Document No.,Shipment No.,Line No.
Yes Posting Date,Item Charge Type,Gen. Prod. Posting Group
Yes No.
Best regards -
Jet Reports Historic Posts Hi Michel,
Nope, can't say I have many more ideas for you. The keys you have there should make this query about as fast as it can be. Unfortunately the table is probably very large and so Jet may have to spin through a lot of records to determine whether each line should be included or not.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
however thank you very much for your cotribution.
I'll try to find another way to retreive these sum of tax, wihout link=
Best regards and thank again
Michel
Please sign in to leave a comment.