Optimising for speed

[AliB]

Firstly, apologies if this question is not quite in the correct place!

I have built an events quotation solution which is working well, but having added the capability to review 'available' stock, the whole thing is becoming less responsive. Let me explain, and hopefully those more knowledgeable could shed some light.....

Basically I have a data file with events, stock and hires tables. The guts of the thing are that an 'event' contains a start date and an end date, and items from stock are added to an event via a record in hires. One portal in the event layout shows all hires (related via eventID), and another portal shows all stock items (this can be filtered by stock category).

On creation of a new record in hires, the start date and end dates are looked up from the current event. These dates are also set in a seperate global 'control' table. A calculation field in the hires table evaluates whether or not the start and end dates of the current hire record 'conflict' with the start and end dates in the global control table, with a result of 1 (conflict) or 0 (no conflict). Each hire record has a quantity field, so, to get closer to the desired result of showing available stock, there is a second calculation field in the hires table - allocatedQuantity - which multiplies the dateTest result by this lineItemQuantity.

Since stock items could be (and are) allocated to more than one event with conflicting dates, there are a couple of calculations in the stock table. A relationship via stockID between stock and hires gives the first calc which is a Sum of allocatedQuantity (from all related records in hires). A final calculation subtracts this figure from the actual number of units in stock.

To summarise:

• Events Start & End dates are set in global control
• Hire record has a calculated dateTest field (result 1 or 0)
• Hire record - quantity is multiplied by dateTest result (allocatedQuantity)
• In stock table alreadyAllocated = Sum of allocatedQuantity (related via stockID)
• Voila - Available = actualNumberinStock - alreadyAllocated

If you're still with me (!?) this does work. When I view an event, the available stock levels (shown in the stock portal) update with the correct quantities. But it slows down the more stock items there are in the portal list, and I fear that as the level of data increases, the refresh delays may become prohibitive.

Is there a better way? What is taking time - is it the calcs within hires or is it the Sum(hires::allocatedQuantity) that's causing the problem....

One final point to note is that my interface and data files are seperate, and the relationships are defined in the interface file which is local - data is remote (although currently on a LAN).

Advice would be much appreciated.

[Søren Dyhr]

Is there a better way? What is taking time - is it the calcs within hires or is it the Sum(hires::allocatedQuant ity) that's causing the problem...

Oh yes this sounds all too familiar to me, it's cunningly fast in the beginning, but when the data grows problem is evident.

I have indeed been barking up this wrong tree, and there are in my humble opinion no way around the bookingtransaction template in this tread:

http://www.fmforums.com/forum/showtopic.php?tid/168633/hl/transactions%7CComment/tp/0/all/1/

--sd

[AliB]

Thank you SD!

I've been wondering whether I actually need to set global dateStart and dateEnd values at all. I only need the hire records to have a dateTest calculation with a result of 1 (conflict) or 0 (no conflict) - could the dates not be incorporated into the calc itself?

Something like:

dateTest = Case ( ( hireStart ≤ currentlyBrowsedEventEnd OR hireEnd ≥ curentlyBrowserEventStart ) ; 1 ; 0 )

I'm sure I tried this before, but it didn't work - surely it should, no?

Is this a Eureka moment, or have I missed something....?

[Søren Dyhr]

I'm against tagging on foreign side of the relation - because it can only be an indexed field by setting it by script (which are bound to be quite slow if 40000 tiny items needs attention, try to investigate Rays template ...where he switches the filtering to the other side of the relation. But you have here to consider if a thing is returned wrecked wouldn't it be a particular canny move to put in on the next consignment - you need to grap a replacement of almost similar value or functionality.

What this means is you can get away with bookings on uniqueID level only until the shop reaches a certain level, but from there would it be on type level as complimentary item types. This is here where transactions comes in, in my huble opinion!

Next rather complicated issue is sets of tightly connected items, where you pluck as a set not as indiviual items. Here would you need to gather via recursive relations structure wich sets are posible at all.

How would your system deal with clients who wish have an option on an entire list of equipment, but still are awaiting the final signatures to go thru, are these items occupied or what?

I'm afraid I post more questions than answers, mainly because I came from rental in a PA-rentalshop/tourproduction kind of environment to a selfimployed subcontractor deal with a company dealing with fasion models photo sessions.

All this have learned me that scaling a solution into a one-size-fit's-all doesn't exist or at least never come easy. With the tourproduction seemed the most ugent problem to solve, the recursive relations to strain the list of sets for impossible combinations of equipment.

But data dicipline is another tough issue to deal with - all these BTW's days after, from the roadies about stuff being on the blink, not being addressed right away when unloading the trucks, it is not done in bad will - but after a tough gig are they excused for being slightly absent minded.

To however be absolutely honest, although I'm a database aficionado, are businesses who concentrate too much on inner mechanics and technicalities doomed to be merged into larger companies with .com'ish psycopats in the driving seat, who have have the (lack of) guts and ability to charm the bankers to massive loans, in order to do the right things but not nessersarily the right resource savy way.

This means huge warehouses of redundant equipment with sparsely applied tools for dealing with the booking, making them able to land the largest contracts going for next to nothing, because there would never be a tight spot in their gear portfolio.

The tourproduction company I partnered in was merged this way into a much larger thing, and it really astonished me to see how little attention the resource allocation tools really needed, contracts were written in a word processor! A spreadsheet gave a rough indication of when and where the various types of equipment was deployed, but if a shortage was discovered did they grap the phone and bought some new.

But I did yesterday explore another way of unstored tagging, which might prove to be rather fast, but I have no expirience in it yet:

http://www.fmforums.com/forum/showpost.php?post/220973/

--sd

[Søren Dyhr]

I have today completed a new template which in essence tightens up the tagging idea as such ...only thing yet to be added is to put in the timeslot as factor in the now semi-tagging.

http://fmforums.com/forum/showtopic.php?tid/180260/post/221268/#221268

--sd