'Find Operation' Going Slow

[FM_Newbie]

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 January 24, 2017 by FM_Newbie

[webko]

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.

[Wim Decorte]

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.

[comment]

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 January 25, 2017 by comment

[FM_Newbie]

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

 

[comment]

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.

 

[FM_Newbie]

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. 

[comment]

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.

 

[bruceR]

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. 

[FM_Newbie]

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.

[webko]

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

[FM_Newbie]

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

[comment]

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

[FM_Newbie]

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 

[Wim Decorte]

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.

[bruceR]

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. 

 

[FM_Newbie]

@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.

[bruceR]

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.