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]

Yes, you're right, though it isn't listed online at

https://docs.microsoft.com/en-US/sq...isual-foxpro-odbc-driver?view=sql-server-2017

it is listed in the foxpro chm help file. I don't even look, as some things are listed in both this and the other chapter about supported language.

In general, the set of VFP language supported by OLEDB Provider is anything non-visual, which compares to the restriction of the multithreaded runtime. But indeed the normal runtime even supports the COMPILE command while the OLEDB provider supports EXECSCRIPT but not compile, it's a mess to find reasons why some things are or are not supported, EXESCRIPT obviously needs to compile the code you give it as parameter r to be able to execute it.

Anyway, if you have VFP installed you'll have all language at your disposal and all the IDE tools making life easier with the VFP world. Also, a VFP installation doesn't mess up with the rest of the system, it's rather the other way around, sometimes. So go for it.

Bye. Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

OK, now you're having VFP installed do SYS(3054,12) in the command window, followed by your queries and the analysis of the queries running will be showing on screen. As Tamar says tags used for joins and where clauses may be the only ones reported. At least you could see whether SELECT * from CONTACT WHERE SUB_NR='LD96178117' is optimized with the SUB_NR tag or not.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Maybe the SUB_NR tag is not used as other tags with filter are found first. I'd need to create a test case to see whether Rushmore gives up too early in such cases or Tamar hits the nail and tags are never used to optimize order by, though I still can't believe that.

You also have the script manually using that tag and copying the first 50 rows in index tag order and could use that now. Also, you now could simply USE CONTACTS via its remote location, SET ORDER and BROWSE it.

If you don't just want to solve your own problem and in general want to enable remote DBF access via OLEDB, forget that, the more sensible option is creating a web service acting at the server side, don't do remote DBF access. All the features used for DBF file access including automatic locks assume you at least have the network throughput of a LAN and no cloud on the way. VFP file access is very chatty and not good for usage over the cloud.

Bye. Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Strange

But

Why ?
I don't know.....

 

[Alwin N]

Or simpel 3 orders...
ADM_PER

SELECT TOP 5 * FROM ADM_PER ORDER BY REC_ID

SELECT * FROM ADM_PER ORDER BY REC_ID

Rushmore ....... please wait......

 

[Olaf Doschke]

One thing I clearly see the same as Tamar, SYS(3054) only reports what index tags are used for joining or filtering (where clauses). It's still striking how fast you get data sorted and that the command to set an index as active is called SET ORDER, isn't it?

It turns out Rushmore does not optimize order by clauses.

But you can quickly find the 50th value with SET ORDER, LOCATE, SKIP 49:

USE CONTACTS
SET ORDER TO SUB_NR
LOCATE
SKIP 49
lnBorderNr = SUB_NR
SELECT * FROM CONTACTS WHERE SUB_NR<=lnBorderNr ORDER BY SUB_NR

Done with the ExecScript, that should be optimized.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

I still looked into this further and may have found the reason why ORDER BY is not as easy to optimize as you'd think from the fact the binary tree index structure VFP uses can easily be read in index order to sort data.

Like I demonstrated SKIP 49 is fast, so that's not the problem, but let's first look at what Rushmore does in general about the WHERE clauses.

In very short VFP builds up a list of record numbers by reading the CDX and then fetches those from the DBF. It doesn't create a collection of integers, it creates a "bitmap" with a "pixel" for each record number. With multiple where conditions, in the general case it uses multiple indexes to create a bitmap for each condition and uses bitwise operations just like the single clauses are combined with AND or OR.

Anyway, the end result is a bitmap with the few pixels set or you might also think of a sorted set of record numbers. What's not known from this list is which of these are the top N in some order. The nature of TOP is not a condition which you bitAND with the other partial results, you could easily create a bitmap of the top 50 records in some order, but if you use that and bitAND or bitOR it with other results you'll typically get fewer pixels (AND) or more pixels (OR) than the N you specify. TOP N is not a clause that fits into the scheme Rushmore uses. You'd need to pick the N records from the result of all other Rushmore optimizations, but the order of RecNs isn't apparent from the bitmap.

And while a binary tree is easily traversable in order and you can fastly determine the first N nodes, to determine the first N nodes also applying to further conditions. A simple, yet unsatisfactorily way would be simply going from top to bottom and on the way check whether the records you visit in order are in the Rushmore set of results fitting all conditions aside of the TOP N condition and then stop when you found N records.

