SETCURRENTKEY, SETFILTER

megawavez

I'm upgrading to Client v5.0. Is it still recommended to SETCURRENTKEY & SETFILTER for all key fields for optimal SQL performance or is Nav/SQL now smart enough to figure that stuff out? Thanks,

Mega

kriki

It is best to ALWAYS use the best SETCURRENTKEY.
Especially because some versions use a cursortype that uses the index that best fits the ORDER BY in SQL and not the WHERE.

megawavez

kriki wrote:

It is best to ALWAYS use the best SETCURRENTKEY.
Especially because some versions use a cursortype that uses the index that best fits the ORDER BY in SQL and not the WHERE.

Thanks - now, what about setting filters for ALL keys?

So something like:
salesLine.SETCURRENTKEY("Document Type", "Document No.", "Line No.");
salesLine.SETRANGE("Document Type", xxx);
salesLine.SETRANGE("Document No.", xxx1);
salesLine.SETFILTER("Line No.", '') <---- Is this necessary ?

DenSter

No, that's not necessary. It's also not necessary to put the filters in the same order as the key fields (although I like it from a best practice point of view).

What IS necessary is that you use the actual key in SETCURRENTKEY. So if you have a key for "Document Type", "Document No." and "Line No.", then you MUST include all of those fields in your SETCURRENTKEY command. Only including the first 2 fields will compile, and it will run, but SQL Server might revert to clustered index scan if the exact key in the ORDER BY statement (which is generated by SETCURRENTKEY) does not exist. So, even if there is an index for "Document Type", "Document No.", "Line No." on SQL Server, it might still revert to clustered index scan if you only supply the first two fields in the ORDER BY statement.

Also, having a different SQLIndex is counterproductive now. With the type of cursor that NAV now uses it will revert back to clustered index scans if the exact key in the SETCURRENTKEY command does not exist as an index on SQL Server.

megawavez

That's kind of how I thought things worked and thanks !

David_Singleton

Keep in mind though that this is all version dependent. Different hot fixes do things different ways. So you need to test the exact build you are using to see how it works, and worst of all none of this is documented.

Lars_Westman

If You take a look at what You get in client monitor You will see if the cursor is Fast Forward (*should* be version 4 and older) or a Dynamic Cursor.

When working with Dynamic Cursors the Query Optimizer will tend to choose index based on the where clause. When the cursor is Fast Forward it's the order-by that decides the index (at least should be).

No matter what cursor type it's allways a good practice to use SETCURRENTKEY.

DenSter

Lars Westman wrote:

When working with Dynamic Cursors the Query Optimizer will tend to choose index based on the where clause. When the cursor is Fast Forward it's the order-by that decides the index (at least should be).

I think you have that reversed. With Dynamic cursors, it forces SQL Server to use the index in the ORDER BY clause.

David_Singleton

I think the core issue here is that the SQL team keep saying "let SQL decide how to search for data, its smart and knows what it is doing" then we have the Navision team, saying that they are heading more and more to doing things the SQL way, and then they do this.

The worst thing is that for experienced Navision developers and users we can work around this and optimize the systems. but the current certification program encourages people with no experience to be handling all this. So it actually becomes even more important that we leave all these decision to SQL based on statistics.

to me it just all seems a step backwards and a huge step backwards at that.

Lars_Westman

DenSter wrote:

I think you have that reversed. With Dynamic cursors, it forces SQL Server to use the index in the ORDER BY clause.

Of course I have! ](*,) . Must blame it on friday afternoon. Thank's for beeing awake Daniel =D>

Here's the info from the NAV Team on this subject: http://blogs.msdn.com/nav/archive/2009/ ... types.aspx

megawavez

Lars Westman wrote:

If You take a look at what You get in client monitor You will see if the cursor is Fast Forward (*should* be version 4 and older) or a Dynamic Cursor.

When working with Dynamic Cursors the Query Optimizer will tend to choose index based on the where clause. When the cursor is Fast Forward it's the order-by that decides the index (at least should be).

No matter what cursor type it's allways a good practice to use SETCURRENTKEY.

