Performance issue

[DP]

I do like to use SELECT-SQL statements yet I just found out that for doing
so I am paying a big price in performance. So what am I doing wrong?

Here is an example. Invoice master file has the master record and is openend
in the Dataenvironment with alias a_mast. the detail file is opened as
a_tran. the mast file is about 80 meg the tran file about 220 meg. I got to
get for a small range of invoices the box count so initialy, Both mast and
tran files have an invno index (and a few more indexes)

SELECT a.invno, a.custno, .........SUM(b.boxcnt) AS boxcnt ;
FROM a_mast a, a_tran b WHERE a.invno = b.invno and a.invno >= ln_beginv AND
a.invno <= ln_endinv AND b.arstat # "V" GROUP BY a.invno ;
INTO TABLE <restablename>

Takes about 9 seconds. (also tried BETWEEN(invno, ln_beginv, ln_endinv) with
no noticable time difference
Time was established through the file created by "SET COVERAGE ..."

Changeing above to

CREATE TABLE <restable> (invno C(8), .......etc etc)
USE restable ALIAS a_res
SELECT a_mast
SET ORDER TO tag invno
SET KEY TO RANGE ln_beginv, ln_endinv

SCAN FOR arstat # "V"
SCATTER MEMVAR
SELECT a_tran
SET KEY TO a_mast.invno
SUM boxcnt TO m.boxcnt
INSERT INTO a_res FROM memvar
SELECT a_mast
ENDSCAN

Took after adding up all the times produced by the COVERAGE FILE between 0.1
and 0.3 Seconds depending on the size of the result set (typically 10-50
invoices)

SO WHY is doing it the "old fashioned" way so much faster? (No noticable
time difference between VFP7.0 or 9.0 on that one)

TIA

[Lee Mitchell]

Hi DP:

I am not sure, but does VFP see the SELECT-SQL command as optimized? Use
SYS(3054) to find out. Does tuning VFP's memory with SYS(3050) help?

http://support.microsoft.com/default.aspx?scid=KB;EN-US;156551
http://support.microsoft.com/default.aspx?scid=KB;EN-US;176483

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/info/product.aspx?view=22&pcid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

Post by DP
I do like to use SELECT-SQL statements yet I just found out that for doing
so I am paying a big price in performance. So what am I doing wrong?
Here is an example. Invoice master file has the master record and is openend
in the Dataenvironment with alias a_mast. the detail file is opened as
a_tran. the mast file is about 80 meg the tran file about 220 meg. I got to
get for a small range of invoices the box count so initialy, Both mast and
tran files have an invno index (and a few more indexes)
SELECT a.invno, a.custno, .........SUM(b.boxcnt) AS boxcnt ;
FROM a_mast a, a_tran b WHERE a.invno = b.invno and a.invno >= ln_beginv AND
a.invno <= ln_endinv AND b.arstat # "V" GROUP BY a.invno ;
INTO TABLE <restablename>
Takes about 9 seconds. (also tried BETWEEN(invno, ln_beginv, ln_endinv) with
no noticable time difference
Time was established through the file created by "SET COVERAGE ..."
Changeing above to
CREATE TABLE <restable> (invno C(8), .......etc etc)
USE restable ALIAS a_res
SELECT a_mast
SET ORDER TO tag invno
SET KEY TO RANGE ln_beginv, ln_endinv
SCAN FOR arstat # "V"
SCATTER MEMVAR
SELECT a_tran
SET KEY TO a_mast.invno
SUM boxcnt TO m.boxcnt
INSERT INTO a_res FROM memvar
SELECT a_mast
ENDSCAN
Took after adding up all the times produced by the COVERAGE FILE between 0.1
and 0.3 Seconds depending on the size of the result set (typically 10-50
invoices)
SO WHY is doing it the "old fashioned" way so much faster? (No noticable
time difference between VFP7.0 or 9.0 on that one)
TIA

[Craig Berntson]

Make sure you readup in the help file on Rushmore so that you can optimize
the query.
--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net

Post by DP
I do like to use SELECT-SQL statements yet I just found out that for doing
so I am paying a big price in performance. So what am I doing wrong?
Here is an example. Invoice master file has the master record and is openend
in the Dataenvironment with alias a_mast. the detail file is opened as
a_tran. the mast file is about 80 meg the tran file about 220 meg. I got to
get for a small range of invoices the box count so initialy, Both mast and
tran files have an invno index (and a few more indexes)
SELECT a.invno, a.custno, .........SUM(b.boxcnt) AS boxcnt ;
FROM a_mast a, a_tran b WHERE a.invno = b.invno and a.invno >= ln_beginv AND
a.invno <= ln_endinv AND b.arstat # "V" GROUP BY a.invno ;
INTO TABLE <restablename>
Takes about 9 seconds. (also tried BETWEEN(invno, ln_beginv, ln_endinv) with
no noticable time difference
Time was established through the file created by "SET COVERAGE ..."
Changeing above to
CREATE TABLE <restable> (invno C(8), .......etc etc)
USE restable ALIAS a_res
SELECT a_mast
SET ORDER TO tag invno
SET KEY TO RANGE ln_beginv, ln_endinv
SCAN FOR arstat # "V"
SCATTER MEMVAR
SELECT a_tran
SET KEY TO a_mast.invno
SUM boxcnt TO m.boxcnt
INSERT INTO a_res FROM memvar
SELECT a_mast
ENDSCAN
Took after adding up all the times produced by the COVERAGE FILE between 0.1
and 0.3 Seconds depending on the size of the result set (typically 10-50
invoices)
SO WHY is doing it the "old fashioned" way so much faster? (No noticable
time difference between VFP7.0 or 9.0 on that one)
TIA

[dp]

Thanks - read up and changed some indexes - queries are now full optimized
and faste - but still a bit slower than the SET KEY TO RANGE thing - but
workable.

Post by Craig Berntson
Make sure you readup in the help file on Rushmore so that you can optimize
the query.
--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net

Post by DP
I do like to use SELECT-SQL statements yet I just found out that for doing
so I am paying a big price in performance. So what am I doing wrong?
Here is an example. Invoice master file has the master record and is openend
in the Dataenvironment with alias a_mast. the detail file is opened as
a_tran. the mast file is about 80 meg the tran file about 220 meg. I got to
get for a small range of invoices the box count so initialy, Both mast and
tran files have an invno index (and a few more indexes)
SELECT a.invno, a.custno, .........SUM(b.boxcnt) AS boxcnt ;
FROM a_mast a, a_tran b WHERE a.invno = b.invno and a.invno >= ln_beginv AND
a.invno <= ln_endinv AND b.arstat # "V" GROUP BY a.invno ;
INTO TABLE <restablename>
Takes about 9 seconds. (also tried BETWEEN(invno, ln_beginv, ln_endinv) with
no noticable time difference
Time was established through the file created by "SET COVERAGE ..."
Changeing above to
CREATE TABLE <restable> (invno C(8), .......etc etc)
USE restable ALIAS a_res
SELECT a_mast
SET ORDER TO tag invno
SET KEY TO RANGE ln_beginv, ln_endinv
SCAN FOR arstat # "V"
SCATTER MEMVAR
SELECT a_tran
SET KEY TO a_mast.invno
SUM boxcnt TO m.boxcnt
INSERT INTO a_res FROM memvar
SELECT a_mast
ENDSCAN
Took after adding up all the times produced by the COVERAGE FILE between 0.1
and 0.3 Seconds depending on the size of the result set (typically 10-50
invoices)
SO WHY is doing it the "old fashioned" way so much faster? (No noticable
time difference between VFP7.0 or 9.0 on that one)
TIA