Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K 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
- 269 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 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
Why does card page retrieve 50 rows from DB and how to prevent it?
igor.chladil
Member Posts: 28
Hello,
In NAV 2016 RTC client I have intuitively expected that the Card page will read only the single record + its flowfields that is just displayed. However it seems that RTC client reads instead 50 records (using SELECT TOP (49) ... and OPTION(optimize FOR unknown, fast 50)). Is it possible to somehow prevent this behaviour?
Card pages may often show a lot of the information (flowfields) on it which may cost quite a lot of the SQL resources to calculate. When reading this information for 50 records when I actually need it for single record displayed on the card makes no sense to me. In my scenario this behaviour costs me almost 30 seconds in performance for EACH record. Even the argument that it reads just in case that I want to go to the next record while staying on the card page doesn't help as NAV actually re-reads fresh 50 records when I go to the next record.
In my example, that card page should be surely optimized in NAV, however if the NAV engine helped me by not reading 50 records that won't be ever used, it would be a big help as well ...
Any ideas how to suppress this behaviour? Are there any options in CustomSettings or anything else I can do easily? If this behaviour is intentional could anyone please explain to me what is the reason behind?
Thanks,
Igor
In NAV 2016 RTC client I have intuitively expected that the Card page will read only the single record + its flowfields that is just displayed. However it seems that RTC client reads instead 50 records (using SELECT TOP (49) ... and OPTION(optimize FOR unknown, fast 50)). Is it possible to somehow prevent this behaviour?
Card pages may often show a lot of the information (flowfields) on it which may cost quite a lot of the SQL resources to calculate. When reading this information for 50 records when I actually need it for single record displayed on the card makes no sense to me. In my scenario this behaviour costs me almost 30 seconds in performance for EACH record. Even the argument that it reads just in case that I want to go to the next record while staying on the card page doesn't help as NAV actually re-reads fresh 50 records when I go to the next record.
In my example, that card page should be surely optimized in NAV, however if the NAV engine helped me by not reading 50 records that won't be ever used, it would be a big help as well ...
Any ideas how to suppress this behaviour? Are there any options in CustomSettings or anything else I can do easily? If this behaviour is intentional could anyone please explain to me what is the reason behind?
Thanks,
Igor
0
Answers
You can change the number of records to grab to a lower value but I think you are not dealing with the real problem.
Reading 50 records takes almost the same time of reading 1 record. If you have 30 seconds to show the card I suspect that something is not optimized in factboxes or flowfields.
Please try this:
In one codeunit put this code
dtmstart := CURRENTDATETIME;
Customer.SETRANGE("No.", '<key>');
Customer.FINDFIRST; // Force SQL to SELECT TOP 1
MESSAGE(FORMAT(CURRENTDATETIME - dtmstart));
dtmstart := CURRENTDATETIME;
Customer.SETRANGE("No.", '<key>');
Customer.FINDSET; // SQL will be SELECT TOP 50
MESSAGE(FORMAT(CURRENTDATETIME - dtmstart));
Also run with sql profiller to see the time taken in SQL.
Regards and good luck.
parm