Excuse my ignorance - "Fast Forward" / "Dynamic" cursor?

DenSter

David Singleton wrote:

to me it just all seems a step backwards and a huge step backwards at that.

So true... While in many implementations the move to dynamic cursors can make a HUGE difference, we've lost a few very important tools to improve performance.

In a lot of projects that have included major index tuning, moving to dynamic cursors means that most of the index tuning will need to be largely undone (removing SQLIndex values, re-enabling SQL indexes for instance). It is still possible to tune indexes, but now we also need to make code changes to really make a difference, which means extensive additional testing.

It is really unfortunate that performance improvement has been made so much more difficult.

David_Singleton

DenSter wrote:

It is really unfortunate that performance improvement has been made so much more difficult.

Well only unfortunate for the customer? I mean its good for everyone else isn't it :shock: :-#

DenSter

David Singleton wrote:

Well only unfortunate for the customer? I mean its good for everyone else isn't it :shock: :-#

Let me get my sarcasm bowl out again

Seriously though, I think it's unfortunate for us too, because something that we were able to do in just a 2-3 days is now virtually impossible without turning it into extensive development. I don't know about you, but I don't like spending weeks on something that I used to be able to do in days. That's not progress in my opinion.

NaviDeveloper_NL

DenSter wrote:

So true... While in many implementations the move to dynamic cursors can make a HUGE difference, we've lost a few very important tools to improve performance.

The books I read tell me that dynamic cursors are the slowest compared to the other types. Can you give me an example of a situation where dynamic cursors are faster?

DenSter

It's not the type of cursor that is slow, it's the fact that SQL Server will try to use an index that matches the ORDER BY clause exactly, and if there is not such an index, it will revert back to a clustered index scan, even when there is another index that closely resembles the fields in the ORDER BY clause. As long as you make sure that the ORDER BY exactly matches an existing index (or reversely as long as you make sure there is an index that matches the ORDER BY clause), dynamic cursors will perform very well.

Microsoft has removed all SQLIndex values from all keys, and made sure that SETCURRENTKEY statements match existing indexes, and in these cases, the move to dynamic cursors actually make most NAV implementations perform very well. The thing that I don't like about it is that this way you end up having to modify C/AL code, for implementations that are out of the ordinary (larger databases, higher than average transaction volume, etc.). We don't have the option to simplify SQL indexes anymore, because not having an index that matches the ORDER BY clause will cause most queries to go back to a clustered index scan. We used to be able to make HUGE improvements just by tuning indexes, and this is not possible anymore, not in the same extent. Now we can still add a simple index (or at least less complex), but then we also have to go in and match the ORDER BY clause in every object that uses that index.

As an example, we had removed the SQLIndex from the Reservation Entry table, modified the C/AL code so that the SETCURRENTKEY statement exactly matched the index, and the query duration went from around 200ms down to 0. I had to adjust my duration filter in the SQL Profiler to even get the queries to register. Lars Lohndorf wrote a blog about it, check it out here.

NaviDeveloper_NL

DenSter wrote:

It's not the type of cursor that is slow, it's the fact that SQL Server will try to use an index that matches the ORDER BY clause exactly, and if there is not such an index, it will revert back to a clustered index scan, even when there is another index that closely resembles the fields in the ORDER BY clause. As long as you make sure that the ORDER BY exactly matches an existing index (or reversely as long as you make sure there is an index that matches the ORDER BY clause), dynamic cursors will perform very well.

Microsoft has removed all SQLIndex values from all keys, and made sure that SETCURRENTKEY statements match existing indexes, and in these cases, the move to dynamic cursors actually make most NAV implementations perform very well. The thing that I don't like about it is that this way you end up having to modify C/AL code, for implementations that are out of the ordinary (larger databases, higher than average transaction volume, etc.). We don't have the option to simplify SQL indexes anymore, because not having an index that matches the ORDER BY clause will cause most queries to go back to a clustered index scan. We used to be able to make HUGE improvements just by tuning indexes, and this is not possible anymore, not in the same extent. Now we can still add a simple index (or at least less complex), but then we also have to go in and match the ORDER BY clause in every object that uses that index.

