Getting back to the original problem I think I have found the cause, not
necessarily the solution but at least getting closer.
Further testing suggests that the cause is SET ANSI ON!!!
In ADO SET ANSI is ON by default, I don't know if there is any way that can
be changed.
Have been testing
On Fri, Sep 12, 2008 at 11:41 AM, NickC [EMAIL PROTECTED] wrote:
Getting back to the original problem I think I have found the cause, not
necessarily the solution but at least getting closer.
Further testing suggests that the cause is SET ANSI ON!!!
In ADO SET ANSI is ON by default, I don't
On Thu, Sep 11, 2008 at 4:36 PM, Dave Crozier
[EMAIL PROTECTED] wrote:
Stephen,
Dbase III had multi user as standard option as opposed to Dbase II (single
user) which could run multi user but only if the files were not modified
(Read only).
The only way to run DBASEII multi user was using
Actually the table limit with dBaseII was TWO
Paul Newton
dBase2 had 4 tables open as a limit, dBase3 you could get 25
Darn that was so long ago. That made a huge difference to me. Then I
saw foxbase and the same app compiled in fox was faster. I think that
was 89 or so and never looked
: SQL not optimised
On Thu, Sep 11, 2008 at 4:36 PM, Dave Crozier
[EMAIL PROTECTED] wrote:
Stephen,
Dbase III had multi user as standard option as opposed to Dbase
II (single
user) which could run multi user but only if the files were not modified
(Read only).
The only way to run
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Paul Hill
Sent: 12 September 2008 11:59
To: ProFox Email List
Subject: Re: SQL not optimised - Rushmore failing
On Fri, Sep 12, 2008 at 11:41 AM, NickC [EMAIL PROTECTED] wrote:
Getting back
dBase2 had 4 tables open as a limit, dBase3 you could get 25
dBaseIII+ had 10 work areas (source, page 20 of Hacker Guide 6.0)
Rick
White Light Computing, Inc.
www.whitelightcomputing.com
www.swfox.net
www.rickschummer.com
___
Post Messages
. I will
say however, if it is in the Hacker's Guide you can pretty much take it to
the bank!
Gil
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Rick Schummer
Sent: Friday, September 12, 2008 1:18 PM
To: [EMAIL PROTECTED]
Subject: RE: SQL
On Fri, Sep 12, 2008 at 8:12 AM, Paul Newton [EMAIL PROTECTED] wrote:
Actually the table limit with dBaseII was TWO
Paul Newton
dBase2 had 4 tables open as a limit, dBase3 you could get 25
Darn that was so long ago. That made a huge difference to me. Then I
saw foxbase and the same
either.
Still very slow.
Rgds,
Nick
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Stephen Russell
Sent: 09 September 2008 21:29
To: ProFox Email List
Subject: Re: SQL not optimised
On Tue, Sep 9, 2008 at 1:57 PM, NickC [EMAIL PROTECTED
Hi Ted,
SET DELETED is OFF, have tried with it on but no difference.
Nick
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Ted Roche
Sent: 09 September 2008 22:07
To: profox@leafe.com
Subject: Re: SQL not optimised
On Tue, Sep 9, 2008 at 2:57
: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Dan Olsson
Sent: 09 September 2008 22:30
To: profox@leafe.com
Subject: RE: SQL not optimised
At 2008-09-09 21:59, NickC wrote:
SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo cLastOrdNo ;
AND Left(OrdNo,1) = cDept ORDER BY OrdNo
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some thirty year
old applications. So unfortunately no I cannot add a C(1) field for dept.
I didn't think Foxpro was around 30 years agowhat was the original
program, just for the sake of curiosity?
much
complete.
Gil
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of MB Software Solutions
General Account
Sent: Thursday, September 11, 2008 3:41 PM
To: [EMAIL PROTECTED]
Subject: Re: SQL not optimised
NickC wrote:
Dan,
Cannot change tables
]
Subject: Re: SQL not optimised
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some
thirty year
old applications. So unfortunately no I cannot add a C(1)
field for dept.
I didn't think Foxpro was around 30 years agowhat was the original
program, just
General Account
Sent: 11 September 2008 20:41
To: ProFox Email List
Subject: Re: SQL not optimised
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some thirty
year
old applications. So unfortunately no I cannot add a C(1) field for
dept.
I didn't think
I also started with dBase III around 1984, then Clipper. Moved to FoxDos I
think 2.5 all the way up to VFP9.
Sure has been a great ride,
Kent
I jumped into dBase III in about 1984. So that was more like
24 years ago (time flies).
Gil
___
You left out dbXL and its companion compiler Quicksilver... g
Gil Hale wrote:
In reverse order AFAIK, the fre VFP sBase apps were were FoxBase, dBase IV,
dBase III+, dBase III, dBase II, Vulcan. R_Base was in that mix at some
point, as was Paradox and some other database apps that had their
On Thu, Sep 11, 2008 at 2:41 PM, MB Software Solutions General Account
[EMAIL PROTECTED] wrote:
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some thirty year
old applications. So unfortunately no I cannot add a C(1) field for dept.
I didn't think Foxpro was
To: [EMAIL PROTECTED]
Subject: Re: SQL not optimised
On Thu, Sep 11, 2008 at 2:41 PM, MB Software Solutions General Account
[EMAIL PROTECTED] wrote:
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some
thirty year
old applications. So unfortunately no I
11, 2008 4:29 PM
To: [EMAIL PROTECTED]
Subject: Re: SQL not optimised
You left out dbXL and its companion compiler Quicksilver... g
Gil Hale wrote:
In reverse order AFAIK, the fre VFP sBase apps were were
FoxBase, dBase IV,
dBase III+, dBase III, dBase II, Vulcan. R_Base
Subject: Re: SQL not optimised
On Thu, Sep 11, 2008 at 2:41 PM, MB Software Solutions General Account
[EMAIL PROTECTED] wrote:
NickC wrote:
Dan,
Cannot change tables at all easily, they are accessed by some thirty year
old applications. So unfortunately no I cannot add a C(1) field for
dept
: RE: SQL not optimised
Slight progress but no idea why!
Three ways that don't work (too slow):
SELECT MIN(OrdNo) FROM Orders WHERE OrdNo cLastPONum
SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo cLastPONum ORDER BY OrdNo ASC
SELECT * FROM Orders WHERE OrdNo IN ;
(SELECT TOP 1
OrdersNoOnly WHERE OrdNo cLastOrdNo ;
AND Left(OrdNo,1) = cDept ORDER BY OrdNo
* takes 0.7 seconds
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of NickC
Sent: 09 September 2008 18:15
To: 'ProFox Email List'
Subject: RE: SQL not optimised
On Tue, Sep 9, 2008 at 1:57 PM, NickC [EMAIL PROTECTED] wrote:
INDEX ON OrdNo TAG OrdNo
INDEX ON LEFT(OrdNo,1) TAG Dept
Have you dropped the indexes above on the production table and then
recreated them?
I understand that getting 200 + columns of data is what is in the way
even if it was
On Tue, Sep 9, 2008 at 2:57 PM, NickC [EMAIL PROTECTED] wrote:
Sorry that example was supposed to read:
What's your setting of SET DELETED? What if you toggle it?
--
Ted Roche
Ted Roche Associates, LLC
http://www.tedroche.com
___
Post Messages
At 2008-09-09 21:59, NickC wrote:
SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo cLastOrdNo ;
AND Left(OrdNo,1) = cDept ORDER BY OrdNo
* takes 50 seconds
IF cDept is (or you could make it) C(1) you should not need an INDEX
ON LEFT(OrdNo,1) TAG Dept since the shorter value to the right of
On Sun, Sep 7, 2008 at 12:17 PM, NickC [EMAIL PROTECTED] wrote:
Slight progress but no idea why!
Three ways that don't work (too slow):
SELECT MIN(OrdNo) FROM Orders WHERE OrdNo cLastPONum
SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo cLastPONum ORDER BY OrdNo ASC
SELECT * FROM Orders
On Mon, Sep 8, 2008 at 6:53 AM, NickC [EMAIL PROTECTED] wrote:
Stephen Russell wrote:
how many indexes on Ordno?
Why not make a fake table to hold just the orders for the past ??? 180
days perhaps.
Now your NEXT or Prev. navigation will give you the actual value you
then do your query
Stephen Russell wrote:
how many indexes on Ordno?
Why not make a fake table to hold just the orders for the past ??? 180
days perhaps.
Now your NEXT or Prev. navigation will give you the actual value you
then do your query for.
Stephen,
how many indexes on Ordno?
Have tried
Thanks Gil but tried that and no difference.
Nick
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Gil Hale
Sent: 06 September 2008 20:43
To: profox@leafe.com
Subject: RE: SQL not optimised
You know what, I just remembered a trick I brought up
Slight progress but no idea why!
Three ways that don't work (too slow):
SELECT MIN(OrdNo) FROM Orders WHERE OrdNo cLastPONum
SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo cLastPONum ORDER BY OrdNo ASC
SELECT * FROM Orders WHERE OrdNo IN ;
(SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo
So nobody has any suggestions?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Nick Causton
Sent: 04 September 2008 20:43
To: 'ProFox Email List'
Subject: SQL not optimised
Hi all,
I have a problem with an app which retrieves from a table of
Oops, just noticed the replies in my Profox[OT] folder!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of NickC
Sent: 06 September 2008 12:09
To: 'ProFox Email List'
Subject: RE: SQL not optimised
So nobody has any suggestions?
-Original
00:12
To: profox@leafe.com
Subject: Re: SQL not optimised
Yikes - what a muddle.
I'll take a stab at it.
Assume the data is in VFP on a web server, running IIS, and IIS is using
ASP with an ado connector to the vfp table.
The iUsr account on the server machine doesn't have rights
is again
taking around a minute to extract.
Nick
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of NickC
Sent: 06 September 2008 12:59
To: 'ProFox Email List'
Subject: RE: SQL not optimised
Thanks all for the suggestions.
I obviously didn't
Why not make a VFP dll to run on ASP?
Allen
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of NickC
Sent: 06 September 2008 15:05
To: [EMAIL PROTECTED]
Subject: RE: SQL not optimised
One other thing worth a mention is that each record has 200 fields
On Thu, Sep 4, 2008 at 3:42 PM, Nick Causton [EMAIL PROTECTED] wrote:
* Next
SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo (clastOrderNumber) ORDER
BY OrderNo DESC
* Prev
SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo (clastOrderNumber) ORDER
BY OrderNo ASC
Problem is VFP does not
I obviously didn't explain fully. This is an ASP webpage running on IIS
which uses ADO to retrieve data from FoxPro tables. The Fox tables are
shared with thirty year old applications, so no chance of making any
substantial modifications to the table structures.
By any chance, is the index tag
Still same slow problem even when SQL is run direct within VFP.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Allen
Sent: 06 September 2008 14:19
To: profox@leafe.com
Subject: RE: SQL not optimised
Why not make a VFP dll to run on ASP?
Allen
Nope, no filters of any sort on the index.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Rick Schummer
Sent: 06 September 2008 14:35
To: profox@leafe.com
Subject: RE: SQL not optimised
I obviously didn't explain fully. This is an ASP webpage
] On Behalf
Of Ted Roche
Sent: 06 September 2008 14:26
To: profox@leafe.com
Subject: Re: SQL not optimised
On Thu, Sep 4, 2008 at 3:42 PM, Nick Causton [EMAIL PROTECTED]
wrote:
* Next
SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo (clastOrderNumber)
ORDER
BY OrderNo DESC
* Prev
I like your theory but unfortunately still doesn't help speed this up.
According to SYS(3054) it is being optimised but even so still takes almost
a minute to extract one record.
Fully or partially. Big difference.
In an earlier message you noted the table is wide, over 200 columns. What about
On Sat, Sep 6, 2008 at 1:03 PM, NickC [EMAIL PROTECTED] wrote:
I like your theory but unfortunately still doesn't help speed this up.
According to SYS(3054) it is being optimised but even so still takes almost
a minute to extract one record.
The fact that selecting on the full, wide table is
Hi Rick,
Fully Optimised. Only index is on OrderNo field all others deleted for
testing.
Nick
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Rick Schummer
Sent: 06 September 2008 18:58
To: profox@leafe.com
Subject: RE: SQL not optimised
I
Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Ted Roche
Sent: 06 September 2008 19:23
To: profox@leafe.com
Subject: Re: SQL not optimised
On Sat, Sep 6, 2008 at 1:03 PM, NickC [EMAIL PROTECTED] wrote:
I like your theory but unfortunately still doesn't help speed
On Sat, Sep 6, 2008 at 3:24 PM, NickC [EMAIL PROTECTED] wrote:
Do you have a tag on DELETED()?
No, only index is on OrdNo Ascending.
Well, try the DELETED() tag and see what happens.
When SET DELETED is ON, SQL SELECTs are designed to return only
non-deleted records. It can use the index to
@leafe.com
Subject: Re: SQL not optimised
On Sat, Sep 6, 2008 at 1:03 PM, NickC [EMAIL PROTECTED] wrote:
I like your theory but unfortunately still doesn't help speed this up.
According to SYS(3054) it is being optimised but even so still takes
almost
a minute to extract one record
: Saturday, September 06, 2008 1:03 PM
To: [EMAIL PROTECTED]
Subject: RE: SQL not optimised
Hi Ted,
I like your theory but unfortunately still doesn't help speed this up.
According to SYS(3054) it is being optimised but even so still
takes almost
a minute to extract one record.
However I
Nick Causton wrote:Hi all, I have a problem with an app which
retrieves from a table of over 100k records, this is happening via ASP
with ADO so it can only be done using SQL. There is an OrderNo field
which is indexed and to move through the records I am using: * Next
SELECT TOP 1 * FROM
On Thu, Sep 4, 2008 at 2:42 PM, Nick Causton [EMAIL PROTECTED] wrote:
Hi all,
I have a problem with an app which retrieves from a table of over 100k
records, this is happening via ASP with ADO so it can only be done using
SQL.
There is an OrderNo field which is indexed and to move through
Nick Causton wrote:
Hi all,
I have a problem with an app which retrieves from a table of over 100k
records, this is happening via ASP with ADO so it can only be done using
SQL.
There is an OrderNo field which is indexed and to move through the records I
am using:
* Next
SELECT TOP 1
Yikes - what a muddle.
I'll take a stab at it.
Assume the data is in VFP on a web server, running IIS, and IIS is using
ASP with an ado connector to the vfp table.
The iUsr account on the server machine doesn't have rights to the index
file, as some one else reindexed the file , and the rights
Stephen Russell wrote:
--
Is OrderNo int or nchar() ? Hummm clastOrderNumber I am guessing that
it is not a good index candidate for what your doing.
have you checked the statistics?
Here is a good overview with specific
Paul McNett wrote:
1) Maintain a cursor of valid order numbers on the VFP side. For
example, to start with:
sqlexecute(handle, select OrderNo from orders order by OrderNo ASC,
order_nums)
select order_nums
index on OrderNo tag OrderNo
2) When it comes time to view the next/previous
55 matches
Mail list logo
