Collecting data in vfp vs fox2x

I need to collect and sum data from related database, something I know
how to do in fox2x based on the speed and efficiency limits of that
platform, but would like to get input on (efficient) ways to do the same
in vfp.

Assuming a large related table with a handful of records meeting the
relation scope, how would I best sum a (non indexed) numeric field field
for the scope of the relation?

here's what I assume would still be most efficient;


select ordent &&parent
onum = ordernum
select orddet &&o2m child
if seek(onum)
CALCULATE sum(qtyord * price) while ordernum = onum TO otot
*or
sum (qtyord * price) TO otot while ordernum = onum
endif
select ordent
goto recno()


Thanks in advance,
Beverly Howard

Dan
Thu Sep 06 09:52:21 PDT 2007

I'd throw away all that code.

onum = ordent.ordernum

SELECT Sum(qtyord * price) As nTotal from ;
orddet ;
WHERE orddet.ordernum = onum ;
GROUP BY 1 ;
INTO Array laTotal
If _Tally > 0
* your sum is in laTotal[1]
Endif


If you need sums for multiple ordernum values, do that all in a single
command by slightly changing the query.

Dan

Beverly Howard [Ms-MVP/MobileDev] wrote:
> I need to collect and sum data from related database, something I know
> how to do in fox2x based on the speed and efficiency limits of that
> platform, but would like to get input on (efficient) ways to do the
> same in vfp.
>
> Assuming a large related table with a handful of records meeting the
> relation scope, how would I best sum a (non indexed) numeric field
> field for the scope of the relation?
>
> here's what I assume would still be most efficient;
>
>
> select ordent &&parent
> onum = ordernum
> select orddet &&o2m child
> if seek(onum)
> CALCULATE sum(qtyord * price) while ordernum = onum TO otot
> *or
> sum (qtyord * price) TO otot while ordernum = onum
> endif
> select ordent
> goto recno()
>
>
> Thanks in advance,
> Beverly Howard

Beverly
Thu Sep 06 10:02:56 PDT 2007

>> do that all in a single command by slightly changing the query <<

While I understand that a single query will meet the needs and reduce
the coding needs, the question is specific to large datasets where
queries often wade through a lot of cpu and drive access to process the
information contained in only two or three records, especially if
process functions are used.

I have yet to see any evidence that vfp is any "smarter" than fox26 and
I do know that often it can be much faster to obtain answers if the
needed data is apparent to the programmer.

If vfp is indeed smarter, I'll use queries in these cases.

Thanks,
Beverly Howard

Gregory
Thu Sep 06 10:58:28 PDT 2007

"Dan Freeman" <spam@microsoft.com> wrote in message
news:eEQsLZK8HHA.5012@TK2MSFTNGP02.phx.gbl...
> I'd throw away all that code.
>
> onum = ordent.ordernum
>
> SELECT Sum(qtyord * price) As nTotal from ;
> orddet ;
> WHERE orddet.ordernum = onum ;
> GROUP BY 1 ;
> INTO Array laTotal
> If _Tally > 0
> * your sum is in laTotal[1]
> Endif
>
>
> If you need sums for multiple ordernum values, do that all in a single
> command by slightly changing the query.
>
> Dan

Dunno what version of vfp he's using

But if it were to be vfp 9, then

create cursor pp (pp I)
select sum(pp) from pp into array qq
?_tally && 1
?qq[1] && null


I'd use this

local laTotal[1]
laTotal = null

> SELECT Sum(qtyord * price) As nTotal from ;
> orddet ;
> WHERE orddet.ordernum = onum ;
> GROUP BY 1 ;
> INTO Array laTotal

? ' Total = ', nvl(laTotal[1], $0)


Gregory
_

Dan
Thu Sep 06 11:07:57 PDT 2007

If raw speed is your main concern and you're truly only dealing with a few
values,

SEEK value
SCAN WHILE field=value
result = result + field
ENDSCAN

will probably be fastest.

It presumes you have the right index to use for the order.

I generally still prefer SQL, though, because I never have to worry about
moving the record pointer possibly upsetting some other routine should the
code ever get called from within a larger routine.

Dan

Beverly Howard [Ms-MVP/MobileDev] wrote:
>>> do that all in a single command by slightly changing the query <<
>
> While I understand that a single query will meet the needs and reduce
> the coding needs, the question is specific to large datasets where
> queries often wade through a lot of cpu and drive access to process
> the information contained in only two or three records, especially if
> process functions are used.
>
> I have yet to see any evidence that vfp is any "smarter" than fox26
> and I do know that often it can be much faster to obtain answers if
> the needed data is apparent to the programmer.
>
> If vfp is indeed smarter, I'll use queries in these cases.
>
> Thanks,
> Beverly Howard

