Quickest way to see if child objects exist

[happymac]

summary of problem - any thoughts on really fast validation attempts to see if child records exist?

 

I have a parent table (called SUMMARY) that has records that store values from a child table (called TRANSACTIONS).  The purpose of the parent table is to store summaries of records from the child table, so instead of the user waiting for an unstored calculation, they can access the parent table's stored value.  

An example of this is ... we might store the total for all sales for one month.  so the child table may have 10,000 'sales' records in the month, while the parent table has one record with a number field (stored) showing the sales for that month.  

We are running into a problem with a maintenance script, where we want to know if there are any SUMMARY records that do not have child records.  In other words, there's a chance the TRANSACTIONS got deleted, and so we need to delete the SUMMARY (parent) record as a result.  

Currently we run a simple loop in the parent (SUMMARY) table and say 

show all records

go to first record

loop

If ( isempty ( child::pktransaction ) ... 

delete record

end if

go to next record (exit after last)

end loop

 

This works, but with 100,000 parent records and over 4 million child records, this is REALLY slow.  Takes about 3-9 seconds per record and therefore takes over 24 hours to complete.

I am trying to find a faster method.  The goal is only to see if there are related records for the summary record.

I tried the following ...

1) changing "isempty" to "isvalid"

2) executeSQL with one query "select pktransaction from transaction where xx = ? and xx = ? and xx = ?"

3) adding an unstored get(foundcount) record in the child table and seeing if that is empty

4) doing an old fashioned find from a layout on the parent (SUMMARY) table for omit * in the child's table pktransaction field.

 

all of which do not seem to improve on the speed.

I thought of maybe doing some kind of subquery using executeSQL but dont know if it will help anyway.  Something like executesql "select pktransaction from transaction where xx = ? and xx=? and xx=? IN (select pktransaction from transaction where date = xx).  though this would significantly reduce the child records in the SQL query by only looking at one month's records (10,000 records instead of 4 million), but am having trouble getting the query written correctly, and dont know if this will help anyway.

 

any thoughts on really fast validation attempts to see if child records exist?

 

thank you!

fyi - all the related fields that I am querying OR are part of the relationship criteria are fully indexed.  

 

Edited May 23, 2016 by happymac

[bruceR]

Enter Find Mode

  Set Field[ childTO::recordID; "*"]

Perform Find

Show Omitted Only

If [ Get(foundCount)]

#do stuff

End if

[happymac]

BruceR ... thank you for your suggestion.  Unfortunately though it is just as slow.  I tried this technique already.

[webko]

Is setting a field in the Parent to the Count of the child, then finding on that field any quicker?

In one of my systems with 215K Parent records and 1.5 million Child records, the following takes about 30 minutes to find Parent records without Children

Show All
Replace Field Contents [Table::zt_Count; Count (Child::zz_Constant)]
Enter Find Mode[]
Set Field [Table::zt_Count; "*"]
Perform Find
Show Omitted Only

 

[LaRetta]

Hi HappyMac  :-)

Instead of setting that unstored calculation childTO::recordID, I suggest you go back to your first field and use Bruce's script searching child::pktransaction instead since it is stored.  It should be much faster.  I think that is what Bruce meant.

Since there are possibilities that the transaction values are changed or records added, it is probably best to tighten your scripting of that entire process using a transaction model.  I suggest you check out Todd Geist's presentations such as this one:  Simple Transactions

 

If you lock this down properly and script deletions, you won't have to do this at all.

Edited May 24, 2016 by LaRetta