Totals & Tally's (Cursor vs. Filter Performance) 3

[drosenkranz]

Hello,

I have an app where the main table's records have a unique ID. There are several other (child) tables which may have one or more related records. I'm using VFP 7 with the database container and .cdx's (no free tables). I have not created any persistent relationships.

Sometimes I need to count the number of related records in another table(s), other times I need a total for a specific field (dollar amount) in the related records. Often times - both of these will be true - and there could be as many as four different (child) tables being processed. I'm running the EXE's on each local user's machine and the data is stored up on a Win 2k server. As a "general rule of thumb", which is the better choice performance wise:

1) setting and unsetting filters in the related table(s).

2) SQL-Select records from the related table(s) to a cursor.

3) Locate and then Count For "somevalue" while "somecondition" remains true.

Thanks for your time,

Dave


The 2nd mouse gets the cheese.

 

[fluteplr]

Personally I usually go with the SQL option, but not as you stated it. I would do the following query. (Assuming all you really need are the count and the sum.)

select count(*) cnt, sum(amount) totamt from childtable where parentid = parent.id



Assuming you are have the child tables indexed on the parent key the response time should be very quick.

 

[clarkrj]

I read some years ago an article discussing the pro's & cons of this. It really is all down to the size of the data that is being searched & the size of the subset required.

If you had a massive table 500K records & the subset was lightly to be > 200K then a set filter would be more efficient, if you were searching for 5 records then scan for would be pretty fast.

Then we have this grey area in between where a select statement would be best. In my opinion there is not a hard & fast rule, my suggestion would be to use an SQL statement & if performance became an issue in that area then look at the other options.

 

[DSummZZZ]

clarkrj,
I have to disaagree with your second paragraph. I avoid filters whenever possible. Most of the time, at least with data of any size, filters degrade performance to the point of lockups.

drosenkranz,
If you're talking about setting a filter in a child table to optimize performance of a lookup by a parent table using SET RELATION, I think you'll find that an optimized index will suffice. Again, I never use filters on any table with more than say a couple hundred records, and that's only when I do a manual browse on a temp table I am referencing.

If all you're trying to do is get some counts of records, probably the fastest scenario is to have an index set on a field. Something like:

nTotal = 0
IF SEEK('Jones', 'MyTable', 'LastNameTag')
   COUNT REST WHILE lastname = 'Jones'
   nTotal = _TALLY
ENDIF
?'Total records: '
?? nTotal

That way, Fox will find the first record, then count WHILE the condition is .T., as opposed to FOR, where it will count records until EOF().


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[drosenkranz]

Hello,

I'm using VFP7 and there are lots of text boxes with the parent table's fields and several grids with child tables that appear in the form's four paneled pageframe. The user needs the ability to add, edit, and delete records from any of them. If filters degrade performance on larger tables (and these will be large), then:

1) Is there any "down side" to using a Local View as the source for the text boxes that are displaying the parent record on a Pageframe's panels?

2) Should I use Local Views as the data sources for the grids that are displaying the related (child) records too ?

THanks again for your time,

Dave



The 2nd mouse gets the cheese.