Our Sales Invoice Line Table (113) has almost 1,500,000 records and has become very slow. We are running SQL, and I ran the Client Monitor to try and troubleshoot the issue. I found two sections where the Elapsed Time is high (11328). Can anyone shed a little light on what exactly this is telling me? (Or is my quad 733mhz box the problem) Function Parameter Parameter Number Data COUNT RANGE 1 Table 113 Sales Invoice Line COUNT RANGE 2 Search Method > COUNT RANGE 3 Key Document No.=‘NJ00116544’,Line No.=‘10000’ COUNT RANGE 6 Filter Document No.:NJ00116544 COUNT RANGE 13 Timeout (ms) 200 COUNT RANGE 14 Source Object Table 113 Sales Invoice Line COUNT RANGE 15 Source Trig/Fun GetCurrencyCode() COUNT RANGE 16 Source Line No. 8 COUNT RANGE 17 Source Text EXIT(’’); COUNT RANGE 30 SQL Statement SET SHOWPLAN_ALL ON SELECT * FROM “Inc_$Sales Invoice Line” WHERE ((“Document No.”=‘NJ00116544’)) AND “Document No.”=‘NJ00116544’ AND “Line No.”>10000 UNION ALL SELECT * FROM “Inc_$Sales Invoice Line” WHERE ((“Document No.”='NJ001165 COUNT RANGE 62 Timeout Status 1 COUNT RANGE 100 Elapsed Time (ms) 11328
can you paste the code here ?
Yes, more information is required. The c/al code and a description of what you are doing with the salesinvoicelines.
My guess is a case of breaking the cardinal rule of Navision. i.e. adding a key to the Sales Invoice line, and then runing reports or the like off it. My guess is that moving to a different table will help.
I agree with David, if a request is getting slower the more records are in a table, then in most cases it’s an index problem. You should run this function and monitor it with the SQLProfiler. The amount of page “Reads” indicates if there is a problem; a number greater than 1000 Reads is worth to be investigated. Copy the SQL statement to the QueryAnalyzer (modify the parameter stuff) and look at the “Estimated Execution Plan”. Here you can see, how the SQLServer “behaves”, which indexes are used, if it Seeks or Scans an Index (or even worse the table). If an index is missing, you could create it (ideally in order of the WHERE clause), but I recommend to do that in SQL and not with NAVISION C/SIDE (NAV creates stupid indexes, from a SQL point of view). Additionally: You should care about your index statistics and index page fillfactors! Maintenance is crucial! (Search this forum and you’ll find plenty of advice). Hope this helps a little …
Thanks for the information. I will check into it, and let you know what I find.
Matt, what is the function that is causing the performance issues.
When you choose the “Posted Invoices” option from the Sales menu. Also when in the screen, and you try to search for a specific Invoice number , it can take 10 minutes, or longer to respond.
I would say that there is something on the form causing problems. FOr example some flow fields. Or some code. It caould also be in the slaes line sub form. Either way this looks like a development issue.
To test this, you could create a new form with the wizzard. Create a list form, and based on table 112. sorry table 113 sales line. Add just ONE field, No. then see if running this form is fast. Second, open Form 133 directly from the Object designer and see if it is slow.
I had the same thought, and it is just as slow going straight into the table. Could this be a hardware / network issue?
You need to create new form, runnign the table won’t tell you anything. If it is a flow field issue (which it looks like), then the flow fields will still be calculated when you run the table. Believe me its cheaper to try to create a form (2 min work) than to do a total network rebuild.
PS why are you searching for a sales line by UInvocie number. You chouldbe finding the Sales Invoice Header, and locating the line from there. Am I missing something.
Works just fine using a form with one field on it. This is not my design, I am just trying to troubleshoot the speed. What exactly are you asking me about the search?
Ok thats the information we needed to extract. basically the cause of the issue is probably bad design of flow fields. You are going to need a Navision developer to look at this. It looks like bad design and will probably be diagnosed very fast by a good developer. Are you an employee of the end user, or an external? I am assuming that you are not a Navision developer?
Looks like I found the issue. There is a Flowfield which does a Lookup on a History file, which is 3 time the size of the Invoice Line Table. I removed that field on the development box, and it seems to have resolved the issue. Thanks to everyone for their time and assistance.
The entries you have included from the Client Monitor are issued by c/side to calculate approximate record counts in order to display a vertical scroll bar in lists. There are 2 COUNT RANGE operations every time a scroll bar is needed to be drawn (1 for the up direction and 1 for the down from the current record). This is not the cause of AL code or a CALCFIELD, if you have identified the right operation in the client monitor. It is odd that this would take 11 seconds (implying the ‘up’ direction is taking about the same too). Each COUNT RANGE has a 200ms timeout so that scroll bar logic is not costly, which appears not to be honoured here. You might try to Optimize the Sales Invoice Line table.
Robert, I’m pretty sure its a flow field issue, since removing it fixed the problem. I think the originally posted code was just a red herring.
I guess my original question was about understanding what the Client Monitor was telling me in the code I posted. Then we moved on to troubleshooting the actual problem. Anyway, thanks again for the Help!
Yes I guessed somethign like that, which is why I changed your thread topic, so it reflected two issues. So long as you got it fixed, that’s the important thing.