I have a set where doing a cross level filter with a from is dreadfully slow when you include any fields from a child (even outside of a form it is slow). The set has two 1:M links as follows:
Each DBF table has approx. 400,000 - 500,000 records
To run the following filter would take upwards of 50+ seconds (snippet of code generated by action scripting)
My thought was to individually query the child first to get a list of Foreign Keys and then use those keys to filter the parent. So I wrote some in-line xbasic on a script to do that.
After the above inline xbasic I have an action script line that runs the filter: (action script snippet)
so this seems to run SUPER fast, basically instantly
My questions are:
I really would welcome and appreciate any feedback and critiques
Code:
t_actlg1 || || || ||===t_clntlg || ||======t_stafflg
To run the following filter would take upwards of 50+ seconds (snippet of code generated by action scripting)
Code:
form.queryrun("T_Clntlg->Clint_Id = Var->v_SrvClient","Date","","No","<Cross Level>",.t.)
Code:
'...Previous action script already prompted user for a value for v_SrvClient dim shared clntlgActlgIDs as c dim shared ActlgIDRecNos as c 'get FK IDs from the child table clntlgActlgIDs = table.external_record_content_get("t_clntlg","actlg1_ID","clint_id","clint_id = " + quote(v_SrvClient)) 'clean up list clntlgActlgIDs = remove_blank_lines(clntlgActlgIDs) clntlgActlgIDs = *remove_duplicatesi(clntlgActlgIDs) 'use FK list to get a list corresponding Recno of the parent ActlgIDRecNos = table.external_record_content_from_key_get("t_actlg1","recno() + ''",clntlgActlgIDs,"Actlg1_Id")
Code:
form.queryrun("is_recno_in(Var->ActlgIDRecNos)","Date","","No","T_Actlg1",.t.)
My questions are:
- Do you guys see any "gotchas" in my code?
- Do you see any issues running this in a network environment?
- Is there an easier or better way to do this?
- Is there a limit that I should keep in mind with using Is_recno_in() or the any variables I feed into it?
I really would welcome and appreciate any feedback and critiques