Anders
Thu Sep 06 11:41:12 PDT 2007

CREATE VIEW vSums AS SELECT (SUM(column) FROM Table AS T ;
WHERE T.key = ?myvariable

To get the next set of data for a different variable value
Myvariable = anre value)
REQUERY('vSums')

IF you want more columns FROM that table:
CREATE VIEW vSums2 AS SELECcustid, date, SUM(amount) ;
FROM Table2 WHERE custid = ?variable1 AND date = ?var2

or if you want to pull data from two tables and get more than result row
use a query with
FROM Table1 T1 JOIN Table2 T2 ON T1.key = T2.key + a GROUP BY cluse listing
the columns.

A grid showing the result will autorefresh after the REQUERY(viewalias)
This is also the proctcally only way you can get at remote (non-dbf) data.
-Anders

"Beverly Howard [Ms-MVP/MobileDev]" <BevNoSpamBevHoward.com> wrote in
message news:uz$JDSK8HHA.4584@TK2MSFTNGP03.phx.gbl...
>I need to collect and sum data from related database, something I know how
>to do in fox2x based on the speed and efficiency limits of that platform,
>but would like to get input on (efficient) ways to do the same in vfp.
>
> Assuming a large related table with a handful of records meeting the
> relation scope, how would I best sum a (non indexed) numeric field field
> for the scope of the relation?
>
> here's what I assume would still be most efficient;
>
>
> select ordent &&parent
> onum = ordernum
> select orddet &&o2m child
> if seek(onum)
> CALCULATE sum(qtyord * price) while ordernum = onum TO otot
> *or
> sum (qtyord * price) TO otot while ordernum = onum
> endif
> select ordent
> goto recno()
>
>
> Thanks in advance,
> Beverly Howard

Anders
Thu Sep 06 11:30:52 PDT 2007

Come on Dan, you can't group by an aggregate.
-Anders

"Dan Freeman" <spam@microsoft.com> wrote in message
news:eEQsLZK8HHA.5012@TK2MSFTNGP02.phx.gbl...
> I'd throw away all that code.
>
> onum = ordent.ordernum
>
> SELECT Sum(qtyord * price) As nTotal from ;
> orddet ;
> WHERE orddet.ordernum = onum ;
> GROUP BY 1 ;
> INTO Array laTotal
> If _Tally > 0
> * your sum is in laTotal[1]
> Endif
>

Anders
Thu Sep 06 11:55:51 PDT 2007

