SETCURRENTKEY, SETFILTER

2»

Comments

  • megawavezmegawavez Member Posts: 133
    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?
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • DenSterDenSter Member Posts: 8,304
    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.
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • BeliasBelias Member Posts: 2,998
    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
    .
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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?
    David Singleton
  • NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
    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.
  • megawavezmegawavez Member Posts: 133
    So FINDSET should only be used on read only queries with an expected result set < 500 entries?
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Alex_ChowAlex_Chow Member Posts: 5,063
    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_SingletonDavid_Singleton Member Posts: 5,479
    Alex Chow 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.
    David Singleton
  • DenSterDenSter Member Posts: 8,304
    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?
  • genericgeneric Member Posts: 511
    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?
  • DenSterDenSter Member Posts: 8,304
    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.
  • BeliasBelias Member Posts: 2,998
    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('-')
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • DenSterDenSter Member Posts: 8,304
    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.
  • BeliasBelias Member Posts: 2,998
    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...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • DenSterDenSter Member Posts: 8,304
    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 :mrgreen:
  • BeliasBelias Member Posts: 2,998
    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 :mrgreen:
    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...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • krikikriki Member, Moderator Posts: 9,094
    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).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • cnicolacnicola Member Posts: 181
    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.
    Apathy is on the rise but nobody seems to care.
  • Lars_WestmanLars_Westman Member Posts: 116
    Dynamic cursos are used since version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
Sign In or Register to comment.