What is faster on MSSQL: Calcsums or Calcfields?

deblocker

Anyone has done some comparison tests about that?
Like in the example of Mark Brummel posted Fri the Nov 04, 2005, if boths the flowfield and sumindex are related to the same result, what is faster?

ara3n

flowfields uses sumindex fields. they would work the same way.

Marije_Brummel

Both flowfields and calcsums use the SIFT tables.

You only need to use calcsums if you want to filter in a piece of code.

What is the exact situation you are facing?

deblocker

I mean, if there could be a performance gain on MSSQL usig either Calcsums or Calcfields...

DenSter

I don't think there'd be a performance gain, because they both use the same technology. A Flowfield is calculated using CALCSUMS behind the scenes, it uses the same SIFT technology.

Of course it could be possible that CALCFIELDS uses some really efficient version of SIFT that we can't get to, but I really doubt there's a big performance difference.

Marije_Brummel

Just do a test with the client monitor. You will see if there is a difference.

With the clientmonitor you can see the SQL queries.

Let us know the results.

jreynolds

I'm not sure how it works with SQL but with the Navision database I believe that CALCFIELDS will use the first key that can statisfy the request, while with CALCSUMS you can control which key is used.

Marije_Brummel

jreynolds wrote:

I'm not sure how it works with SQL but with the Navision database I believe that CALCFIELDS will use the first key that can statisfy the request, while with CALCSUMS you can control which key is used.

Yes this is true!

I remember changing the order of the keys for making flowfields work! Imagine that.

So that is

Flowfields - Calcsums

0 - 1

bbrown

When you do a CalcFields on a FlowField, Navision actually executes a CalcSums on the underlying table. It uses the first key that contains all the fields being filtered in the FlowField definition. If the same key and filters are specified, I would expect little or no performance difference between the two.

You can influence the key used by adding filters to the FlowField defintion.

With SQL, this key may or may not be the index that is used to retrieve the data.