Jump to content
Sign in to follow this  
FM_Newbie

'Find Operation' Going Slow

Recommended Posts

Hi Guys,

I am having trouble with Find Operation getting slower and slower each day. There are some design consideration as per some articles i have read recently which i will be doing as well.

I inherited this solution from another developer, Find is based on some Calculation fields which are based further on some other calculation fields (both calculation fields have 'Do not store results' option checked)

I know its not the best of designs, i wanted to ask for any possible improvement to Find process as its getting clumsy now.

I have indexed relevant fields though, data in the table isn't huge, around 2000 records with 50 fields but Find is based on unstored calculation fields mainly.

Can PSOS be used in Find Operation? 

Is there any other thing i must do at the beginning.

Thanks much,

Edited by FM_Newbie

Share this post


Link to post
Share on other sites

A Find on unstored calc fields will end up being slow - do those fields _really_ need to be unstored calc, or can they be normal Text/Number fields with auto-enter - then they can be indexed for much faster searches.

Share this post


Link to post
Share on other sites

I'll echo Webko's sentiment here: my take on it is that you have to bite the bullet and retool the solution so that these fields are regular stored fields.  There is no reason - except bad design - why a search on 2,000 records should be slow.

PSoS may help as a band-aid for now but you'll have to test.  You also have to consider whether the server can handle the PSoS load you throw at it.  If the server is marginal then you are just going to tip it over.

Share this post


Link to post
Share on other sites
On 01/24/2017 at 9:30 AM, FM_Newbie said:

i wanted to ask for any possible improvement to Find process

That's hard to say without knowing what those calculations do, and what are you trying to find. In many cases you can simply move the find to operate on the original stored fields.

For example, say you have a field for DateOfBirth and an unstored calculation of Age. Instead of searching the Age field for numbers higher than 18, you can calculate the date 18 years before the current date and search the DateOfBirth field for dates earlier than that.

 

Edited by comment

Share this post


Link to post
Share on other sites

Thanks for the comments. I will try to explain the scenario here to be clear:

There is jobs table and there is clients table. Every Job has more than one clients like where we, pick up, drop off, client who pays etc. Now these clients do change as well during the lifecycle, someone entered wrong client or some other issue. I had to make these client types into jobs table as Calculation fields to update if something changes.

The Search is mainly based on 'Search all jobs with same Pick up Address' or likewise. These target search fields are Calculations and hence non-indexed.

I hope i have made my question clear.

Cheers

 

Share this post


Link to post
Share on other sites

I am afraid this part makes no sense to me:

3 minutes ago, FM_Newbie said:

I had to make these client types into jobs table as Calculation fields to update if something changes.

I don't think you need to have any client details in the Jobs table at all. Except perhaps as a convenience, for display purposes (when a portal would not provide the required view). Even then, there should be no need for one calculation to depend on another.

 

6 minutes ago, FM_Newbie said:

he Search is mainly based on 'Search all jobs with same Pick up Address'

That too is not quite clear. If you're speaking about a particular, single address, then the correct procedure would be to search the address field of the related Clients table. Otherwise it might be necessary to construct some auxiliary relationships to interlink Jobs with common addresses.

 

With all that said, I am still puzzled about one thing: you say you have around 2000 records. That's practically nothing. Even with complex unstored calculations, the find should be instant or very close to that. When you say that the find is "getting slower and slower each day", I can't hep thinking there must be some other factors at play here.

 

Share this post


Link to post
Share on other sites

hi @comment, Sorry for the confusion on this. I will try to clarify a bit more.

On 1/27/2017 at 4:23 PM, comment said:

I don't think you need to have any client details in the Jobs table at all. Except perhaps as a convenience, for display purposes (when a portal would not provide the required view). Even then, there should be no need for one calculation to depend on another.

 

The Layout in discussion is 'jobs' table layout and the calculation fields are there for user conveniece to display data from clients table. Also the problem is that user wants to have search on the same layout on those calculation fields.

what i meant by getting slower every day was a little sweeping comment out of frustration. The search has gotten slower over the period slowly and it seems quite slower since the start of database. 

Share this post


Link to post
Share on other sites
1 hour ago, FM_Newbie said:

user wants to have search on the same layout

Users can still search on the Jobs layout. In fact, they must search on Jobs layout, if they hope to find Job records that meet the criteria. The point is that they need to enter the search criteria into a field of the Clients table - either directly, or through the help of a script.

 

1 hour ago, FM_Newbie said:

the calculation fields are there for user conveniece to display data from clients table.

Well, are you sure you need them? Why not display the actual client data directly in a portal?

 

1 hour ago, FM_Newbie said:

The search has gotten slower over the period slowly

My point still stands. With only 2,000 records, even a grossly inefficient search should be nearly instant.

 

Share this post


Link to post
Share on other sites

It would be very helpful if you could post the file.

Or a clone of the file.

Or a print-to-PDF or screen shot of the field and table definitions. 

Share this post


Link to post
Share on other sites

Attached are three files:

Script: The script used for search on main layout

Field def1: main calculation field used in find

field def2: base field on which the calculation field is based on.

field def2.jpg

Field def1.jpg

Script.jpg

Share this post


Link to post
Share on other sites

Use Not IsEmpty

And make TOP_LocalCompany a text field with an auto-enter calc the same as the calc definition - I'm assuming the actual value doesn't really change very often

Share this post


Link to post
Share on other sites

Hi @webko, Unfortunatley it does change quite often after the record is created.. so can't miss out on any change made ;(

Share this post


Link to post
Share on other sites

Let me make two points here:

