SETCURRENTKEY, SETFILTER

megawavez · 2010-02-16

Huh.. so I've been trying to read up on this:

So..
Fast Forward Cursors: Copy results to a tempdb table and scan through those results
Dynamic Cursors: Run through the existing db (no temp table)

FINDSET() uses a Fast Forward cursor for record set < 500 and Dynamic Cursor for record set >= 500 ?

FIND('-') always uses Dynamic Cursors?

ara3n · 2010-02-16

megawavez wrote:

Huh.. so I've been trying to read up on this:

FINDSET() uses a Fast Forward cursor for record set < 500 and Dynamic Cursor for record set >= 500 ?

FINDSET for the first 500 records does not use a cursor. after 500 records it creates a cursor.

NaviDeveloper_NL · 2010-02-17

You can easyly see the used cursor types from sys.dm_exec_cursors

for example

SELECT session_id,cursor_id,properties,creation_time,is_open,reads,SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as statement
FROM sys.dm_exec_cursors(63) qs -- 0: all session id's, can be replaced by a specific id
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

David_Singleton · 2010-02-17

ara3n wrote:

megawavez wrote:

Huh.. so I've been trying to read up on this:

FINDSET() uses a Fast Forward cursor for record set < 500 and Dynamic Cursor for record set >= 500 ?

FINDSET for the first 500 records does not use a cursor. after 500 records it creates a cursor.

That's interesting. I always thought differently.

I thought that with find set, it goes back and generates the cursor for all the records. By that I mean that if you had a FINDSET that went through 600 records, that it would read the first 500 records normally. Then when it got to 501 would say "oops looks like I need to build a cursor" and then go back and retrieve all 600 records into a cursor. In that case, FIND('-') would be faster than FINDSET on requests over 500 and the other way for requests less than 500.

If what you are saying is correct, i.e. that with FINDSET the first 500 records are searched normally, and then the cursor is created on only the last 100 that would mean that we should ALWAYS uses FINDSET, and NEVER use FIND('-').

My concern is that I have not seen any real performance using findset on large record sets, I have only really seen it make things faster when the record set was small and repeated.

This is all very confusing.

DenSter · 2010-02-17

David Singleton wrote:

If what you are saying is correct, i.e. that with FINDSET the first 500 records are searched normally, and then the cursor is created on only the last 100 that would mean that we should ALWAYS uses FINDSET, and NEVER use FIND('-').

That's how I have always interpreted. The way it was explained to me was that when it gets to record number 501, it 'reverts back to FIND('-') and retrieves the rest of the records "the old way". Since this happens without having the actually write the code, I figured I don't need to think about how big my record set is, I'll just always use FINDSET, for the first 500 that's always faster.

ara3n · 2010-02-17

Here is a link on (jfalkebo) MS on this.

http://mibuso.com/forum/viewtopic.php?f=32&t=30687

I guess somebody needs to do a test and post the results.

Belias · 2010-02-17

Take a look at this manual https://mbs.microsoft.com/partnersource/communities/training/trainingmaterials/student/course80156.htm
chapter 5, page 18

[...]From previous paragraphs, you know that FIND will generate a cursor in code
sample 1, which is to be avoided. Therefore, it is better to use the FINDSET
instruction, as shown in code sample 2. Unlike the FIND('-') command,
FINDSET does not use cursors. When executed, the T-SQL result looks as
follows:
SELECT TOP 500 * FROM ...

and also (page 23)

There is a parameter in Microsoft Dynamics NAV that is used to set up the
maximum number of records retrieved from the database (File, Database, Alter,
Advanced tab, Caching, Record Set = 50). If the set is bigger than the maximum,
Microsoft Dynamics NAV will continue to work but it will replace the reading
mechanism with a dynamic cursor. If there is an indication that this will occur,
use the 'old' FIND('-') command as opposed to FINDSET.

David_Singleton · 2010-02-17