CREATE VIEW vSums AS SELECT (SUM(column) FROM Table AS T ;
WHERE T.key = ?myvariable

To get the next set of data for a different variable value
Myvariable = < a new value>
REQUERY('vSums')

IF you want more columns FROM that table:
CREATE VIEW vSums2 AS SELECT custid, date, SUM(amount) ;
FROM Table2 WHERE custid = ?variable1 AND date = ?var2 ;
GROUP BY custid, date

or if you want to pull data from two tables and get more than result row
use a query with
FROM Table1 T1 JOIN Table2 T2 ON T1.key = T2.key + a GROUP BY clause listing
the columns.

A grid showing the result will autorefresh after the REQUERY(viewalias)
This is also the proctcally only way you can get at remote (non-dbf) data.

-Anders

"Beverly Howard [Ms-MVP/MobileDev]" <BevNoSpamBevHoward.com> wrote in
message news:uz$JDSK8HHA.4584@TK2MSFTNGP03.phx.gbl...
>I need to collect and sum data from related database, something I know how
>to do in fox2x based on the speed and efficiency limits of that platform,
>but would like to get input on (efficient) ways to do the same in vfp.
>
> Assuming a large related table with a handful of records meeting the
> relation scope, how would I best sum a (non indexed) numeric field field
> for the scope of the relation?
>
> here's what I assume would still be most efficient;
>
>
> select ordent &&parent
> onum = ordernum
> select orddet &&o2m child
> if seek(onum)
> CALCULATE sum(qtyord * price) while ordernum = onum TO otot
> *or
> sum (qtyord * price) TO otot while ordernum = onum
> endif
> select ordent
> goto recno()
>
>
> Thanks in advance,
> Beverly Howard

Al
Thu Sep 06 12:04:28 PDT 2007

tested on 85MG parent, 824 MB child, 11 records from middle

test 1 used Seek, sum...while
test 2 use Select sum(....

both tested after clear reboot
both returned 0.000 seconds

al


"Beverly Howard [Ms-MVP/MobileDev]" <BevNoSpamBevHoward.com>
wrote in message
news:uz$JDSK8HHA.4584@TK2MSFTNGP03.phx.gbl...
>I need to collect and sum data from related database,
>something I know how to do in fox2x based on the speed and
>efficiency limits of that platform, but would like to get
>input on (efficient) ways to do the same in vfp.
>
> Assuming a large related table with a handful of records
> meeting the relation scope, how would I best sum a (non
> indexed) numeric field field for the scope of the
> relation?
>
> here's what I assume would still be most efficient;
>
>
> select ordent &&parent
> onum = ordernum
> select orddet &&o2m child
> if seek(onum)
> CALCULATE sum(qtyord * price) while ordernum = onum TO
> otot
> *or
> sum (qtyord * price) TO otot while ordernum = onum
> endif
> select ordent
> goto recno()
>
>
> Thanks in advance,
> Beverly Howard

RandyBosma
Thu Sep 06 12:42:05 PDT 2007

Bev-

You've given us a small fragement of what I'm guessing is a larger block of
code that probably sums order detail lines to figure out the order total
amount and then (guessing) puts that number in a field of the ordent table.

SELECT ordent && parent
onum = ordernum

SELECT ordernum, SUM(qtyord*price) AS otot ;
FROM orddet ;
GROUP BY ordernum ;
HAVING ordernum = onum ;
INTO CURSOR oTotal
USE IN orddet && closes order detail table

SELECT ordent
GOTO RECNO()
* and do more.......

Theoretically you'll have one record. The field oTotal.otot will contain the
number I think you're interested in. (This code should also work in fox2x.)

If you describe in more detail what you're trying to accomplish, perhaps
someone can suggest another method of accomplishing your objective. The
SELECT - SQL statement is powerful and generally faster (given appropriate
indexes) than SCANs and SEEKs.

Should you need to prove that the speed is or is not an issue, you can wrap
each code fragment with statements to capture the SECONDS() before and after,
and calculate the elapsed time.

HTH,
Randy Bosma

Beverly Howard [Ms-MVP/MobileDev] wrote:
>I need to collect and sum data from related database, something I know
>how to do in fox2x based on the speed and efficiency limits of that
>platform, but would like to get input on (efficient) ways to do the same
>in vfp.
>
>Assuming a large related table with a handful of records meeting the
>relation scope, how would I best sum a (non indexed) numeric field field
>for the scope of the relation?
>
>here's what I assume would still be most efficient;
>
>select ordent &&parent
>onum = ordernum
>select orddet &&o2m child
>if seek(onum)
> CALCULATE sum(qtyord * price) while ordernum = onum TO otot
> *or
> sum (qtyord * price) TO otot while ordernum = onum
>endif
>select ordent
>goto recno()
>
>Thanks in advance,
>Beverly Howard

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/foxpro-general/200709/1

Beverly
Thu Sep 06 20:10:43 PDT 2007

>> speed <<

Thanks... exactly what I was looking for.

Beverly Howard

Beverly
Thu Sep 06 20:12:49 PDT 2007

>> tested <<

Thanks! Very illuminating.

If possible, was that run on local or network data access?

Beverly Howard

Beverly
Thu Sep 06 20:17:14 PDT 2007

Thanks everyone,

Got a lot of good answers... and, fwiw, am very familiar and comfortable
with select statements.

Sometime in the future, I'm going to try and duplicate some query speed
issues under fox26 which involved complex, conditional functions where
it was discovered that even with the excluding conditions, every
function fired even after it had been excluded.

Thanks again,
Beverly Howard

Lew
Sun Sep 09 05:35:55 PDT 2007

.... or seek / sum ... while
"Dan Freeman" <spam@microsoft.com> wrote in message
news:u54VbDL8HHA.3940@TK2MSFTNGP05.phx.gbl...
> If raw speed is your main concern and you're truly only dealing with a few
> values,
>
> SEEK value
> SCAN WHILE field=value
> result = result + field
> ENDSCAN
>
> will probably be fastest.
>
> It presumes you have the right index to use for the order.
>
> I generally still prefer SQL, though, because I never have to worry about
> moving the record pointer possibly upsetting some other routine should the
> code ever get called from within a larger routine.
>
> Dan
>
> Beverly Howard [Ms-MVP/MobileDev] wrote:
>>>> do that all in a single command by slightly changing the query <<
>>
>> While I understand that a single query will meet the needs and reduce
>> the coding needs, the question is specific to large datasets where
>> queries often wade through a lot of cpu and drive access to process
>> the information contained in only two or three records, especially if
>> process functions are used.
>>
>> I have yet to see any evidence that vfp is any "smarter" than fox26
>> and I do know that often it can be much faster to obtain answers if
>> the needed data is apparent to the programmer.
>>
>> If vfp is indeed smarter, I'll use queries in these cases.
>>
>> Thanks,
>> Beverly Howard
>
>