As an example, we had removed the SQLIndex from the Reservation Entry table, modified the C/AL code so that the SETCURRENTKEY statement exactly matched the index, and the query duration went from around 200ms down to 0. I had to adjust my duration filter in the SQL Profiler to even get the queries to register. Lars Lohndorf wrote a blog about it, check it out here.

Dynamic cursors have the advantage of having less impact on tempdb. But should this be a good reason to use this cursor type? A dynamic cursor cannot sort, so it may need other indexes than the fast_forward cursors.

The book "SQL Server 2008 Query Performance Tuning Distilled" says "The dynamic cursor is absolutely the slowest possible cursor to use in all situations. ..."

Why was this change made? The functionality of a dynamic cursor is not needed in NAV I think.

It should be possible to avoid the ORDER BY in cases where this is not necessary.

"the move to dynamic cursors actually make most NAV implementations perform very well" --> How is this possible?

DenSter

NaviDeveloper NL wrote:

The book "SQL Server 2008 Query Performance Tuning Distilled" says "The dynamic cursor is absolutely the slowest possible cursor to use in all situations. ..."

You keep saying that, but if dynamic cursors are so slow, then explain to me how we can see query duration of 0ms? I hadn't even seen performance that good on that table when it was using fast forward cursors.

First of all, you can't just take one little quote from some book and apply it to all uses of dynamic cursors, it seems to me that this statement was made under a certain context that does not always apply to NAV databases. Under the right circumstances, dynamic cursors are REALLY fast, and most of the standard app is modified to meet those circumstances. You need to understand that NAV databases are not like other SQL Server databases. The application was not created for SQL Server but for a proprietary DBMS, and most improvements we have seen for SQL Server are efforts to make SQL Server act like the old NAV native DBMS.

NaviDeveloper NL wrote:

Why was this change made? The functionality of a dynamic cursor is not needed in NAV I think.

I don't know why they do anything, I am not part of the NAV team. Maybe you should drive to Vedbeak and explain this to them

NaviDeveloper NL wrote:

"the move to dynamic cursors actually make most NAV implementations perform very well" --> How is this possible?

Because most SETCURRENTKEY statements in the standard objects (which determine the ORDER BY clause) have been matched exactly to physical indexes, and as a result the actual indexes are used, it hardly ever reverts to clustered index scans, and those queries perform very well.

NaviDeveloper_NL

I did some tests. I executed this code

if find('-') then repeat until next = 0;

on table "G/L Budget Entry" in a standard Cronus database it created a cursor with many fetches.

In 4.0SP3 I get
cursor creation: reads 2 duration 0 - 976 rowcount 10
fetches (total): reads 30 duration 15.000 - 15.895 Rowcount 1718

In 5.0SP1 I get:
cursor creation: reads 22 duration 976 - 1953 rowcount 10
fetches (total): reads 3475 duration 55.000 rowcount 1718

Of course, this is a bad example since I should use FINDSET. But the new cursors are slower in this case.

generic

NaviDeveloper NL wrote:

Of course, this is a bad example since I should use FINDSET. But the new cursors are slower in this case.

you should not use FINDSET, IF the number of record returned is greater than 500 < 2009 or 50 in 2009.

for GL entry you should be using find('-') if number of record return is greater than setup in db setup.

David_Singleton

Navi Developer, you are also forgetting that its critical to know what version you are comparing.

You need to quote the build number for you tests to have any meaning.

http://wiki.dynamicsbook.com/index.php?title=Build_No

DenSter

generic wrote:

you should not use FINDSET, IF the number of record returned is greater than 500 < 2009 or 50 in 2009.

I did some simple tests, measuring the difference in query performance between FIND('-') and FINDSET, and found that it makes no significant difference. So maybe at the 500th record it reverts back to "the old FIND('-')", no reason to suddenly change all your coding habits, if it were for the simple reason that up until the 500th record it would be faster to use FINDSET.

I just don't ever use FIND('-') anymore. BUT that's another discussion anyway.

DenSter

NaviDeveloper NL wrote:

the new cursors are slower in this case.

Again, making general statements based on one specific test. Did you use the same database with different version exes, or did you use the standard databases that came with those versions?

Not ALL queries are fast, just the ones where the SETCURRENTKEY has been matched with an actual physical index. There are plenty of places in the standard app where this has not been done yet, and in those cases you're going to find very bad performance.

Look, I'm not saying these dynamic cursors are good (I HATE that we don't have the capability to tweak indexes the way we used to anymore), but you can't deny that in some of the most crucial places (surrounding warehouse entries, reservation entry, things like that) performance is very good with the dynamic cursors. I was absolutely stunned when my reservation entry query came in with 0ms duration. It was completely the opposite from what I had expected, but it was true nonetheless, and so dynamic cursors are simply not the evil that some people are saying.

And yes, this applies only to 5.0 SP1 and up, which is when dynamic cursors were introduced.

David_Singleton

DenSter wrote:

And yes, this applies only to 5.0 SP1 and up, which is when dynamic cursors were introduced.

Are you sure? I thought Dynamic Cursors were also added to the later hot fixes on both 4sp3 and 5.00

generic

DenSter wrote:

I just don't ever use FIND('-') anymore. BUT that's another discussion anyway.

Yes it's another discussion but MS has the code with find('-') in many places where they follow the logic I've mentioned.

They (MS) also mentioned in one of the post here on MIBUSO, so I will follow their advice instead.

Lars_Westman

David Singleton wrote:

DenSter wrote:

And yes, this applies only to 5.0 SP1 and up, which is when dynamic cursors were introduced.

Are you sure? I thought Dynamic Cursors were also added to the later hot fixes on both 4sp3 and 5.00

Quote from NAV Team Blog:

One of the changes in Microsoft Dynamics NAV version 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920

NaviDeveloper_NL

generic wrote:

NaviDeveloper NL wrote:

Of course, this is a bad example since I should use FINDSET. But the new cursors are slower in this case.

you should not use FINDSET, IF the number of record returned is greater than 500 < 2009 or 50 in 2009.

for GL entry you should be using find('-') if number of record return is greater than setup in db setup.

You are right, I did some tests with FINDSET. For the first 500/501 records a sp_prepare is used (no cursor). After that I see many sp_cursorprepare and sp_cursorclose. For me one cursors seems enough. Very strange.

NaviDeveloper_NL

David Singleton wrote:

Navi Developer, you are also forgetting that its critical to know what version you are comparing.

You need to quote the build number for you tests to have any meaning.

http://wiki.dynamicsbook.com/index.php?title=Build_No

I am talking about the build numbers from the product cd with no hotfixes applied.

NaviDeveloper_NL

DenSter wrote:

generic wrote:

you should not use FINDSET, IF the number of record returned is greater than 500 < 2009 or 50 in 2009.

I did some simple tests, measuring the difference in query performance between FIND('-') and FINDSET, and found that it makes no significant difference. So maybe at the 500th record it reverts back to "the old FIND('-')", no reason to suddenly change all your coding habits, if it were for the simple reason that up until the 500th record it would be faster to use FINDSET.

I just don't ever use FIND('-') anymore. BUT that's another discussion anyway.

If you have a lot of users in the system, this can be a big difference. What did you measure? You should not measure time only, because this is not a system resource, but for example IO (reads) and CPU is.

NaviDeveloper_NL

DenSter wrote:

NaviDeveloper NL wrote:

the new cursors are slower in this case.

Again, making general statements based on one specific test. Did you use the same database with different version exes, or did you use the standard databases that came with those versions?

I had to began with something. Testing one specific case is for me practical.

I used the databases that came with those versions.

Tables and query plans were the same.

NaviDeveloper_NL

NaviDeveloper NL wrote:

DenSter wrote:

NaviDeveloper NL wrote:

the new cursors are slower in this case.

Again, making general statements based on one specific test. Did you use the same database with different version exes, or did you use the standard databases that came with those versions?

I had to begin with something. Testing one specific case is for me practical.

I used the databases that came with those versions.

Tables and query plans were the same.