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

 

[Mike Lewis]

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

The usual reason for that is that the relevant columns are not indexed. Check that you have an index tag on the column(s) in the ORDER BY clause.

If you are not familiar with this issue, read the Help topics on Rushmore optimisation.

It would also be useful for us to know if you see a disproportionate slowdown when performing the same queries locally.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Alwin N]

The database is indexed, otherwise "SELECT * from CONTACT WHERE SUB_NR='LD96178117'" would not be so fast. I tested it from my home using the internet connection instead of the network connection. By the network everything works acceptabily fast, but i am looking for the startup overhead.

 

[Olaf Doschke]

I agree with Mike, especially ordering takes long on unindexed data no matter if you query with an ODBC driver or OleDB provider, the database engine does the querying, not the drivers or providers, they are just messengers and if you only transfer x rows, the driver has the least thing to do. Even if you have millions of records, which is the top can be easily taken from an index, but not from unindexed data, this needs a full table scan to ensure it catches the top record or x of them. Indexes are always both good for direct access of a specific index value (like in a book index) but also for the whole or part of data in the sort order of the index. If you imagine there is some memory effect about that, there is statistical data and cached data, but sort order is not kept up to date once it's done, new data is somewhere in between and only indexing it will keep track of that index order, too.

But I experience bad timings on a database about same data to be slower with OleDB than with ODBC where OleDB is just used to create ADO Recordsets for an ActiveX control (that's done in VB5). So there is some difference from the type of driver you use, too. In general, ODBC is strictly about data, OleDB is more versatile but it's slower. In this case, I'm sure it's just unindexed data.



Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Attached a demo database from AccountView with exactly the same structure.

 

[Alwin N]

Can i list the index structure directly from CONTACT.DBF & CONTACT.CDX ?
Can i see the "execution" plan like Microsoft SQL ?

Maybe the OLEDB engine chooses to not use an index, but the question is why.....

 

[Olaf Doschke]

Our posts overlapped, if you're so sure about the indexes, then the question is whether they are used. VFP has little analysis tools.
Your initial 2.7 seconds for finding a single record also point out no indexes, the 16ms after that comes from caching.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Hi Olaf,

Is there anything i can see ?
Like is CONTACT.CDX used ? etc ?

Thanks

 

[Olaf Doschke]

You cannot analyze this remotely. But with VFP you can see whether and what indexes are used with SYS(3054) (rushmore optimization level)

Your OLEDB connection is using Deleted=True and Deleted=Yes, one of them should be the one that's used, but I don't think that'd turn off rushmore optimization.

What can cause indexes to not be used is a collation sequence not matching that of an index, so try adding the provider option Collating Sequence=machine

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

I just tried, but SYS(3054) is not among the part of the VFP language supported by the OleDB provider.

There's just one thing not very obvious, but likely the reason for the long timing: VFP is no server, so whatever you execute - even via the oledb provider, runs local on your computer, where you have installed the vfp oledb provider. So when you do a query involving a dbf, to be able to do that the oledb provider has to open the dbf file via your slow connection.

Rushmore optimization will help a bit, but in the end the file operations are done through your slow connection.

What I said earlier about the role of drivers as messengers is only true for drivers remotely connecting to servers, not to a VFP database or dbf files.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Hi,

I just tested it, and found out using PROCMON that CONTACT.CDX is openened when:
SELECT * from CONTACT WHERE SUB_NR='LD96178117'
SELECT TOP 50 * from CONTACT WHERE SUB_NR='LD96178117' ORDER BY SUB_NR
(results in 1 record).
-> CONTACT.CDX Few bytes are read.
-> CONTACT.DBF Few bytes are read.

But when this is executed:
SELECT TOP 50 * from CONTACT ORDER BY SUB_NR
-> CONTACT.CDX is opened, but only read a few bytes
-> CONTACT.DBF is read from the beginning until the end.

So, it looks like RUSHMORE somehow doesn't use the index, because ?
-> ANSI=True
-> Collation doesn't match ?
-> Maybe something else ?

Any ideas ?

 

[TamarGranor]

In order to do TOP 50, the entire table has to be ordered. AFAIK, Rushmore isn't involved in ORDER BY clauses, only JOIN and WHERE.

Tamar

 

[Olaf Doschke]

Even, if you had 'SYS(3054) to work with, you'd only get confirmation or not, VFPs SQL Engine does not explain in what way a TAG of a CDX is used or not.

What you'd expect from an ORDER BY query when an index tag on SUB_NR exists is at worst a full read of that TAG within the CDX to sort all data by that index info and then reading the top 50 records of the DBF, what you describe is a bit contradictory, as the query for a single SUB_NR seems to use the index and the order by not.

The few bytes read from the CDX are likely just the header and rushmore decides he doesn't find a tag to be usable for order by.
I don't see how that matches, beause then also the look for a single record should fail to use an index. Maybe just luck the record is found earlier than a single full scan. Statistically a half table scan is necessary.

Points towards a mismatch of index collation and current set collation.

The best thing you can use with OLEDB allowing a lot of more operations and script executions other than SQL queries is VFPs EXECSCRIPT. You can crreate a vfp script, save it to a file (on your local home PC) and execute it via running 'EXECSCRIPT(FILETOSTR("C:\PAth\To\Filename.txt"))' as the oledb command. 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. That way you could execute ATAGINFO() and turn the result array into retrievable cursor data.

In the same way you may read the first 50 records by a script doing this manually instead of sql:

SELECT * FROM CONTACTS WHERE .F. INTO CURSOR crsResult READWRITE
SELECT CONTACTS
SET ORDER TO TAG SUB_NR && in the hope this is the tag name
SCAN
SCATTER NAME loRecord
   INSERT INTO crsResult FROM NAME loRecord
   IF RECCOUNT('crsResult')=50
      EXIT
   ENDIF
ENDSCAN
SETRESULTSET('crsResult')

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Tamar, wel it looks like you are right.

The table is ordered, but why doesn't vfpoledb than not use the index. It is full of all kind's of indexes.
The SDK shows it uses: Collating Sequence=machine

Codepage shows 1252: SELECT TOP 50 CPDBF(),CPCURRENT(),* from CONTACT WHERE SUB_NR='LD96178117' ORDER BY SUB_NR

Can somebody please try to find out for me why it doesn't work ?

Database is attached above.

 

[Olaf Doschke]

Tamar, I just think Sys(3054) doesn't tell us that or what tags are used for sorting, but clearly a LOCATE for .t. in a workarea with a SET ORDER is optimized to find the first record in index order and SKIP 1 also is always fast, and so TOP N could be optimized. Alone the command SET ORDER TO hints about the usability of indexes for fast sorting of data and traversing it in sort order without needing to read it fully.

If you're right and the Rushmore engine doesn't make use of indexes for sorting data, then this is indeed quite a weakness of the VFP SQL engine. I don't think so. Remember how VFP wasn't open sourced as it was deprecated back in 2007. One argument assumed is that there are some secrets in the way the optimizer works. I don't think you could hide it away as C++ decompilation is perhaps harder than VFP code or any bytecode decompilation, but it's possible and done.

Anyway, if you're right, then this would be one of the things showing up as better be done in an xBase fashion SCANning a table manually in some tag order than asking SQL to query it.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Alwin, collation sequence isn't codepage.

INDEX ON has a COLLATION option, that's optional, but an index is created with the current collation (not codepage) when you don't use the option. That info is part of the index tag and all further index data is made with that collation sequence. And that means an index only is used for Rushmore optimizations, if SET('COLLATE') matches with the index collation given in the 6th column of the array ATAGINFO generates.

There even are bugs about index used for rushmore, as Christof Wollenhaupt described here:

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

So you better index with SET COLLATE TO MACHINE and to use an index with another collation you need to SET COLLATE to the index collation. Recommending the oledb option collation sequence=machine is done under the assumption all indexes are created in that collation, especially since indexes on integer and other non character data would suffer from other collation sequence indexes and there only use is for sorting in some dictionary order also doubling the index size or limiting to 120 character index values, as also is mentioned in the Wiki.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

Olaf,

Can you create a simpel statement so i could retrieve ATAGINFO() from the database table ? So i can see the 6th item ?
I guess i am close to the solution....

Thanks
Alwin

 

[Olaf Doschke]

Well, as said you can execute EXECSCRTPT(). So your OLEDB command to send is simply:

EXECSCRIPT(FILETOSTR("C:\Path\To\Filename.txt"))

And then you need a script doing ATAGINFO, like that:

USE CONTACTS
ATAGINFO(laTAGINFO)
CREATE CURSOR crsResult (tagname char(128), tagtype char(20), expression char(254), filter char(254), order char(20), collation char(20))
APPEND FROM ARRAY laTAGINFO
SETRESULTSET('crsResult')

And save that as C:\Path\To\Filename.txt (any path filename you like, just the same as you put into the EXECSCRIPT call).

Bye, Olaf

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Alwin N]

USE CONTACT
ATAGINFO(laTAGINFO)

Function is not implemented.

Think i need to install Visual Foxpro.....

 

[Alwin N]

Only 69 tags

Collation MACHINE
Tag SUB_NR like the key SUB_NR

Help me please...... i don't know where to seek the problem......