It's unsatisfactorily, as it's still a table scan technique and in the general case you might do a full table scan when there are less than N records, though in that case, you know without traversing the result will be the full result. The worst case scenario os you find N+1 records and need to find out which one to eliminate from the result to get the TOP N.

Anyway, since a set of record numbers isn't easily sortable in an index tag order without scanning the index, the FoxPro team may have decided to not add ORDER BY optimization into the Rushmore engine. It's only doable in some corner cases, eg with no other conditions. You need a totally different approach to an optimization engine to optimize a TOP N clause in a good way.

For example, my approach will give you 50 records when you don't add any other conditions to the final query, but obviously with further WHERE conditions you get fewer result records than you want, that illustrates the difficulty to find the first N from a set of results.

You'd now perhaps think, that should be easy, when records are output in some order, simply stop after the resultset has N records. Well, the result bitmap is always in RecNo order and not in ORDER BY order, so creating the result in an order is still needing a full sort of the data, it's not doable for any case of any further conditions, only the simple case is solvable and that's perhaps the reason it was not added to the Rushmore engine.


As I said earlier, you better have a server component so you can act in a real client/server way, the bottleneck of the slow connection could be eliminated, the query could still not be optimized, but of the server side has the result - and it will have it faster for it has local access - it comes back faster.

Even without this specific order-by-problem the usage of the CDX to avoid reading too much of the DBF has its limitations. It's only usable in at least LAN speeds for the chatty part of "negotiating" the result records with the CDX.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Well the major problem is that i have a lot of customers which use software like an add-on. So we need +/- 300 customers to install a service module, which i need to build in VFP. They are already using my software for years, and everybody accepts it. Primairy startup in a network is 15 seconds, and after that it keeps working fast. Since the first load creates an temporary index in the TEMP folder. This is typically since the first query takes 4 seconds (or less or more) but after that it runs fast, the query's. Lot of tables are very small, CONTACT.DBF is the larges one. I have large tables to load info, but those tables don't load TOP x, but a WHERE clause: SELECT * FROM TRANSACT WHERE DJ_CODE='925'.... ORDER BY DJ_CODE, etc.. So they load +/- 40 records by the WHERE clause and then the OLEDB sorts it. So that is fast.
I needed to locate the part of the engine which works slow, so i connected it from my home internet to the business location using VPN. I can see then what is te slow part of my application. I saw a database CONTACT.DBF which contains lot of records. So 135 seconds isn't the real problem, it takes 4 seconds in the LAN connection. So when i fix it and it takes +/- 10 seconds via VPN, it would be very fast in the LAN connection........
Locally from my hdd: 1 second
PROVIDER=VFPOLEDB.1;Data Source=C:\TESTDATA\xxxx-ACCVIEW\DATA\2018\xxxx\ADMSEGJN.DBC;Mode=Share Deny None;DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5;OLE
0 23-12-2018 11:35:56 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
906 23-12-2018 11:35:57 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
But from the LAN it is slower, so using VPN i really can see the slow part....

Now i am still look for a solution.......
A script using:

​

SET ORDER TO xx

​

INSERT INTO .....
is no problem at all, as long as i can use it......

 

[Olaf Doschke]

OK, you can use it, what problems do you have using the code I posted 22 Dec 18 17:00 via the general Execscript solution? If your main application is VFP you can also create this on the fly with TEXT..ENDTEXT but then you could also directly use the code without going through OLEDB, because if you think that transports your code there and excecurts it server side, that's only true for real SQL servers, not for VFP.

Still, the best solutions are
a) using a cloud database instead of DBFs
With much effort in moving the backend into such an SQL server
b) using remote desktop
With no effort in code changes but costs for remote desktop licenses and no normal web hosting or just a VPN connection to some data files, of course.

Just notice if you determine the value for the 50th SUB_NR with SKIP 49, a query with some other conditions will likely return less or even no result. So this solution only works for the case you have no other condition. The more general approach for paging data is only possible with an active server component you don't have with OleDB and DBF files. That's the disadvantage of VFP, even stored procs in DBCs are loaded by the VFP runtime and execute client side VFP is serverless and the way it handles the situation with locks with the file system locks is tricky and binding it to NTFS and SMB protocol, which means a simple http connection isn't enough and you have to have a VPN.