There is a parameter in Microsoft Dynamics NAV that is used to set up the
maximum number of records retrieved from the database (File, Database, Alter,
Advanced tab, Caching, Record Set = 50). If the set is bigger than the maximum,
Microsoft Dynamics NAV will continue to work but it will replace the reading
mechanism with a dynamic cursor. If there is an indication that this will occur,
use the 'old' FIND('-') command as opposed to FINDSET.

See this makes no sense. If in fact the first 500(50) records are read without a cursor, and ONLY the remaining say 100 records use a cursor, then FINDSET would ALWAYS be a better option.

Something just does not add up here.

Is everyone here certain that if I read 600 records with a FINDSET that the cursor is ONLY reading the last 100 records?

NaviDeveloper_NL · 2010-02-17

David Singleton wrote:

See this makes no sense. If in fact the first 500(50) records are read without a cursor, and ONLY the remaining say 100 records use a cursor, then FINDSET would ALWAYS be a better option.

Something just does not add up here.

Is everyone here certain that if I read 600 records with a FINDSET that the cursor is ONLY reading the last 100 records?

After the first resultset of 500, Dynamics NAV creates not just one cursor (as you would expect), but many (something like 10). You can see this with the Profiler.

megawavez · 2010-02-17

So FINDSET should only be used on read only queries with an expected result set < 500 entries?

David_Singleton · 2010-02-17

megawavez wrote:

So FINDSET should only be used on read only queries with an expected result set < 500 entries?

Well 50 in 2009.

So we need to go back and change all the FINDSETs we added in.

Alex_Chow · 2010-02-17

David Singleton wrote:

megawavez wrote:

So FINDSET should only be used on read only queries with an expected result set < 500 entries?

Well 50 in 2009.

It's user define-able.

David_Singleton · 2010-02-17

Alex Chow wrote:

David Singleton wrote:

megawavez wrote:

So FINDSET should only be used on read only queries with an expected result set < 500 entries?

Well 50 in 2009.

It's user define-able.

Yes correct, I should have been more specific.

DenSter · 2010-02-18

I just don't use FIND('-') anymore at all, strictly FINDSET for me.

You want to bet that the next "fix" is to make the whole "cursor type jump" around that number of records go away?

generic · 2010-02-18

DenSter wrote:

I just don't use FIND('-') anymore at all, strictly FINDSET for me.

You want to bet that the next "fix" is to make the whole "cursor type jump" around that number of records go away?

At customer's expense?

DenSter · 2010-02-18

Do you know how much 'expenses' your customer will have to review every single use of FINDSET and do a statistical analysis of how many records they get, so that they can be replaced with other keywords? There are many other things to do that are far more effective at speeding up the system.

Besides, you rewrite those keywords based on record sets of 500, and then MSFT changes the default to 50. Then you rewrite for a value of 50, and the user goes in and changes it to 1000. You can't rely on it.

Belias · 2010-02-18

i profiled a findset(false,false) vs. a Find('-') in a cronus database:

glentry.findset;
repeat
until glentry.next = 0;

glentry.find('-');
repeat
until glentry.next = 0;

FINDSET:

SP:StmtCompleted	SELECT TOP 51 * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)   ORDER BY "Entry No_" 
SP:StmtCompleted	SELECT TOP 51 * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)   ORDER BY "Entry No_" 
SP:StmtCompleted	SELECT  * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)  WHERE  "Entry No_">@P1 ORDER BY "Entry No_" 
SP:StmtCompleted	SELECT  * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)  WHERE  "Entry No_">@P1 ORDER BY "Entry No_" 
SP:StmtCompleted	FETCH API_CURSOR0000000000000010
SP:StmtCompleted	FETCH API_CURSOR0000000000000010
SP:StmtCompleted	FETCH API_CURSOR0000000000000010
...MORE FETCHES...

FIND('-')

SP:StmtCompleted	SELECT  * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)   ORDER BY "Entry No_" 
SP:StmtCompleted	SELECT  * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS Italia S_p_A_$G_L Entry" WITH (READUNCOMMITTED)   ORDER BY "Entry No_" 
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
SP:StmtCompleted	FETCH API_CURSOR0000000000000011
...MORE FETCHES...

