Slow network performance OleDB engine 1

[Alwin N]

I installed a Foxpro database on my network. Using OPENVPN i try to open the database from a remote location (my house).
Opening a database 95.000 records,
29.908.480 CONTACT.CDX
265.553.022 CONTACT.DBF
912.640 CONTACT.FPT
PROVIDER=VFPOLEDB.1;Data Source=\\192.168.1.95\d$\xxx-ACCVIEW\DATA\2018\xxx\ADMSEGJN.DBC;SourceType=dbf;Deleted=Yes;Mode=ReadWrite|Share Deny None;Mode=Share Deny None;DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5;OLE DB Services = 0;

Seems no problem:
Opening 1 record directly with a WHERE clause: 2,7 seconds
0 19-12-2018 21:45:21 AdoRecordSet open 3/3/1 : => SELECT * from CONTACT WHERE SUB_NR='LD96178117'
2734 19-12-2018 21:45:23 AdoRecordSet done 3/3/1 : => SELECT * from CONTACT WHERE SUB_NR='LD96178117'
or NULL value
0 19-12-2018 22:06:57 AdoRecordSet open 3/3/1 : => SELECT * from CONTACT WHERE SUB_NR IS NULL
4390 19-12-2018 22:07:01 AdoRecordSet done 3/3/1 : => SELECT * from CONTACT WHERE SUB_NR IS NULL
After that: 16ms
0 19-12-2018 22:07:01 AdoRecordSet open 3/3/1 : => SELECT CONTACT.* FROM CONTACT WHERE SUB_NR IS NULL
16 19-12-2018 22:07:01 AdoRecordSet done 3/3/1 : => SELECT CONTACT.* FROM CONTACT WHERE SUB_NR IS NULL
But then: 102,2 seconds
0 19-12-2018 22:07:01 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT WHERE (CRED_SHOW) ORDER BY SUB_NR
102203 19-12-2018 22:08:43 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT WHERE (CRED_SHOW) ORDER BY SUB_NR
Or
0 19-12-2018 21:04:34 AdoRecordSet open 3/3/1 : => SELECT * from CONTACT WHERE RECNO()=1
135875 19-12-2018 21:06:49 AdoRecordSet done 3/3/1 : => SELECT * from CONTACT WHERE RECNO()=1
Or
0 21-12-2018 08:15:18 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
142610 21-12-2018 08:17:40 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

Everytime i use something like TOP x and ORDER BY it takes a lot of time.

Why does it take such a long time ? Do i need to disable something ?

Alwin

 

[Olaf Doschke]

Well, with different additional conditions, of course, SKIP 49 would not suffice but for the simple top 50 records case it does:

a) LOCATE is Rushmore optimized with an index set beforehand, the "top node" of the index is located.
b) SKIP 49 is Rushmore optimized. Well, it actually is not, but like Rushmore optimizations, this makes use of the index, and indeed the index only, even without first determining which index to use, as you manually specify it.
c) SELECT without TOP clause and instead WHERE SUB_NR<=lnBorderNr clause is optimized without needing to fetch and copy over every single record.

Therefore don't worry this would traverses the records twice, it doesn't the SKIP 49 will traverse the index tag, not the DBF. This is the part the SQL engine fails to do. And then finally the DBF is read optimized as WHERE clauses are optimized. So what is read twice but in different ways is the CDX tag, but since the final fetch of records is in one SQL command this will get faster than copying row by row, despite this small overhead.

Yes, in a more general case when you would SKIP while a SET FILTER is set, this will get slower again.

Yes, TOP N clause and ORDER BY is NOT optimized, never is, even not in native VFP locally. That's even a lesson I learned from examining this in detail, I made myself aware of it, but I think of it as a weakness the Rushmore SQL engine could do better. And aside of that another lesson learned is SCATTER is supported in VFP9 OLEDB Provider SCATTER NAME is not, it's quite disappointing and with a self-made COM Server you could even overcome that and use some more of VFP language up to all of it when compiling as an EXE.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

>Set order to something incompatible with the record number order and LOCATE will take time to find the first record.

That's where you are wrong, GO TOP always takes long, LOCATE will be better optimized and will also use the current index set by SET ORDER. Show me a case where LOCATE takes long on an ordered cursor.

I saw you posting on

http://fox.wikis.com/wc.dll?Wiki~UnderstandingRushmore

and you're full of wrong conclusions with your examination of the CDX usage. You say "It seems very clear that Fox is only using the index to initially "create a set of records that match the criteria using Rushmore" and it does re-evaluates the search criteria again to find out whether the record still matches it" when the record pointer moves."

No, LOCATE never builds up a bitmap, LOCATE always only makes use of an index if a FILTER is set and only to find the NEXT 1 record, no more, no less. Compare with a SEEK of one value and you'll also notivce multiple CDX read access, because using a binary tree is like the number guessing game, you don't hit the correct recno immediatley, you need at max N guesses for up to 2^n records and that's what you saw there. bitmaps only are created using SQL.

You only need LOCATE as a better GO TOP to get to the top recno in the index order and that'll be fast, also SKIP 49 is fast, as it doesn't traverse the index to look roshmore optimized for filter conditions, it traverses the index tree in the way it is also pointing to siblings in index order, SKIP 49 with no index set will simply add 49 to the recno and position there, SKIP 49 when an index is set will read a concatenated list of index node pointers in the CDX starting from the index node it's currently on and that's not using the bitmap mechanism of rushmore. That's simply using the index tree structure that's not only optimized to find (SEEK) certain values and do lookups, but also maintains information about next and previous in order so SKIP N is fast, this has nothing to do with the classical rusmore optimization SQL does nor the differrent optimization of finding a record matching SET FILTER including the indirect DELETED() condition.

Have you seen the post of Calvin Hsia about extracting index information, there you can understand the nature of the index binary tree:

https://blogs.msdn.microsoft.com/calvin_hsia/2005/01/05/what-is-an-index-anyway/

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name