First, the calculation does not fit your earlier description. I am not sure if this is a mistake in description or in the actual design, but the calculation returns the first paragraph of the first (in relationship's order) related party's name. This is very different from:

On 01/27/2017 at 10:01 AM, FM_Newbie said:

The Search is mainly based on 'Search all jobs with same Pick up Address' or likewise.

 

Next, I made a test file with 2,000 parent (Jobs) records and 20,000 child (Parties) records and added a calculation field similar to yours. Searching this field produces instant results. I am attaching the file so you can test this for yourself.

 

 

Test.fp7.zip

Share this post


Link to post
Share on other sites

Thank You @comment for the demo. 

I will have a look at the design again to simplify it, i took this from another developer and since it was working so i didnt touch it.

Overall the database file size is near 4 GB now (with container data stored externally separately), plus in the respective jobs table there are total 115 fields. Overall this could be making search slower.

Thanks again for your help 

Share this post


Link to post
Share on other sites

4GB is not massive enough to account for the slowdowns you have described.  But it is big enough to make me wonder if you are really storing all container data externally.  How big is the file when you save it as an empty clone?  And how big when you save it as compacted?  Do things get noticeably faster after you save it as compacted?

115 fields in the job table is iffy; it usually does not take 115 attributes to describe an entity so I would look hard at getting rid of some of these, especially if you have a lot of calcs in there.

Share this post


Link to post
Share on other sites

This isn't really getting anywhere. Statements about trimming have been vague. It may be time to move on and tell the company to work with an experienced developer.

It would be very helpful if you could post the file. Or a clone of the file.

Or a print-to-PDF or screen shot of the (complete) field and table definitions. 

 

Share this post


Link to post
Share on other sites

@Wim Decorte, Clone file size is approximaltely 8MB, I didnt compact the file size recently but it would reduce around 500 Mb in size.

There is just one container field that is using data internally, rest is all stored externally.

Problem with Search going slow was due to bad design, i was doing the search on same layout which was used to display different calculated fields from related tables,  I have moved the Find Operation to another layout which is based on the the actual table. Results are pretty good.

@BruceR, Isn't this the same at the start that we all make silly mistakes ? and every question seems silly untill u know the answer. I would rather try hard to become that 'experienced developer' one day.

Share this post


Link to post
Share on other sites

Answer: no.

To a significant extent, it is about making it easy for volunteers here to see enough detail of the solution to be able to help and being considerate of their time.

Having seen some of the detail of this solution, this is even more apparent to me. Playing twenty questions, revealing tiny unrepresentative and incomplete slices of a solution for us to review, preventing the company from operating on reliable data; those seem to be important considerations.

Share this post


Link to post
Share on other sites

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

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Lape 
  • Similar Content

    • By rivet
       
       
      Most of my medium to heavy scripts are now performed on server. To give the users some feedback while they wait, I wanted a method that was simple and subtle. In this example I ;
      added  a popover button with '+' icon set the feedback message in the popover title bar formatted, sized and positioned the popover   added a OnObjectEnter script tigger to the popover in the script I add a PSoS step with Wait for completion set to 'On' last step of script is Close Popover  
       

    • By TJ53
      Hi, I get error 800 in the "Export Records" script step when executing it as PSOS.
      The file path is set in a variable ($path) as a calculation:
      Get(TemporaryPath)& "tmp.fmp12"
      This script works well when executing it to save the file to the local temporary folder (OS X El Capitan), but returns an error when executing it as PSOS (Windows Server).
      I believe it can be a permissions issue, but I thought the temporary path was accesible from FM Server "by default". Anyway, any insights are appreciated, thanks!
       
       
    • By docasar
      Hi,
      I have a global variable that has been set from previous a script. This global variable is $$abc and has a value of 100.
      I have two fields on Table A, Field1 and Field2. Field 1 has a value of abc, and field 2 is a calculation field with this formula: (Evaluate ( "$$" & Field1 ) ). The calculated result, in this case, should be "100" since that is the value of the Global Variable that I have set on the previous Script.
      However, I don't know what I am doing wrong. I Evaluate the formula on data viewer and it evaluates it correctly, but on the calculation field, it is just not working. I have tried to set the formula as $$abc directly, and still, it does not perform the calculation on the field.
      Any ideas would be much appreciated.
      Thanks a lot in advance for your time.
       
      Hi,
      I just solved the problem, it seems that calculation needs to be set to not store calculation results.
      Thanks a lot for just reading this!
       
    • By milanm
      Has anyone had the following issue and might have a quick fix. I migrated client files from FM Server 13 residing on Mac Server to FM Server 14 residing on Windows Server. All went very well except one thing, which i would be grateful if someone could help me on.   PREVIOUS STATE   - The client had his files hosted on OX platform and with FileMaker 13 Server. - There is a container field in the records that would contain pdf files, these are 1 or more pages document files. - When the user dragged the file into the container field it would display as page thumbnail BOTH on Mac and Windows.   AFTER THE MIGRATION TO WINDOWS SERVER   - files still display thumbnails when dragged on MAC - the files are displaying FILE ICON when dragged into the container on WINDOWS.   Tech boards are suggesting that Windows can not insert the file as picture into the FM container field, but there it happened before. Does anyone have any isight on this behaviour, is it documented anywhere? Thank you
    • By AbsoluteVoice4u
      I'm looking to create a script and place it in a button to exist on two layouts (BACKUP and MAIN), to accomplish the following when activated from either layout:
      Copy current ACCTNUMBER
      If current layout is MAIN
      then go to BACKUP layout and show the same ACCTNUMBER record.
      else if
      current layout is BACKUP
      then go to MAIN layout and show the same ACCTNUMBER record.
       
      ....I'm sure it's a no brainer but right now I'm totally missing some small step to start the script ....help?
       
       
×

Important Information

By using this site, you agree to our Terms of Use.