Report Runs Very Very Slow

wswai

Hello to all navision expert,

I have a problem here... The report dat i created it runs very very slow... i had added in the setcurrentkey function into the reports but still it runs slow..

can anyone help me to this issue?

Thanks
SW

ara3n

what does the report do? what tables are you using. You have to be more specific, in order for us to help you.

DenSter

Did you make sure that the key you are using in your SETCURRECTKEY is actually a key on the table that you are using?

ngebhard

Don't you get an error message if it's not?
I think so!

DenSter

No not if there is a key with those fields. I don't know exactly when it does or doesn't accept it, but I have seen reports that are using keys that don't exist. Of course I NEVER make that mistake anymore :whistle:

ngebhard

Okay! Got it...

But since we don't know what kind of report runs slow, there is no way to give any good hints...

kine

If there is no such a key (or key beginning with same fields in same order, the actual key on table can be longer), you get error message when you run the report.

bylle

Are you running native or sql? If you are running on a sql server it could be possible that the key you are using don't have MaintainSQLIndex and MaintainSIFTIndex enabled. If so - and the table you are running on contains a lot of data - the report would be slow if the sql server can't find a key that would fit you filters.

So if you are running sql, then try checking your key for the flags.

Also remember to set your filters, in the same order as the key. If your report is modifying records, make sure it does not modify your filters - if so, make the modify on a local var.

Another tips - is to run it with the client monitor active, there you will see what is taking time.

That was just some few tips - if none of these are helping you - then please specify:

1) What is the report doing
2) Native / SQL
3) Which tables is it running on
4) Some code example - where you suspect it to run slow

/Bylle

wswai

Hello, I read thru ur quote, can i know in order to make the reports to run faster, i have to use those keys that is set under the table? coz actually most of the report especially on the machine cost for my project ... it runs very very slow although i have set the setcurrentkey. I follow the order of the setrange and setfilter but still it is very very slow.

Is there any other way besides adding setcurrentkey that i could do to make my report run faster??Hope that i can get ur reply soon... Thanks

DenSter wrote:

No not if there is a key with those fields. I don't know exactly when it does or doesn't accept it, but I have seen reports that are using keys that don't exist. Of course I NEVER make that mistake anymore :whistle:

kine

If it is on MS SQL server, and you are using FlowFields, you can add apropriate (with good selectivity) keys on the tables, from which the FlowFields are calculated.

wswai

Dear Bylle,

My report dat run very very slow is using native, Not using SQL. i think is because there is a lot of the calculation and filtering doing behind the report and there is a lot of records. For tables i have take about three tables for my reports.

I had done the setcurrentkey for each setrange and setfilter but the reports still run very very slow.

So is there any way that i can do to make my reports run faster?? Hope to get ur reply soon.

Thanks

bylle wrote:

Are you running native or sql? If you are running on a sql server it could be possible that the key you are using don't have MaintainSQLIndex and MaintainSIFTIndex enabled. If so - and the table you are running on contains a lot of data - the report would be slow if the sql server can't find a key that would fit you filters.

So if you are running sql, then try checking your key for the flags.

Also remember to set your filters, in the same order as the key. If your report is modifying records, make sure it does not modify your filters - if so, make the modify on a local var.

Another tips - is to run it with the client monitor active, there you will see what is taking time.

That was just some few tips - if none of these are helping you - then please specify:

1) What is the report doing
2) Native / SQL
3) Which tables is it running on
4) Some code example - where you suspect it to run slow

/Bylle

bylle

Hi wswai,

Are you modifying any records during the report run - or is it only showing data? If you are modifying be sure to not modify the filters.

If your report is using a lot af flowfields - then try to build some alternative fields, which you can use as sum fields. Calcsum are a lot faster than calcfields.

You dont have to use setcurrentkey, if you are sure that dataitem for each table has the right proberty (DataItemTableView - here you should set the key).

The data you are collecting - must it be shown as a print out (report)? I am only asking because, if you could use them as a csv file, then use a codeunit to create the file - codeunits are a lot faster then reports.

If you still have problems then please give me some more details. E.g. what are you calculating and how are you doing it. From which tables are you collecting the data etc...

/Bylle

kine

1) Do not forget, that your report is run on the client. You need to have enough physical RAM else Navision will write to HDD...

2) Do not forget, that it is good to sometime use Optimize function on the tables.

3) Check what is doing your server - CPU utilization, Physical disc Write and Read queue and others performance pointers...

4) Check what is doing your client - CPU, RAM, HDD...

wswai

Hello Bylle,

Well, I can't really tell u how is the report doing. and i also is new to navision... coz the rpt is not created by me, is created by other people. Well, as what i know is she created the report and it involve a lot of tables.

Thanks,

bylle wrote:

Hi wswai,

Are you modifying any records during the report run - or is it only showing data? If you are modifying be sure to not modify the filters.

If your report is using a lot af flowfields - then try to build some alternative fields, which you can use as sum fields. Calcsum are a lot faster than calcfields.

You dont have to use setcurrentkey, if you are sure that dataitem for each table has the right proberty (DataItemTableView - here you should set the key).

The data you are collecting - must it be shown as a print out (report)? I am only asking because, if you could use them as a csv file, then use a codeunit to create the file - codeunits are a lot faster then reports.

If you still have problems then please give me some more details. E.g. what are you calculating and how are you doing it. From which tables are you collecting the data etc...

/Bylle

kine

Ok, if it is working with many tables, it can be slow. Try to redesign the report to use less tables or not nested in many levels... because do not forget, that each nested level multiply the count of loops...

wswai

Hello Kine and other Navision expert,

Well, i have a question here, i have a report that need grouping.

Eg. Item Quantity
1 10
2 20
3 30

What i need is to group item 1 and 3 together and display result under item 1. Below is the output that it should be.

Output: Item Quantity
1 40
2 20
Hope dat u can help me. Thanks

wswai

Hello Kine and other Navision expert,

Well, i have a question here, i have a report that need grouping.

Eg. Item Quantity
1 10
2 20
3 30

What i need is to group item 1 and 3 together and display result under item 1. Below is the output that it should be.

Output: Item Quantity
1 40
2 20
Hope dat u can help me.
Thanks