So, I'd not go for this, but if this is the only thing you need for the moment, this works. Other than the script using ATAGINFO, this uses nothing not supported by OLEDB, you can USE a table, sure, you can use SKIP and LOCATE, I don't even need to look into the help topic again, it would be useless you have a SETRESULTSET function specifically for OLEDBB, if you couldn't do the slightest most general things of xBASE code working with workareas aside of SQL.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

It works perfectly, but the major problem is, that EXECSCRIPT returns .T. and not the dataset....
-> Fieldname: RETURN_VALUE
-> Value: .T.
0 23-12-2018 12:09:59 AdoRecordSet open 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))
4719 23-12-2018 12:10:03 AdoRecordSet done 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))

 

[Olaf Doschke]

SETRESULTSET makes the script return the data.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

So, even within VFP this works and gives me an Adodb.recordset:

o = CreateObject('adodb.connection')
o.open('provider=vfpoledb.1;deleted=true;exclusive=false;collation sequence=machine;data source=C:\Program Files (x86)\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind\Northwind.dbc')
oRS = o.Execute('ExecScript(FileToStr("D:\temp\some.txt"))')

As long as some.txt does not only do the query and code leading to it, but queries INTO CURSOR aliasname and sets that aliasname as the result set with SETRESULTSET. That should have become clear from what I already said:

To get a result back the VFP code in the txt file would need to create a cursor and specify this with the SETRESULTSET() function, so the oledb provider returns these records.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

First, cleaned all indexes, created only 1 index.
INDEX ON SUB_NR TAG SUB_NR OF "CONTACT.CDX"
Database still slow.... with VFPOLEDB
PROVIDER=VFPOLEDB.1;Data Source=\\192.168.1.95\d$\XXX-ACCVIEW-1INDEX\ADMSEGJN.DBC;Collating Sequence=machine;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;
0 23-12-2018 19:02:03 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
212922 23-12-2018 19:05:36 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
212 seconds !

Then i tried Advantage Database which works really quick....
PROVIDER=Advantage.OLEDB.1;Data Source=\\192.168.1.95\d$\XXX-ACCVIEW-1INDEX\;Persist Security Info=False;TableType=ADS_VFP;ServerType=ADS_LOCAL_SERVER;OLE DB Services = -2;
0 23-12-2018 19:13:34 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
2265 23-12-2018 19:13:36 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

2 seconds !

But then again the next problem.... with the complete CONTACT.CDX
Error 7200: AQE Error: State = HY000; NativeError = 3007; [iAnywhere Solutions][Advantage SQL][ASA] Error 3007: Unsupported function found in index key expression. Table name: CONTACT SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

When will something work for me....

 

[Olaf Doschke]

You have a solution, just reread.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Hi Olaf,

Thanks already, your script inspired me.

I tested it today, but: SCATTER NAME loRecord
doesn't work in the script (from oledb)

This works, but RECNO()=1 is very slowly....
SELECT * FROM CONTACT WHERE .F. INTO CURSOR crsResult READWRITE
SELECT CONTACT
SET ORDER TO TAG SUB_NR
SCAN
&&SCATTER NAME loRecord
INSERT INTO crsResult SELECT * FROM CONTACT WHERE RECNO()=1
IF RECCOUNT('crsResult')=50
EXIT
ENDIF
EXIT
ENDSCAN
SELECT crsResult
SETRESULTSET('crsResult')

Description: Syntax error.
HelpContext: 0
HelpFile:
NativeError: 200/000000C8
number: 2147500037/80004005
Source: Microsoft OLE DB Provider for Visual FoxPro

Can you help me out please ?

Thanks Alwin

 

[Olaf Doschke]

You don't need that script, you just need to add the SETRESULTSET to the script SKIPping 49 records.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Hi olaf,

The problem is that there orders that contain the same records like CUSTOMER_CITY so, that query results in +/- 500 same records.
My last script works perfectly with SETRESULT(), but SCATTER doesn't work..... Do you have any other idea ?

 

[Olaf Doschke]

This is the script from 22 Dec 18 17:00 with added SETRESULTSET, and that works:

