Reports on gl gets slow

vyanku

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.

kriki

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?

Savatage

For comparison only.. my G/L Entries table has
11,025,297 Records

70,000 not that much

Slawek_Guzek

vyanku wrote:

What can I do to imporove the performance of gl table and reports.

Think twice before applying 'setcurrentkey on every report' ??

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
Slawek

bbrown

Savatage wrote:

For comparison only.. my G/L Entries table has
11,025,297 Records

70,000 not that much

Tiny...How's 30,526,735

vyanku

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?

kriki

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?

DBCache=1000????????????????????????

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.

Slawek_Guzek

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.
Slawek

vyanku

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?

kriki

vyanku wrote:

One more thing if we apply SETCURRENTKEY on any SETRANGE or SETFILTER in reports. Is it improves the report performance?

Yes, the SETCURRENTKEY defines which key Navision should use to search the records.
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.

Sandeep_Prajapati

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 like

    SETFILTER(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:)

vyanku

But if I apply one key on DATAITEMTABLEVIEW property of report and filtering from another key on report then where should I defin the SETCURRETKEY

Sandeep_Prajapati

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:)

vyanku

Thanks ALL.
I got my answer.
\:D/ :whistle: