Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 280 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Clarification on SETTABLEVIEW
rsaritzky
Member Posts: 469
Hello all:
Our Vendor Ledger entry table is large (>2,000,000 records). NAV2009R2, SQL2005.
The "Apply Entries" form, when called from the "Vendor Ledger Entries" form, takes a very long time to populate and open. I'm troubleshooting this issue.
What happens (based on observation and running Client Monitor) is that all the open records that are "eligible" to be applied are read, then before control is passed back to the user, there is a very long wait.
Here is an excerpt from Client Monitor. If you look at the 7th line from the bottom, you will see a command "FETCH 5" taking 17587 milleseconds:
Date Time Entry No. Function Name Parameter No Parameter Number Data
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 30 SQL Statement FETCH 5
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 33 SQL Status ID: 545;Reused: 1;Prepared;Dynamic;Restricted;Rows: 5;UpdateNoLocks
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 50 Search Result
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 100 Elapsed Time (ms) 140
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 1 Table 25 Vendor Ledger Entry
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 2 Search Method <
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 3 Key Entry No.='7518401'
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 6 Filter Entry No.:<>7432157, Vendor No.:V13425, Open:Yes
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 15 Source Trigger/Function Form - OnAfterGetCurrRecord()
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 16 Source Line No. 551
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 17 Source Text END;
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 30 SQL Statement SELECT * FROM "MCB$Vendor Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Entry No_"<>?)) AND (("Vendor No_"=?)) AND (("Open"=?)) AND "Entry No_"<? ORDER BY "Entry No_" DESC
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 31 SQL Plan Sort[2,1];Nested Loops[3,2];Index Seek($4)[5,3];Clustered Index Seek(MCB$Vendor Ledger Entry$0)[7,3]
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 32 SQL Index Vendor No.,Open,Positive,Due Date,Currency Code,Entry No.
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 33 SQL Status ID: 562;New;Prepared;Dynamic;Restricted;Rows: 1;UpdateNoLocks
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 50 Search Result <
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 51 Record Found Entry No.='7432243'
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 100 Elapsed Time (ms) 485
11/8/2011 3:58:34.479 PM 118 CALCSUMS 1 Table 380 Detailed Vendor Ledg. Entry
11/8/2011 3:58:34.479 PM 118 CALCSUMS 4 SumIndexFields Amount
11/8/2011 3:58:34.479 PM 118 CALCSUMS 5 Order Vendor Ledger Entry No.,Entry Type,Posting Date,Entry No.
11/8/2011 3:58:34.479 PM 118 CALCSUMS 6 Filter Vendor Ledger Entry No.:7432243
11/8/2011 3:58:34.479 PM 118 CALCSUMS 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:34.479 PM 118 CALCSUMS 15 Source Trigger/Function Form - OnAfterGetCurrRecord()
11/8/2011 3:58:34.479 PM 118 CALCSUMS 16 Source Line No. 551
11/8/2011 3:58:34.479 PM 118 CALCSUMS 17 Source Text END;
11/8/2011 3:58:34.479 PM 118 CALCSUMS 30 SQL Statement SELECT SUM("Amount") FROM "MCB$Detailed Vendor Ledg_ Entry" WITH (READUNCOMMITTED) WHERE (("Vendor Ledger Entry No_"=?))
11/8/2011 3:58:34.479 PM 118 CALCSUMS 31 SQL Plan Compute Scalar[2,1];Stream Aggregate[3,2];Nested Loops[4,3];Index Seek($1)[6,4];Clustered Index Seek(MCB$Detailed Vendor Ledg_ Entry$0)[8,4]
11/8/2011 3:58:34.479 PM 118 CALCSUMS 32 SQL Index Vendor Ledger Entry No.,Posting Date,Entry No.
11/8/2011 3:58:34.479 PM 118 CALCSUMS 33 SQL Status ID: 378;Reused: 8;Prepared;Default;Rows: 1;UpdateNoLocks
11/8/2011 3:58:34.479 PM 118 CALCSUMS 52 Sum 8.5
11/8/2011 3:58:34.479 PM 118 CALCSUMS 100 Elapsed Time (ms)
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 1 Table 25 Vendor Ledger Entry
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 2 Search Method <
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 3 Key Entry No.='7432243'
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 6 Filter Entry No.:<>7432157, Vendor No.:V13425, Open:Yes
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 15 Source Trigger/Function Form - OnAfterGetRecord()
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 16 Source Line No. 534
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 17 Source Text }
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 30 SQL Statement FETCH 5
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 33 SQL Status ID: 562;Reused: 1;Prepared;Dynamic;Restricted;Rows: 5;UpdateNoLocks
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 50 Search Result <
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 51 Record Found Entry No.='7432227'
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 100 Elapsed Time (ms) 17187
11/8/2011 3:58:51.666 PM 120 CALCSUMS 1 Table 380 Detailed Vendor Ledg. Entry
11/8/2011 3:58:51.666 PM 120 CALCSUMS 4 SumIndexFields Amount
11/8/2011 3:58:51.666 PM 120 CALCSUMS 5 Order Vendor Ledger Entry No.,Entry Type,Posting Date,Entry No.
11/8/2011 3:58:51.666 PM 120 CALCSUMS 6 Filter Vendor Ledger Entry No.:7432227
11/8/2011 3:58:51.666 PM 120 CALCSUMS 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:51.666 PM 120 CALCSUMS 15 Source Trigger/Function Form - OnAfterGetRecord()
11/8/2011 3:58:51.666 PM 120 CALCSUMS 16 Source Line No. 534
As far as I can tell, this is an "internal" command by the form (233) - it reads the records to be displayed (apparently in groups of 5), then applies the code in "OnAfterGetCurrRecord" trigger.
There are multiple FETCH commands in the Monitor output, but only the last one is more than 200ms, and it's always 17000+ms. It behaves the same regardless of which vendor or which vendor ledger entry chosen to apply is selected.
The "visual" behavior of the form is that the open records all appear quite quickly - except for the last one. Then there is a 20+ second pause, then the last record appears and control is released to the user.
I haven't been able to see any flaws in the indexes used by SQL in reviewing the Monitor output. I plan on running a SQL Trace next, but this looks like it may be code "internal" to NAV.
Does anyone know why records are being Fetched in groups of 5?
I haven't gone through the code completely to see if FIND('-') instead of FINDSET is being used somewhere previous to this. I know that there are some significant impacts on performance if FIND('-') is used in SQL and you loop through a group of records. However, because this is in a form, I may not be able to control this.
Other info: Our "record set" caching is set to 1000 in Database options.
Is anyone familiar with how this FETCH is controlled, and if I may be able to change it?
Thanks in advance,
Ron
Our Vendor Ledger entry table is large (>2,000,000 records). NAV2009R2, SQL2005.
The "Apply Entries" form, when called from the "Vendor Ledger Entries" form, takes a very long time to populate and open. I'm troubleshooting this issue.
What happens (based on observation and running Client Monitor) is that all the open records that are "eligible" to be applied are read, then before control is passed back to the user, there is a very long wait.
Here is an excerpt from Client Monitor. If you look at the 7th line from the bottom, you will see a command "FETCH 5" taking 17587 milleseconds:
Date Time Entry No. Function Name Parameter No Parameter Number Data
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 30 SQL Statement FETCH 5
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 33 SQL Status ID: 545;Reused: 1;Prepared;Dynamic;Restricted;Rows: 5;UpdateNoLocks
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 50 Search Result
11/8/2011 3:58:33.994 PM 116 FIND/NEXT 100 Elapsed Time (ms) 140
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 1 Table 25 Vendor Ledger Entry
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 2 Search Method <
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 3 Key Entry No.='7518401'
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 6 Filter Entry No.:<>7432157, Vendor No.:V13425, Open:Yes
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 15 Source Trigger/Function Form - OnAfterGetCurrRecord()
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 16 Source Line No. 551
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 17 Source Text END;
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 30 SQL Statement SELECT * FROM "MCB$Vendor Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Entry No_"<>?)) AND (("Vendor No_"=?)) AND (("Open"=?)) AND "Entry No_"<? ORDER BY "Entry No_" DESC
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 31 SQL Plan Sort[2,1];Nested Loops[3,2];Index Seek($4)[5,3];Clustered Index Seek(MCB$Vendor Ledger Entry$0)[7,3]
11/8/2011 3:58:33.994 PM 117 FIND/NEXT 32 SQL Index Vendor No.,Open,Positive,Due Date,Currency Code,Entry No.
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 33 SQL Status ID: 562;New;Prepared;Dynamic;Restricted;Rows: 1;UpdateNoLocks
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 50 Search Result <
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 51 Record Found Entry No.='7432243'
11/8/2011 3:58:34.479 PM 117 FIND/NEXT 100 Elapsed Time (ms) 485
11/8/2011 3:58:34.479 PM 118 CALCSUMS 1 Table 380 Detailed Vendor Ledg. Entry
11/8/2011 3:58:34.479 PM 118 CALCSUMS 4 SumIndexFields Amount
11/8/2011 3:58:34.479 PM 118 CALCSUMS 5 Order Vendor Ledger Entry No.,Entry Type,Posting Date,Entry No.
11/8/2011 3:58:34.479 PM 118 CALCSUMS 6 Filter Vendor Ledger Entry No.:7432243
11/8/2011 3:58:34.479 PM 118 CALCSUMS 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:34.479 PM 118 CALCSUMS 15 Source Trigger/Function Form - OnAfterGetCurrRecord()
11/8/2011 3:58:34.479 PM 118 CALCSUMS 16 Source Line No. 551
11/8/2011 3:58:34.479 PM 118 CALCSUMS 17 Source Text END;
11/8/2011 3:58:34.479 PM 118 CALCSUMS 30 SQL Statement SELECT SUM("Amount") FROM "MCB$Detailed Vendor Ledg_ Entry" WITH (READUNCOMMITTED) WHERE (("Vendor Ledger Entry No_"=?))
11/8/2011 3:58:34.479 PM 118 CALCSUMS 31 SQL Plan Compute Scalar[2,1];Stream Aggregate[3,2];Nested Loops[4,3];Index Seek($1)[6,4];Clustered Index Seek(MCB$Detailed Vendor Ledg_ Entry$0)[8,4]
11/8/2011 3:58:34.479 PM 118 CALCSUMS 32 SQL Index Vendor Ledger Entry No.,Posting Date,Entry No.
11/8/2011 3:58:34.479 PM 118 CALCSUMS 33 SQL Status ID: 378;Reused: 8;Prepared;Default;Rows: 1;UpdateNoLocks
11/8/2011 3:58:34.479 PM 118 CALCSUMS 52 Sum 8.5
11/8/2011 3:58:34.479 PM 118 CALCSUMS 100 Elapsed Time (ms)
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 1 Table 25 Vendor Ledger Entry
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 2 Search Method <
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 3 Key Entry No.='7432243'
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 6 Filter Entry No.:<>7432157, Vendor No.:V13425, Open:Yes
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 15 Source Trigger/Function Form - OnAfterGetRecord()
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 16 Source Line No. 534
11/8/2011 3:58:34.479 PM 119 FIND/NEXT 17 Source Text }
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 30 SQL Statement FETCH 5
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 33 SQL Status ID: 562;Reused: 1;Prepared;Dynamic;Restricted;Rows: 5;UpdateNoLocks
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 50 Search Result <
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 51 Record Found Entry No.='7432227'
11/8/2011 3:58:51.666 PM 119 FIND/NEXT 100 Elapsed Time (ms) 17187
11/8/2011 3:58:51.666 PM 120 CALCSUMS 1 Table 380 Detailed Vendor Ledg. Entry
11/8/2011 3:58:51.666 PM 120 CALCSUMS 4 SumIndexFields Amount
11/8/2011 3:58:51.666 PM 120 CALCSUMS 5 Order Vendor Ledger Entry No.,Entry Type,Posting Date,Entry No.
11/8/2011 3:58:51.666 PM 120 CALCSUMS 6 Filter Vendor Ledger Entry No.:7432227
11/8/2011 3:58:51.666 PM 120 CALCSUMS 14 Source Object Form 233 Apply Vendor Entries
11/8/2011 3:58:51.666 PM 120 CALCSUMS 15 Source Trigger/Function Form - OnAfterGetRecord()
11/8/2011 3:58:51.666 PM 120 CALCSUMS 16 Source Line No. 534
As far as I can tell, this is an "internal" command by the form (233) - it reads the records to be displayed (apparently in groups of 5), then applies the code in "OnAfterGetCurrRecord" trigger.
There are multiple FETCH commands in the Monitor output, but only the last one is more than 200ms, and it's always 17000+ms. It behaves the same regardless of which vendor or which vendor ledger entry chosen to apply is selected.
The "visual" behavior of the form is that the open records all appear quite quickly - except for the last one. Then there is a 20+ second pause, then the last record appears and control is released to the user.
I haven't been able to see any flaws in the indexes used by SQL in reviewing the Monitor output. I plan on running a SQL Trace next, but this looks like it may be code "internal" to NAV.
Does anyone know why records are being Fetched in groups of 5?
I haven't gone through the code completely to see if FIND('-') instead of FINDSET is being used somewhere previous to this. I know that there are some significant impacts on performance if FIND('-') is used in SQL and you loop through a group of records. However, because this is in a form, I may not be able to control this.
Other info: Our "record set" caching is set to 1000 in Database options.
Is anyone familiar with how this FETCH is controlled, and if I may be able to change it?
Thanks in advance,
Ron
Ron
0