Search very slow?

[jjb]

Hi.

I have a db which lists people, classes and attendance in 3 separate tables. Currently I have about 20,000 people 300 classes and 12000 attendance records. The people table has a calculated field which counts the number of attendance entries which are marked as 'attended'.

I need to be able to search for people with a certain number of attendance records but it has become very slow to do this search!

Any ideas on optimizations?

[Fitch]

You could make the attendance count a stored number field. You would have to keep it up to date via a script. Perhaps you already have a script for entering attendance?

[John Mark Osborne]

Searching an unstored calculation is going to be slow. Have you tried using your formula in an auto-enter calculation so the value can be stored. Make sure you uncheck the option to "do not replace value (if any)" so it continuously auto-enters. The only issue you may have is getting the calculation to update even with this option unchecked. Since it is based on related values, it may not update is you check or uncheck attendance records.

Maybe if you tell us the prupose of searching for people with a certain amount of attendance records, we can help you with an alternative solution. Are you producing a report? Do you need to export them?

[Søren Dyhr]

Unfortunately aren't you on FM8, so you can utilize the GTRR from a found set of records... Because a way to avoid unstored seaches is to perform it in the childtable instead...

Before FM8 where you urged to gather the foreign key just after having made the search in the related table, stuff it in a global field as a multiline key ...and then issue the GTRR.

http://www.kevinfrank.com/download/multi-gtrr.zip

I have made a template which deals with this attendance problem, which owes the credit to Mikhail Edoshin's clever fastsummary technique which can be studied here:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

It should also be said that, the template my attept to give a more normalized structure to a file send from the poster of this thread:

http://www.fmforums.com/forum/showtopic.php?tid/175355/post/200323/#200323

...which were attempting to do make the search in an almost flat file structure - where the use of globals and chained calculations stood in the way for the required indexing ...to convey smooth seaches.

--sd

Attendanc4.zip

[jjb]

Thanks for the responses. I will try this out and post back my results!

[jjb]

Hi.

I'm getting some script errors in the attached file. See attached showing missing functions?

[Søren Dyhr]

Ah! I'm so sorry, I forgot when $variables were introduced!

Checkout the attachment, and see the changes done above (...admitted, further consideration will give compacter code - given the limitation in commands availiable)

--sd

Attendanc5-1.zip

Edited June 19, 2006 by Guest
Found a quick optimization