Jump to content

Quickest way to see if child objects exist


happymac
 Share

This topic is 2231 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by happymac
Link to comment
Share on other sites

Enter Find Mode

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

Perform Find

Show Omitted Only

If [ Get(foundCount)]

#do stuff

End if

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

This topic is 2231 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.