CODEWITHSUNDEEP
foxpro
Alwin N
Alwin N

Reputation: 1

FoxPro SELECT statement slow network performance

SELECT statement of a database is very slow. Database table has 95.000 records.

I have a index listing, and the Index TAG is included. Tested the connection remote using VPN so I can see slow speeds.

SELECT * 
FROM CONTACT 
WHERE SUB_NR = 'LD96178117'

-> takes 3 seconds.

SELECT TOP 50 CONTACT.* 
FROM CONTACT 
ORDER BY SUB_NR

-> takes 142 seconds.

Second time in the same AdoConnection the queries run faster.

PROVIDER=VFPOLEDB.1;Data Source=X:\Projects\FoxPro\ElvyTest\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;

SELECT TOP 50 CONTACT.* 
FROM CONTACT 
ORDER BY SUB_NR

Also tested:

SET COLLATE TO "MACHINE"

or

SET COLLATE TO "GENERAL"

Opened the file in FOXPRO 9.0 SP2 ATAGINFO() shows:

TAGNAME: SUB_NR, TAGTYPE: REGULAR,     
EXPRESSION: SUB_NR,FILTER, ORDER: ASCENDING, COLLATION: MACHINE

It looks like VFPOLEDB.1 is creating an temporary index in %TEMP% folder.

SYS(3054,12) Show in Foxpro that Rushmore uses an index with a select statement

SELECT * 
FROM CONTACT  
WHERE SUB_NR = 'LD96178117'

Using index tab Sub_nr to rushmore optimize table contact Rushmore optimization level for table contact: full

SELECT TOP 50 * 
FROM CONTACT 
ORDER BY SUB_NR

Rushmore optimization level for table contact: none

Upvotes: 0

Views: 1040

Answers (1)

Sparky
Sparky

Reputation: 15085

This command:

SELECT * from CONTACT WHERE SUB_NR='LD96178117'

Tells SQL to look up the SUB_NR in the index, and return all the data from the row.

This command:

SELECT TOP 50 CONTACT.* from CONTACT ORDER BY SUB_NR

Tells SQL to order the table by SUB_NR, (most likely generating the temp table), and once that is done, pull back the top 50 records. How the VFP OLE provider chooses to optimize that is anyone's guess. TOP was added to SQL Server in version 2008. If you are using an old OLE provider, or if it was not optimized for the TOP command, you could see unpredictable performance.

Once you've run the query a first time, subsequent runs of the same query will pull from memory cache, not needing to access the disk, hence better performance. Also, since you are opening the file in SHARED mode, locking may be a factor. Also, the DELETED setting means records returned have to have the deleted marker checked

Upvotes: 1

Related Questions