fbpx
Welcome, Guest
Username: Password: Remember me
Visual Objects

Please use this forum to post questions about Visual Objects and Vulcan.NET
  • Page:
  • 1

TOPIC: Setorder & speed

Setorder & speed 11 May 2020 12:34 #14520

  • ic2's Avatar

  • ic2

  • Topic Author


  • Posts: 672
  • In our programs we often use a method Ordseek (according to my comments I got it from Robert himself from the NG) to seek with a an index tag passed to the function. Ordseek uses this order and then switches back to the original order so any code in the program relying on an earlier SetOrder on the same database (object) won't lose the order.

    My employee gradually stopped using it. She said she found that 2 SetOrder calls, in this method, impacted execution speed of the program considerably, especially in loops of course.

    Can anyone confirm that? Is there an alternative (besides -once- setting the order for every seek anyway)?

    See below our OrdSeek

    Dick

    METHOD OrdSeekN(uZoek AS USUAL,cSeekOrder AS STRING,lSoft AS LOGIC ) AS LOGIC PASCAL CLASS IC2DBServer
    // RvdH NG 18-7-2007
    //#s Seeks in a dbserver with the given order and restores (ER 062000)
    //#s
    //#p uZoek seekvalue, cSeekOrder cdx order name of the database, lSoft logic true when softseek is required
    //#r True when found, false when not found
    //#e self:Server:OrdSeek(cSeek,"adresnm")
    //#e

    LOCAL cOrder AS STRING
    LOCAL lFound:=FALSE AS LOGIC
    cOrder:=SELF:OrderInfo(DBOI_NAME) // Save current order
    SELF:SetOrder(cSeekOrder)
    lFound:=SELF:SEEK(uZoek,lSoft) // Search
    IF cOrder != NULL_STRING
    SELF:SetOrder(cOrder) // Restore current order
    ENDIF
    RETURN lFound

    Please Log in or Create an account to join the conversation.

    Setorder & speed 11 May 2020 13:59 #14521

  • lumberjack's Avatar

  • lumberjack


  • Posts: 680
  • Dick,

    ic2 wrote: In our programs we often use a method Ordseek (according to my comments I got it from Robert himself from the NG) to seek with a an index tag passed to the function. Ordseek uses this order and then switches back to the original order so any code in the program relying on an earlier SetOrder on the same database (object) won't lose the order.
    My employee gradually stopped using it. She said she found that 2 SetOrder calls, in this method, impacted execution speed of the program considerably, especially in loops of course.
    Can anyone confirm that? Is there an alternative (besides -once- setting the order for every seek anyway)?
    See below our OrdSeek

    Do you really have to set your order every time you do a seek?
    I have a stack that I push previous orders to, with RecNo, then do the process according to the order required and pop it at the end.

    So in essence, I have a PushOrder(cOrder), do process, PopOrder() which reset to previous order and set RecNo back to what it was before.

    HTH,
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    Setorder & speed 11 May 2020 15:38 #14522

  • mainhatten's Avatar

  • mainhatten


  • Posts: 139
  • Dick,
    I cannot comment on xSharp/Dotnet perf (yet...) but a large part in this millenium I had to optimize vfp programs.
    First step is always to verify slowdown occurs in estimated position and get hard numbers.
    Windows tool of old are QueryPerformance* functions accessing high res timer, quick search for dotnet found
    stackoverflow.com/questions/163022/high-resolution-timer-in-net
    and do basic measurements yourself
    time between manual input and return as max time
    divide called program parts and find slow spots, getting # of calls and duration.

    Profilers for such work can be great, but unless you worked with it you cannot estimate Heisenberg-effects introduced by the tool (one area where vfp sucks, the profiler bends normal runtime so much you are led astray). If there really is a hotspot where employee estimated, give us numbers AND basic hardware tasked with table description. In vfp on local tables this should never be a problem - but if it is a remote table (or one of the SQL mappings I read about) it might be a hint. SSD vs. HD (including HW cache size) welcome info: esp. on remote tables in single use NOT having index file on remote table helps a lot, as walking the index tree is done without TCP/IP overhead and only final go recno() occurs across TCP/IP. You set order to Adres, in such fields len can be large, leading to large index size: sometimes hashing and searching for small hash entry is faster, even if you have to check for false positives.

    And of course a description what is happening why helps - in your code you never check if you are already on correct order, do you do/know that when calling ? The more you describe, the more specific the help (and in writing sometimes solution pops up in own brain)

    regards
    thomas

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    Setorder & speed 12 May 2020 12:18 #14530

  • ic2's Avatar

  • ic2

  • Topic Author


  • Posts: 672
  • Hello Johan, Thomas,

    Thanks for your replies. I agree that Ordseek isn't always needed, but it is the safest way. You can not forget a SetOrder and if you use a globally opened database, there is no chance that a setorder elsewhere on this same dbf ruins the working of your code (which I recently saw with code I wrote and an employee of mine used - and "ruined" as described.

    Basically if SetOrder has hardly influence on the program execution speed I prefer to keep using it. VO doesn't have a profiler but I could write one. I can imagine that someone already knows that, hence my question.

    And we really do need SetOrder to seek in a specific order right?

    Maybe Robert can comment (SetOrder was published by you)?

    Dick

    Please Log in or Create an account to join the conversation.

    Setorder & speed 12 May 2020 14:31 #14531

  • lumberjack's Avatar

  • lumberjack


  • Posts: 680
  • Hi Dick,

    ic2 wrote: Basically if SetOrder has hardly influence on the program execution speed I prefer to keep using it. VO doesn't have a profiler but I could write one. I can imagine that someone already knows that, hence my question.
    And we really do need SetOrder to seek in a specific order right?

    I am trying to understand how you code that you need to SetOrder that much.

    My basic coding principle is:
    LOCAL cLoScope, cHiScope, cOrder, cOldOrder, cRecNo
    nRecNo := oServer:RecNo
    cOldOrder := ...
    SetOrder(cOrder)
    SEEK cLoScope
    WHILE !oServer:Eof .AND. oServer:GetKey() >= cLoScope .AND. oServer:GetKey() <= cHiScope
      // Do what you need
      oServer:Skip()
    ENDDO
    SET ORDER cOldOrder
    GOTO nRecNo

    Regards,
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    Setorder & speed 12 May 2020 15:18 #14532

  • Karl-Heinz's Avatar

  • Karl-Heinz


  • Posts: 547
  • Hi Dick,

    where is the db located, on a local or a network drive ? Makes that any difference ? I´ve tried it with a local 100000 record dbf. The cdx has two orders.

        SELF:Server:SetOrder ( 2 ) 
        
        SELF:server:Suspendnotification()		
    
        f := Seconds() 
    
    
    	FOR i := 1 UPTO 20
    
    		IF lDoItLikeDick
    
    			SELF:server:SetOrder ( "order2" ) 	
    			? SELF:server:OrdSeekN("A", "order1", .f. ) 
    
    		ELSE
    
    
    	 		cOrder := SELF:server:OrderInfo(DBOI_NAME)
    			? "Current order", cOrder         
    			? "SetOrder()" , SELF:Server:SetOrder ( 1 ) 
    			? "New Order" , SELF:server:OrderInfo(DBOI_NAME)
    			? "Seek ( 'A')" , SELF:Server:Seek ("A")		
    			? "Setorder()", SELF:server:SetOrder ( cOrder )
    			? "restored order" , SELF:server:OrderInfo(DBOI_NAME)  
    			?		
    
    		ENDIF
    		    
    	NEXT 
    
    	SELF:server:ResetNotification()		
    	
    	?
    	? "Elapsed" , Seconds() - f , "secs"  

    No matter which version i use the console output is always correct, and the times elapsed are more or less the same.

    notifications suspended: 0,04 secs
    notifications not suspended: 1,52 secs - This is because the DataBrowser is refreshed continuously :-)

    regards
    Karl-Heinz

    Please Log in or Create an account to join the conversation.

    Setorder & speed 12 May 2020 17:03 #14534

  • robert's Avatar

  • robert


  • Posts: 1676
  • Dick,

    ic2 wrote: Hello Johan, Thomas,
    Maybe Robert can comment (SetOrder was published by you)?
    Dick


    In principle there is nothing wrong with your code, but it requires some extra work on the side of the RDD.
    Assume your file has 2 records and 2 fields,
    # NUMBER and NAME
    1 12345 Dick
    2 67890 Robert
    Now assume your index has 2 tags, also on number and name
    When the index is on the tag NUMBER and you are using the code that you wrote to switch to NAME and seek for Robert and the original record pointer is on the 1st row then this is what is going to happen:
    1) you switch the order to the tag Name
    2) the RDD will evaluate the key for record #1Dick and will lookup that key in the NAME tag to make sure that a subsequent skip or something like that will start at the right offset
    3) You perform the seek for "Robert" and the record pointer is moved to # 2
    4) Then you select the tag NUMBER again and the RDD will evaluate the index expression for NUMBER and will seek in that tag to position on key 67890 for record 2.

    So the reselecting of the original tag triggers an extra search in the index.
    That is normally not a problem, unless your index contains very large keys or many duplicate keys, In that case the extra search may cause a bit of extra time.
    In general I would advise to choose an index key that will not produce many duplicate keys. For example if you key is on an "flag" field of for example 1 character, then make that key unique by adding Str(Recno(),10) to it. That will make the key a bit larger but also a bit faster to work with.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Setorder & speed 12 May 2020 17:52 #14538

  • ic2's Avatar

  • ic2

  • Topic Author


  • Posts: 672
  • Hello Karl-Heinz, Robert,

    Thanks for your replies.

    I have modified your sample to the program below and setting 1 order before a loop compared to Ordseek inside a loop (meaning 2 SetOrders) makes a difference of 25% which is (only) a bit over a second in 100.000 iterations.

    But according to my client & employee who stopped using SetOrder the difference is dramatically more on a server within a bBrowser. They now open separate databases multiple times with a preset order for each and just use the appropriate database-object when they need the order and see the difference.

    I will tell them that the difference may be explained with the existence of duplicate keys so dealing with these may yield more than their solution (alone).

    Dick

    LOCAL f AS FLOAT
    LOCAL i AS DWORD
    f := Seconds()

    // With 0 in the loop setorder: 5,36
    // With 1 setorder in the loop: 5,83 s
    // With 2 setorders in the loop: 6,35 s (with suspendnotification 6,30 s)
    // With Ordseek in the loop: 6,62 s
    oas:odbadres:SuspendNotification()
    FOR i := 1 UPTO 100000

    // oas:odbadres:Ordseek("A","adresnm") ALTERNATIve for the 3 lines below
    oas:odbadres:SetOrder ("adresnm")
    oas:odbadres:Seek("A")
    oas:odbadres:SetOrder ("adresnr")

    NEXT

    oas:odbadres:ResetNotification()

    ErrorBox{," Elapsed " + NTrim(Seconds() - f) + " secs"}:Show()
    RETURN NIL

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1