i'll just follow what microsoft said in that document...less than recorset -> findset, otherwise find('-')

DenSter · 2010-02-18

At the request of my customer (this was a couple of years back, so not a current customer) I modified some code like that for a customer, because they read mibuso too and they thought that would make a big difference. The opposite was true. They ended up with more blocks and longer durations, so we ended up restoring the code. I don't really care how many cursors it takes to execute a query, if it does the job and performs well to me that's not a problem. I care about the database's performance, and FINDSET has been the winner for me so far.

If the number of cursors is hard on memory, then most customers have the budget and the room to increase memory. Takes $100 and a server reboot. Now you add up how much time it takes to investigate which queries you need to modify, and tell me how high the bill for the customer is going to be.

I'm not saying tweaking the code like that is bad practice, of course optimizing C/AL is going to make a big difference. I'm just saying diving into the code should not be your first step when working on performance issues.

Belias · 2010-02-18

DenSter wrote:

At the request of my customer (this was a couple of years back, so not a current customer) I modified some code like that for a customer, because they read mibuso too and they thought that would make a big difference. The opposite was true. They ended up with more blocks and longer durations, so we ended up restoring the code. I don't really care how many cursors it takes to execute a query, if it does the job and performs well to me that's not a problem. I care about the database's performance, and FINDSET has been the winner for me so far.

If the number of cursors is hard on memory, then most customers have the budget and the room to increase memory. Takes $100 and a server reboot. Now you add up how much time it takes to investigate which queries you need to modify, and tell me how high the bill for the customer is going to be.

I'm not saying tweaking the code like that is bad practice, of course optimizing C/AL is going to make a big difference. I'm just saying diving into the code should not be your first step when working on performance issues.

sorry, i don't understand if you are you talking to me...

DenSter · 2010-02-18

Belias wrote:

sorry, i don't understand if you are you talking to me...

I am not talking to anyone in particular, we're having a discussion, feel free to add your opinion

Belias · 2010-02-18

DenSter wrote:

Belias wrote:

sorry, i don't understand if you are you talking to me...

I am not talking to anyone in particular, we're having a discussion, feel free to add your opinion

Oh, ok...nothing to add, anyway...i am adhering the guidelines micorsoft gave...it's not hard for an average developer to know how much records he can have after setting some filters...if the data increases in an unexpected way (e.g. a sales order of 100 lines) the drawback is not noticeable...moreover, we can set a larger recordset, as a last resource...

kriki · 2010-02-19

DenSter wrote:

I'm not saying tweaking the code like that is bad practice, of course optimizing C/AL is going to make a big difference. I'm just saying diving into the code should not be your first step when working on performance issues.

That should be the last step (most of the time), because most of the time it is the most costly (in time=>in money).

cnicola · 2010-03-10

Hey guys,

Couple of questions I needed some clarifications on:

1) So all 5.00 versions and up use Dynamic Cursors? (I am using 5.00 Update 1 not 5.00 SP1)
2) In this MS post (http://blogs.msdn.com/microsoft_dynamic ... perty.aspx) in the comments they discuss index hinting. I vaguely remember the subject :oops: but not much about it so this could be a very dumb question: how do you control Index hinting (turn it on and off)? I am mainly asking since if it was on then apparently it would affect the behavior from point 1.

Lars_Westman · 2010-03-10

Dynamic cursos are used since version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920

David_Singleton · 2010-03-10

NaviDeveloper NL wrote:

David Singleton wrote:

See this makes no sense. If in fact the first 500(50) records are read without a cursor, and ONLY the remaining say 100 records use a cursor, then FINDSET would ALWAYS be a better option.

Something just does not add up here.

Is everyone here certain that if I read 600 records with a FINDSET that the cursor is ONLY reading the last 100 records?

After the first resultset of 500, Dynamics NAV creates not just one cursor (as you would expect), but many (something like 10). You can see this with the Profiler.

exactly. This is what I expected, and it means that there will be a big difference in the use of FINDSET vs FIND('-') in certain situations.

SETCURRENTKEY, SETFILTER

Comments

Categories