Setorder & speed

[ic2]

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

[lumberjack]

Dick,

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,

[mainhatten]

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
https://stackoverflow.com/questions/163 ... mer-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

[ic2]

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

[lumberjack]

Hi Dick,

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:

Code: Select all

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,

[Karl-Heinz]

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.

Code: Select all

    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

[robert]

Dick,

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

[ic2]

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