Loading issues with a Aspect that contains a Multibox

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Loading issues with a Aspect that contains a Multibox

Postby SamT » Sat Jun 25, 2016 5:36 pm

Afternoon Chaps

Having speed issues with a Aspect that contains a multi box field in a sub form. Aspect is slow to load, save , scroll from one record to another etc. Multibox has its own relationship as one of the fields used in the relationship is a virtual and as such cannot be indexed. Subsequent data looked up into fields based on the result of th multibox use their own relationship with all fields indexed.If I delete the multibox field from the subform all speed returns. I am testing this on my Laptop and I suspect on our server at work the waiting time for the Aspect to load will be unacceptable. Any thought ?

Cheers Sam
SamT
 
Posts: 141
Joined: Sat Jan 16, 2010 12:09 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Gil Fleming » Mon Jun 27, 2016 7:42 am

How many records are available through your multi Box?
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Graham Smith » Mon Jun 27, 2016 12:09 pm

Just thinking out loud... Is the field in question used to enter/edit data or just for display? If it's just for display, it might be faster if there was a separate virtual field that merely displayed the value rather than having to populate the multi-list.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby SamT » Tue Jun 28, 2016 12:23 pm

Hi Chaps,

Sorry for the delay in repsonding.

Gil, 6200 records as its looking up the customer part number in our Stock Records to create a Sales Order.


Graham , When we receive a PO from a customer we need to look up the customers part number which then populates further fields for our Sales Order Acknowledgement.

I am unsure how to improve performance....The opening /scrolling to another record and saving/closing is painfully slow on our server.
SamT
 
Posts: 141
Joined: Sat Jan 16, 2010 12:09 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Graham Smith » Tue Jun 28, 2016 1:13 pm

SamT wrote:Gil, 6200 records as its looking up the customer part number in our Stock Records to create a Sales Order.

If you are saying that there are 6200 records in the form that the multi-box is accessing, then I would say that I hope there are things in the relationship that reduce that number to a reasonable level. Not just for performance but for the user - you cannot scroll through anything even a fraction of that size.

If you are using a relationship based on multiple fields, it would behoove you to created an indexed concatenated key to improve performance.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Gil Fleming » Tue Jun 28, 2016 2:03 pm

Your multibox button loads all 6200 records when you load your aspect, I believe. Have you considered using a wildcard search box instead? This assumes that you know something about the part you are looking for eg does it have 'widget' in its description or the part number begins with 'MACPART' or similar.

Just create a field called, say, locator and add a second field which is called locatorwild. Create a relationship (relstock) between locatorwild and the search field on your target form. The search field on your target should be indexed.

Derive locatorwild as concat("*", locator, "*"). Make it Display/hide. If you always know the first characters of the part you are looking for, you can change the derivation to concat(locator, "*") .
Make a button labelled FIND ITEM, next to your locator field. Set its action to lookup and use relstock as the relationship name.

Then, when you want to populate your order or whatever, you simlply type in some part of the item you are looking for and click the FIND ITEM button. You will then get a popup table of matching items.
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Loading issues with a Aspect that contains a Multibox

Postby SamT » Tue Jun 28, 2016 4:19 pm

Thanks for your input Gentlemen.

This Aspect is for Sales Order Processing ie We receive a purchase order from a Customer and need to send the customer a Sales Acknowledgement and create demand for the products on the Sales Order for our MRP function to pick up.

Although their are 6200 parts in our Stock Details file they include not only parts sold to customers but raw materials, consumable parts , mold tools, CNC machining fixtures etc. Our relationship is created using the CustomerCode field as each component is only sold to one customer ...they are bespoke to the customers design. On the Multibox relationship we also have a second field that filters only current sales item. There is a tick box in our Stock Details file that when ticked makes the product visible to the Multibox drop down.

Since we are searching for the customers part number each customer have a diffent part number system so they can be anything. I want to use the drop down as its a nice clean method but i suspect I will need to go back to the old method of click button to open a related aspect that I can then select the correct part and then return and popiulate the field. As you know this is great but you do get a bit of flashing and screen resizing .

Gil I have just reread your post so think your on the money
SamT
 
Posts: 141
Joined: Sat Jan 16, 2010 12:09 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby SamT » Sun Jul 03, 2016 3:25 pm

Graham,

I wonder if you could explain what you mean by a indexed concatenated key and how does that improve performance over a two field based relationship.

Many thanks

SAM
SamT
 
Posts: 141
Joined: Sat Jan 16, 2010 12:09 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Gil Fleming » Sun Jul 03, 2016 3:32 pm

Sam, you just derive a third field from your 2 important fields (customer number and part number, for example). The derivation would be concatenation (field1, field2). You then index that field for speed.
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Loading issues with a Aspect that contains a Multibox

Postby SamT » Sun Jul 03, 2016 9:16 pm

HI Gil,
I guess that fact that the search for matching data is quicker because it is sorting using one indexed filed only ?

Thanks

Sam
SamT
 
Posts: 141
Joined: Sat Jan 16, 2010 12:09 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Gil Fleming » Mon Jul 04, 2016 10:14 am

Sorry, Sam, I sent that reply from my phone.

I should have mentioned that you need to have an equivalent concatted field on your target form, which should also be indexed.
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Loading issues with a Aspect that contains a Multibox

Postby Graham Smith » Mon Jul 04, 2016 12:44 pm

Gil Fleming wrote:I should have mentioned that you need to have an equivalent concatenated field on your target form, which should also be indexed.

The child/target form is the one that needs the indexed field. The field on the parent doesn't need to be indexed and can even be virtual (assuming the relationship will only be used one way).

A relationship with two fields requires that Ffenics has to create the set based on the first field then create a second set out of the first set. That's inefficient and slows things down.

There's a bit more to it, but I'm on my first cup of coffee and it's hard to compress an hours worth of relational theory and mechanics into a forum response. Let's just say that relational keys need to be created with a good deal of consideration toward efficiency.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 
 

Return to Ffenics 1.x

Who is online

Users browsing this forum: No registered users and 9 guests

cron