×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Slow network performance OleDB engine

Slow network performance OleDB engine

Slow network performance OleDB engine

(OP)
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

RE: Slow network performance OleDB engine

Quote:

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

RE: Slow network performance OleDB engine

(OP)
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.

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
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.....

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
Hi Olaf,

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

Thanks

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
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 ?

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

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:

CODE

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

RE: Slow network performance OleDB engine

(OP)
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.

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

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~ProblemsWithCOLLA...
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

RE: Slow network performance OleDB engine

(OP)
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

RE: Slow network performance OleDB engine

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

CODE

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

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

CODE

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

RE: Slow network performance OleDB engine

(OP)
USE CONTACT
ATAGINFO(laTAGINFO)

Function is not implemented. thumbsdown

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

RE: Slow network performance OleDB engine

(OP)
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......

RE: Slow network performance OleDB engine

Yes, you're right, though it isn't listed online at https://docs.microsoft.com/en-US/sql/odbc/microsof... 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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
Strange

But


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

RE: Slow network performance OleDB engine

(OP)
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......

RE: Slow network performance OleDB engine

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:

CODE

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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
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......

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
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"))

RE: Slow network performance OleDB engine

SETRESULTSET makes the script return the data.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Slow network performance OleDB engine

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

CODE

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:

Quote (myself)

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

RE: Slow network performance OleDB engine

(OP)
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....

RE: Slow network performance OleDB engine

You have a solution, just reread.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Slow network performance OleDB engine

(OP)
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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

(OP)
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 ?

RE: Slow network performance OleDB engine


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

CODE

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

CODE

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

RE: Slow network performance OleDB engine

...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:

CODE

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

RE: Slow network performance OleDB engine

(OP)
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

RE: Slow network performance OleDB engine

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

RE: Slow network performance OleDB engine

Olaf

That's all quite nice.

a) A SET ORDER interferes with LOCATE finding the first record. IF you have no set order and you have SET DELETED ON, that is like having SET FILTER TO NOT DELETED(). Same with any other filter.
If an index on deleted() is in place, that is the source of the bitmap, which as you noted, must be in record # order. Set order to something incompatible with the record number order and LOCATE will take time to find the first record.

SET DELETED affects VFP-SQL, but neither the current SET ORDER nor SET FILTER affect VFP-SQL.

Try the following:

CODE

CLEAR
SET DELETED ON

USE IN c_MyTest
CREATE CURSOR c_MyTest (nField1 i)
FOR m.x = 1 TO 10000000
	INSERT INTO c_MyTest (nField1) VALUES (999999999-m.x)
ENDFOR m.x
GO 2
DELETE FOR RECNO()#999999998
INDEX on nField1 TAG xField1

?"Case 1 LOCATE Takes longer with an incompatible order set."
?"rarely will table be in physical order."
X=SECONDS()
LOCATE
?SECONDS()-M.X

?"Case 2 Faster without an order set."
SET ORDER TO
X=SECONDS()
LOCATE
?SECONDS()-M.X

?"Case 3 A little better than Case 1 because DELETED() index is used by Rushmore"
INDEX ON DELETED() TAG XDEL
SET ORDER TO xField1
X=SECONDS()
LOCATE
?SECONDS()-M.X

?"Case 4 Best with both indexes and no SET ORDER"
SET ORDER TO
X=SECONDS()
LOCATE
?SECONDS()-M.X

SELECT c_MyTest
SET ORDER TO xField1
?"vfp sql unaffected by order on xField1"
X=SECONDS()
SELECT * FROM c_MyTest WHERE nField1=999999998 INTO CURSOR c_SQL nofilter
?"with order",SECONDS()-M.X

SELECT c_MyTest
SET ORDER TO
?"vfp sql unaffected by no order on xField1"
X=SECONDS()
SELECT * FROM c_MyTest WHERE nField1=999999998 INTO CURSOR c_SQL nofilter
?"without order",SECONDS()-M.X 
b) It is not optimized, you're right. Optimization is choosing records, not ordering them.

c) is optimized if there is an index on sub_nr.

This will only work if the contacts are physically in sub_nr order because SQL does not respect the SET ORDER

CODE

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

CODE

X=SECONDS()
FOR n = 1 TO 50
	INSERT INTO c_My50 (nSelected) VALUES (c_MyTest.nField1)
	SKIP 200000 IN c_MyTest
ENDFOR n
SELECT c_My50
*INDEX on nSelected TAG xSelected

*Do this instead of a select
SYS(3054,12)
SELECT c_MyTest.* ;
	FROM c_My50 ;
	INNER JOIN c_MyTest ON c_My50.nSelected = c_MyTest.nField1 ;
	ORDER BY c_My50.nSelected asc ;
	INTO CURSOR c_MySelected nofilter
?"simulated top 50 ",SECONDS()-M.X
SYS(3054,0) 

Mike Yearwood - Former FoxPro MVP

RE: Slow network performance OleDB engine

>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~UnderstandingRush... 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/...


Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Slow network performance OleDB engine

GO TOP always takes long. LOCATE is optimized and I always recommend it. LOCATE does create and use bitmaps. PERIOD.

skip will have the same performance problems as go top because skip is also not optimized. When you have set deleted on, you have a filter. Hence a bitmap.



Mike Yearwood - Former FoxPro MVP

RE: Slow network performance OleDB engine

It's really too bad that people can be rude and ignorant and no one does anything to stop it. I have to get into a fight to prove someone wrong. Why should I do that? It's for the greater good of all, not the ego of some few loud mouths. I'm sorry the majority do not know that bullying such as I get is wrong. The toxic masculinity of the person I refer to is unacceptable. This has happened to me before and I have backed away from such sites. The weakness shown by allowing bullying is really unfortunate.

It's when things are ridiculously simple and some loud mouth gets it totally wrong that these sites end up far worse for the users.

LOCATE command attempts to use Rushmore to locate records. That is all it does. That is all Rushmore is.

Mike Yearwood - Former FoxPro MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close