Flowfields far too slow on SQL-Server

recall

Hello,

I have a general problem with flowfields in Navision 4.0 with SQL-Server.
THEY ARE TOOOOOO SLOW
Especially one problem occurs:

The overall-performance is going down because of the flowfields. Every time I open a form with a table on it wich has many calculated fields it takes a long time to build up the form and to scroll / filter in the table. I tried to build indices, but nothing seems to work. I don't want to redesign the whole application so how can I speed this up ?

It's inexceptably slow!!!!!

Thanks.

kine

Generally - calcfields on list form are performance killers. If you will look into many times refered document Performance troubleshooting guide (w1w1PerfTGuide.pdf), you can read about this problem. And if you want to filter them, it is even worse.

If you need optimize flowfields, you need to create correct keys on the base table (the table from which are the flowfields calculated) - keys with fields with best selectivity which are used in filter of flowfields. If you create such a key (it can be only one field in the key, but may be more to speedup), and the first field in the key will rapidly reduce the range of data needed to read, it will be faster. But if wrong key design lead to that MS SQL server select for example key begining with field of type option or boolean, which have very bad selectivity, you need read for example 50% of table in case of boolean. But if you create key begining for example with customer no., and you are calculating fields per customers, server will read only records for the one customer for which is calculating field and the performance will be better. And do not forget to disable flowfields on work tables (sales lines etc.) because it is faster let MS SQL server calculate the sum than have big SIFT Tables with empty records because deleted documents etc... (if you are not optimizing tables on regulary base)...

DenSter

You can speed things up by removing flowfields from list forms. Who reads those fields while scrolling anyway? Open a card form, or create a detail form that you can run from the table.

kriki

DenSter wrote:

You can speed things up by removing flowfields from list forms. Who reads those fields while scrolling anyway? Open a card form, or create a detail form that you can run from the table.

Or you can make the fields invisible (with the property or maybe even better with in the OnOpenForm-trigger, in this way the next time the form opens, it will invisible again). When a flowfield is invisible on a form it is not calculated and if the user wants to see it, he can make it visible.

recall

Thank you all

I wonder why the C/SIDE can calculate flowfields that fast. My Item table has 20 flowfields and in addition 8 filter fields (I'm NOT the programmer).

But the C/SIDE Server cashes 1000 lines per second (!) while the SQL Server needs 1 second to get only ONE LINE...
So I can't understand this ?!?!

How can the C/SIDE be so fast and the SQL Server so slow ?
In native DB there was no need to optimize the Item table, why now ?

And when I execute a function that goes through all lines in the Item table C/SIDE needs about 1,5 Minutes. SQL Server needs 1 hour for 1 percent!

kriki

recall wrote:

But the C/SIDE Server cashes 1000 lines per second (!) while the SQL Server needs 1 second to get only ONE LINE...
So I can't understand this ?!?!

The Navision-DB has the technique needed to calculate this fast in it. SQL does not have this technique. So he does it with an extra table where all totals are stored OR he searches directly the table. But it should go faster than that. So I think your DB needs some fine-tuning for SQL.

recall wrote:

How can the C/SIDE be so fast and the SQL Server so slow ?
In native DB there was no need to optimize the Item table, why now ?

Optimizing Navision-DB does other things than optimizing SQL-DB.
In short : optimizing Navision-tables is only usefull for tables that don't change a lot. Tables that change a lot (eg. entry-tables) will be faster to read but slower to write, so it's not a good idea to optimize those. Optimizing in SQL is always a good idea.

DenSter

How it exactly happens is not really clear to me, but it has been shown to me once in the developer class. Basically, in the Navision proprietary DBMS, there is technology called SIFT (short for Sum Index Field Technology). With this technology, for ANY flowfield, the DBMS only has to read 3 values. It doesn't need to keep thousands of lines to total, because it only has to read 3 values to calculate it on the fly. Flowfields are not actually values in the database tables, but they are calculation statements that tell SIFT how to get the field value.

Becasue SQL Server does not have SIFT (we are hoping that it will be added though, that will solve most of the SQL performance issues), it "mimics" SIFT by keeping bucket values in special key tables. Instead of just 3 values, the DBMS has to read more values than that. How many I don't know, but it's more than the 3 C/SIDE values, and it is in physically separate tables.