Anchor Buoy Searches Take Forever

[RiddleofSteel]

I have just started to use the Anchor Buoy model for all of my reports, which I generally have really liked. Everything was going great, until I tried to do any type of report with a fair sized amount of data.

My specific example is a backlog report for a manufacturing company. I am running a find on all Open Orders in my Order Items Table, then list them out with the Remaining Qty. Nothing fancy going on here, but with 100k records, it takes over 15 mins to run, then summarize the total. This is of course, unacceptable and could never be used by anyone in our production environment.

What is a reasonable solution to this other then not using Anchor Buoy or some ad hoc global temp table.

We tried to use loops and it wasn't any better.

[Ender]

What makes you think it due to your "achor-boey" design choice?

Are both Server and Client updated to 8v3? 8v2 on either can cause especially slow Finds on unstored calcs.

[RiddleofSteel]

Yes, they are both updated to V3. What makes you think it isn't?

[xochi]

What is taking the time -- the Find, or the Summary?

If it's the Find, then perhaps you can try reworking your find to only be searching on a stored field -- i routinely do finds on 100k+ records, and with non-calc fields it's done in a second or two.

[xochi]

Oh, and just for good measure -- set higher RAM caches everywhere. It's my favorite duct-tape solution.

[Ender]

:P

I wasn't trying to be snotty. I truely haven't heard of performance issues with the Anchor-Boey model (if there are problems, I'm sure people would like to hear about it). I asked because it could be important.

Remember, we don't know anything about your system except what you share with us. You've named a couple of fields and we can assume there's at least two tables involved, but we don't know much about the fields being searched or even which table they reside in. Understanding the structure is important to troubleshooting performance issues. If you'd give us the specifics about your structure (maybe a screenshot of the TOG,) I'm sure it would help.

As I stated before, 8v2 on either Server or Client is known to cause slow finds on unstored calcs, which is why I asked about it.

[Wim Decorte]

Upping the cache is not a silver bullet though.

There is a definite trade-off between performance and the risk of losing data. Where the cutoff is depends entirely on the risk analysis for each deployment

The cache needs to be set according to that risk analysis and after monitoring the cache hit % and cache dirty %.

[RiddleofSteel]

Sorry for the late response. I took a screenie of my TOG's. I'm running a basic backlog that is pulling Items from my Order Entry (OE) TO, and my Order Entry Items (OEIT) TO. My search function is find everything that has a value greater then 0 for the field QtyRemaining.

[Fenton]

Unstored Finds on unstored calculation fields (esp. if more than 1 is involved) are just not fast. That means that you have to try hard to do them in the most efficient manner possible. I doubt that it has all that much to do with "anchor-buoy" per se, as you can add whatever you need to them. Some "redundancy" is inevitable (it's not really redundant, 'cause it's in a different place).

One trick I've found (pun) to speed up unstored Finds considerably is to do any possible stored part of the Find 1st. Then do a Constrain Find on that. If you can eliminate most of the "parent" records, ie., Orders already marked "Closed" (or whatever it is finished ones are),* then your unstored Find will run many times faster.

*It would be worth running a Loop marking Orders closed for this very reason, to keep them out of the running. It could be part of this Find (which would slow it down a little, but not very much; it would pay for itself the next time); or it could be run some other time on its own.

[Søren Dyhr]

I doubt that it has all that much to do with "anchor-buoy" per se

I was too taken by surprice by such a statement, when considering that we here only are dealing with metaphors and which have hardly anything to do, with what's going on under the hood.

I would join the lemmings here that states it has something to do with nature of your seaches! It can't be that difficult to make a spiderweb/octopussy approach that proves the flaws in performance have nothing to do with the metaphors used.

If you already have made such an attempt would this be welcome information to the lot of us, equally charmed by the anchor-buoy ease of use in teamed developements. But if it's only because it's the latest approach picked up by you that makes you blame it on the performance without any thought of investigating it, is it spin on belives!

--sd

[RiddleofSteel]

I can't really understand what SD is trying to say, but I will say that we did try what Fenton said by constraining my found set with a loop, and it did not help. We eliminated the parent records, and only searched on the found set with little improvement.

[Søren Dyhr]

I can't really understand what SD is trying to say

Make the same structure without anchor bouy, and tell us if there is a difference - to me is it far fetched!

--sd

[RiddleofSteel]

Let me clarify that my Reporting System sits out seperately from the Data System. It's essentially a UI.

[Søren Dyhr]

No it's not good enough! You're sitting with what's evidence to you, but how can we be sure you're able distinguish guess from diagnosis?

seperately from the Data System. It's essentially a UI.

Perhaps we're getting somewhere here.... Is this a migrated solution? Where you havn't ditched all file references, and then rebuilded them from scratch??

--sd

[Ender]

I agree. The structure is still not clear, and we still haven't heard where this Find is based, where the fields are based that it's searching, and what kind of fields are being searched. We guessed about an unstored calc being involved, but still haven't seen confirmation.

[Ender]

Also, it would still be helpful to have an answer to xochi's question:

What is taking the time -- the Find, or the Summary?

[RiddleofSteel]

This is not a migrated solution. The Find is taking a long time, and the Summary is taking the normal amount of time.

Here's the structure.

I'm performing my find on a Unstored, Calculated field called QtyRemainingTotal. I'm using a separate UI system to view the Data which has a relationship built from the OEIT (Order Entry Line Items table) to the OE (Order Entry Header Table). The QtyRemainingTotal sits in OEIT.

The Diagram I posted above is my Reporting UI file, which is nothing but TO's and contains 0 actual data. It's completely a UI system.

I already know why my searches are taking so long. I wasn't really perplexed as to why it was taking so long, rather how I can speed it up. I really like the Anchor Buoy model so far, and as I am just learning it, was hoping that someone had already encountered the same problems as I have had and could tell me what I was doing wrong/right.

Edited August 2, 2006 by Guest

[Søren Dyhr]

was hoping that someone had already encountered the same problems as I have

Yes somebody has been thinking along those lines, if not all?? ...but the brightest have found the transaction model is a better way than the spreadsheet'ish.

Sometimes about the 1490'ies a geezer called Luca de Pacioli invented or rather adabted the arabic double sided ledger ...enabling venetial merchants to know their present possesions or stocks in matter of seconds. Way before computerization arrived!

Take a look here: http://www.filemakerpros.com/LULAST.zip

--sd