Reports on gl gets slow

vyanku
Member Posts: 791
I am working on Native database. In gl table there is huge data total 70,000 entries in gl table.
When I try to run the reports based on gl it opens very slowly. I applied setcurrentkey on every report , I optimised gl table. But the performance is very slow.
What can I do to imporove the performance of gl table and reports.
When I try to run the reports based on gl it opens very slowly. I applied setcurrentkey on every report , I optimised gl table. But the performance is very slow.
What can I do to imporove the performance of gl table and reports.
0
Answers
-
Actually : 70000 records is not that much.
Are you opening the DB directly or through a server?
If you use a server, make sure you have enough DBcache on it. At least a few 100 MB of memory.
What do you mean with "opens very slowly"? Just opening the report or launching it?Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
For comparison only.. my G/L Entries table has
11,025,297 Records
70,000 not that much0 -
vyanku wrote:What can I do to imporove the performance of gl table and reports.
Tell us what exactly is slow, which part of the code. We are damn good but not the Holy Ghost yet to know in advance your problematic code
Regards
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
Actually : 70000 records is not that much.
Are you opening the DB directly or through a server?
If you use a server, make sure you have enough DBcache on it. At least a few 100 MB of memory.
What do you mean with "opens very slowly"? Just opening the report or launching it?
I am opening DB through Server. The DB cache is 1000.
When I run the report Detal ledger and priview it. It takes 15 min to generate all pages.
When I open 17 Gl Entry table and apply Field filter on any field it also filters all values very slowly.
What will be the problem?0 -
vyanku wrote:Actually : 70000 records is not that much.
Are you opening the DB directly or through a server?
If you use a server, make sure you have enough DBcache on it. At least a few 100 MB of memory.
What do you mean with "opens very slowly"? Just opening the report or launching it?
I am opening DB through Server. The DB cache is 1000.
When I run the report Detal ledger and priview it. It takes 15 min to generate all pages.
When I open 17 Gl Entry table and apply Field filter on any field it also filters all values very slowly.
What will be the problem?
This means you have a DB-cache of 1 MB. This means almost NOTHING is ever cached.
Check how much memory you have free in your server.
And increase your DB-cache to almost that value. (you must avoid that Windows starts to swap the cache of Navision to disk)
You can do that by stopping the service in services.msc.
Then in startupparameters, you can put "CACHE=500000" (this is an example that gives 500MB to your cache; the limit generally is around 850MB) and relaunch the service.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
vyanku wrote:I am opening DB through Server. The DB cache is 1000.
1000 ?? :shock: :shock: :shock:
DB cache settings is in kilobytes. The default DB cache is 8000 (8MB) which is dramatically low.
Check free memory on the server running DB server and set the cache size 100 MB (or 100000 kB) less than actual free memory, but no bigger than 950000.
Hope this helps.
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
Thanks . Its working Fine \:D/ .
One more thing if we apply SETCURRENTKEY on any SETRANGE or SETFILTER in reports. Is it improves the report performance?0 -
vyanku wrote:One more thing if we apply SETCURRENTKEY on any SETRANGE or SETFILTER in reports. Is it improves the report performance?
E.g.:
If you filter on "G/L Account No." and don't put a SETCURRENTKEY, Navision has to scan the whole table.
If you put the SETCURRENTKEY to any key starting with "G/L Account No.", Navision has only to scan the key with the "G/L Account No." of your filter, thus limiting the number of records Navision has to read.
PS : with a native DB, the SETRANGE has the possibility to be faster (but NEVER slower) than a SETFILTER. And it is also more readable.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
vyanku wrote:if we apply SETCURRENTKEY on any SETRANGE or SETFILTER in reports. Is it improves the report performance?
Of course YES, but only if the activated key by the SETCURRENTKEY is an appropriate one for the field being filtered by the SETRANGE or SETFILTER. A wrong selection of key would prove to be a slow performance.
Suppose you r going to write somithing likeSETFILTER(A,.....); SETFILTER(B,.....); SETFILTER(C,.....);
Then a key is approprite for the above, if it has fields 'A','B','C' in the same order [A then B then C]. So, a key with structure [x,A,y,z,B,v,d,m,C,f] is also apppropriate as the order of A,B,C in the key is same as applied in the filter in the above code.
I hope I m clear. O:)Sandeep Prajapati
Technical Consultant, MS Dynamics NAV0 -
But if I apply one key on DATAITEMTABLEVIEW property of report and filtering from another key on report then where should I defin the SETCURRETKEY0
-
1) if we are filltering on the fields of DataItem -- > DataItemTableView is the place to choose key (we dont need to change the active key using SETCURRENTKEY)
2) If filtering on some fields of a record Type Variable (say Item) then set key for the Item record variable using SETCURRENTKEY for that Item record vaiable and then do the filtering. .... O:)Sandeep Prajapati
Technical Consultant, MS Dynamics NAV0 -
Thanks ALL.
I got my answer.
\:D/ :whistle:0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions