Mixing
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:ProjectsFoxProElvyTestADMSEGJN.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
foxpro
edited Jan 12 at 15:13
marc_s
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
add a comment |
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:ProjectsFoxProElvyTestADMSEGJN.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
foxpro
edited Jan 12 at 15:13
marc_s
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
add a comment |
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:ProjectsFoxProElvyTestADMSEGJN.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
foxpro
edited Jan 12 at 15:13
marc_s
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
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:ProjectsFoxProElvyTestADMSEGJN.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
foxpro
foxpro
edited Jan 12 at 15:13
marc_s
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
edited Jan 12 at 15:13
marc_s
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
edited Jan 12 at 15:13
marc_s
581k13011201267
edited Jan 12 at 15:13
marc_s
581k13011201267
edited Jan 12 at 15:13
marc_s
581k13011201267
581k13011201267
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
asked Dec 24 '18 at 9:01
Alwin NAlwin N
12
12
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 1 at 15:34
SparkySparky
13.2k12342
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53911283%2ffoxpro-select-statement-slow-network-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 1 at 15:34
SparkySparky
13.2k12342
add a comment |
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
answered Jan 1 at 15:34
SparkySparky
13.2k12342
add a comment |
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
answered Jan 1 at 15:34
SparkySparky
13.2k12342
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
answered Jan 1 at 15:34
SparkySparky
13.2k12342
answered Jan 1 at 15:34
SparkySparky
13.2k12342
answered Jan 1 at 15:34
SparkySparky
13.2k12342
answered Jan 1 at 15:34
SparkySparky
13.2k12342
13.2k12342
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53911283%2ffoxpro-select-statement-slow-network-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
