Jump to content

Search very slow?


jjb
 Share

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

Recommended Posts

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

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

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

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

This topic is 5651 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.