jjb Posted June 16, 2006 Share Posted June 16, 2006 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? Link to comment Share on other sites More sharing options...
Fitch Posted June 17, 2006 Share Posted June 17, 2006 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? Link to comment Share on other sites More sharing options...
John Mark Osborne Posted June 17, 2006 Share Posted June 17, 2006 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? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted June 17, 2006 Share Posted June 17, 2006 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 Link to comment Share on other sites More sharing options...
jjb Posted June 19, 2006 Author Share Posted June 19, 2006 Thanks for the responses. I will try this out and post back my results! Link to comment Share on other sites More sharing options...
jjb Posted June 19, 2006 Author Share Posted June 19, 2006 Hi. I'm getting some script errors in the attached file. See attached showing missing functions? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted June 19, 2006 Share Posted June 19, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6513 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 accountSign in
Already have an account? Sign in here.
Sign In Now