Use contacts
Set Order To Tag SUB_NR
Locate
Skip 49
lnBorderNr = SUB_NR
Select * from contacts Where SUB_NR<=lnBorderNr Into Cursor crsResult
Setresultset('crsResult')

Why didn't you take that? What's wrong with this? What's not understood?

But Oh boy, they put scatter into the oledb runtime, but not scatter name. You can scatter memvar

Select * From contacts Where .F. Into Cursor crsResult Readwrite
Select contacts
Set Order To Tag SUB_NR
Scan For Reccount('crsResult')<50
   Scatter Memo Memvar
   Select crsResult
   Append Blank
   Gather Memo Memvar
Endscan
Setresultset('crsResult')

This works, too, but it's overcomplicated for a simple TOP N case.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

...Wich once more brings me back to the idea of creating a server side service that serves the data.

As you have Foxpro installed now, you can create a DLL with it, or an EXE having the full lnaguage runtime and no restrictions as ODBC or the OleDB Provider. If you createa Multithreaded DLL (MTDLL) there is indeed another runtime restriction with the vfp9t.dll instead of vfp9r.dll, but that's really just about things you don't need for a nonvsiaul server side component.

The base skeleton for a COM Server class is like this:

Define Class server As Session Olepublic
    Procedure ExecScript(cScript as String,cReturnAlias as String) as String
       
       ExecScript(cScript)
       CursorToXML(Select(Evl(Nvl(cReturnAlias,0),0)),"lcXML",1,1+8+48,0,"1")
       Return lcXML
    EndProc

    Procedure ExecScriptFile(cScriptFileName as String, cReturnAlias) as String
       
       ExecScript(FileToStr(cScriptFileName))
       CursorToXML(Select(Evl(Nvl(cReturnAlias,0),0)),"lcXML",1,1+8+48,0,"1")
       Return lcXML
    EndProc
EndDefine

If you put this as the only prg into a project you call vfpole.pjx and compile as EXE or DLL and register the resulting EXE or DLL you have an OLE class "vfpole.server" which could be used from ASP.NET with vfpserver = Server.CreateObject("vfpole.server") or even PHP (for Windows) with $obj = new COM("vfpole.server")

Now you can really have a server side component and let it be fed from an ASP.NET script or PHP script with input parameters you POST to it or provide in a GET request. And ASP.NET VB/C# or PHP can further process the XML into something else as response, though it'd be easiest if the just forward it.

If you can better work on JSON, there is a good JSON library for VFP at

https://github.com/VFPX/nfJson

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Olaf,

Super that worked. SCATTER MEMO MemVar
Now it works.

The major problem is that, SELECT TOP 50 is used for a selection popup in the software. So we also scan FILTER for the first 50 records found like CUSTOMER_NAME LIKE '%ALWIN%' etc.....
So LOCATE is too complex to use for such seek, and makes it 2 times slower: First skip 50 records, then back and walk 50 records again, with an FILTER set, it takes a lot of datastream....
But SCATTER MEMO works perfectly and let's me start using Scripting in OLEDB.

Thanks a lot, now i start scripting and OLEDB works perfectly fast !
The script runs super, first time 2,8 seconds (open CONTACT.DBF read dictionary i guess)
0 25-12-2018 18:01:00 AdoRecordSet open 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))
2844 25-12-2018 18:01:03 AdoRecordSet done 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))

0 25-12-2018 18:01:03 AdoRecordSet open 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))
16 25-12-2018 18:01:03 AdoRecordSet done 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))

0 25-12-2018 18:01:03 AdoRecordSet open 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))
0 25-12-2018 18:01:03 AdoRecordSet done 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))

0 25-12-2018 18:01:03 AdoRecordSet open 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))
0 25-12-2018 18:01:03 AdoRecordSet done 3/3/1 : => EXECSCRIPT(FILETOSTR("E:\CAVO\DIVERSEN\ELVY\script.txt"))

But OLEDB select statement....... keeps on running slow....
0 25-12-2018 18:01:03 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
122859 25-12-2018 18:03:06 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

0 25-12-2018 18:03:06 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
422 25-12-2018 18:03:07 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

0 25-12-2018 18:03:07 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
437 25-12-2018 18:03:07 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

0 25-12-2018 18:03:07 AdoRecordSet open 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR
438 25-12-2018 18:03:07 AdoRecordSet done 3/3/1 : => SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR