Very slow "constrain" step on 8 record found set

[sgastevep]

Hi everyone,

I am new to FileMaker, but have a long history in other DB related programming/systems/etc.

We have a number of existing MySQL databases for our other in-house systems and web apps, and I wanted to get a chance to see how FileMaker could pull in some of that data to integrate with new solutions or to just do some reporting against that data.

So far, so good, things are working well. However, I am puzzled by the VERY slow performance of a particular find request I am trying to script.

For the Tables in question, all of which are ESS/ODBC to MySQL, we have a Names table with basic constituent information, a Lists table with an entry for each particular list they are on, and a giving_summary table that summarizes their giving in a number of different categories, this last table has an entry for each constituent for each giving category.

All tables are linked on the constituent ID code, from the Names table to the Lists table and from the Names table to the giving_summary table. All of these are one-to-many from Names to each other table.

So, I do a find request for the List in question and it returns a found set of constituents very quickly, and the layout rendering them based on the Names table works well. However, in this case, I want to also only consider giving from one particular category.

I first tried to alter the find request so it included not only the List specification but also the giving category, but that Find Request was taking forever. So, I thought, since the query for just the List returned instantly it would be far faster to first get thet Found Set and then constrain IT against the giving categories. However, this Constrain step also causes the query to take a LONG time to complete.

Shouldn't it be very quick? Shouldn't it only have to consider those eight records in the Found Set and then look back through the relationship tree to find the giving_summary records that match and filter on the category field?

I don't think it is related to scripting errors because if I build a layout that has the Names, Lists and giving_summary related fields on it, and I do a Find on the Lists value (instantly returns) then use the menus to do the constrain step against the giving_summary category field it also takes a very long time.

The giving_summary table in MySQL has an index on the constituent ID and giving category, so I would think this would also be a very quick lookup. I haven't, though, dig through all the way to see what the actual generated SQL for this last (long) step is.

Thanks for any thoughts.

-Steve

[sgastevep]

An Epiphany hit as I was about to go to sleep, so I haven't test it yet, but I think the problem is easy. The reason for the constraint was to remove data that was coming in from a referenced table used in a chart. Well that won't work because I assume it does it's own lookup when it draws itself, the found set is the constituents only not what related data they have. I am so used to writing SQL I just thought I was building the data set and not thinking in FM terms of found sets and related records.

I think I can solve the problem by basing the report off the giving_summary table, so I can restrict categories, then pull the other info in from related fields. Will try tomorrow.

[sgastevep]

Well, I redid it all, but it seems that approach won't work either because then I end up with not getting any constituents on the report who do not have giving since they won't have an entry in the giving_summary table. Plus, it still takes longer than I think it should when doing the combined find on giving category and list, but it is better than it was before.

So, is there any way to restrict what a chart uses for its data set? I thought about things like an external SQL call (don't know if it would work, just thought about it), but that is not available in web or server-automated reports so that would be out.

I would like it to list all constituents, a summary of their giving using the records from giving_summary that only match a certain category, and for each constituent line a summary graph of giving over the last give years for that certain category (each year's giving is in its own field in the giving_summary file), and then at the bottom, in a Trailing Grand Summary part, I would like a pie chart that shows the relative giving for each constituent with respect to the total giving.

Right now the roadblock is getting a restriction on the giving category for charting and text of the giving_summary data.

[Probably ugly - but if I set up a Global table and put a global field in there, and tie it to the category in the giving_summary, then in the script I can retrieve this Global value, save it, change it to what I need for my rep ort, run the report, restore the global value and be done. But this works only if I want just one category, not sure what to do if I need more than one category.]

[cbishop]

As far as the original search, I find that searching any related tables is unnecessarily slow.  It seems to search all records and then push through to the current table.

 

What I do to speed it up - especially when constraining from a smaller set of records - is to create an unstored calc field in the table you're searching in.  The calc would get the contents of the field you want to search for in the relationship.

 

So if you're in TABLE_A, and you're constraining using TABLE_B::STATUS, create a new field in TABLE_A called STATUS_CALC, and then formula is TABLE_B::STATUS (unstored).  Now perform your constrain on TABLE_A::STATUS_CALC instead.

 

I've had searches that were taking up to 3 seconds now perform almost instantly.