Simplicty and flexibility!


Excessive time when Form Clear is used.


Started by Bolt-on-Trailers
Search
You will need to Sign In to be able to add or comment on the forum!

Excessive time when Form Clear is used.

Hi All,

I was hoping someone could help me with a simple (Form Clear) issue. My main form has two subforms. When I search and display a record with the Main Form and then press F5 or a button with the Form Clear Action it will take roughly 5 seconds or more to clear the form. The message at the bottom of the form says it is searching another form. It also displays a completely different record which is usually the first record in the table. I have to hit the Clear Button twice and wait for the form to clear which again takes 5 second or more. If I remove the subforms it seems to work better. Thank you all in advance for your help.


Written by Bolt-on-Trailers 28/01/19 at 18:41:44 Dataease [{8}]FIVE

Re:Excessive time when Form Clear is used.

Hi, 
You can probably solve by checking what are the fields in the relationship between the Main and SubForm and index them.  If you have multiple fields you can concatenate to one field (composite key).

Hope this helps,

Josef


Written by Josef Vella 03/02/19 at 11:48:22 Dataease [{8}]FIVE

How indexing can kill your app or make it snappy.

My guess would be that the table you use in Subform is over-indexed.

it is a common misunderstand that indexes make things faster. This is a truth with some major reservations.

Indexes are a necessity for speedy operation as Josef has described but is is also an Achilles heel.

Indexes are files and if you look in the folder containing you app you can see that they sometimes are bigger than the corresponding DBM file.

In these files the index btree is stored and this need to be maintained when you add, modify and delete records. So if you have a lot of indexes this maintenance job takes a long time. 

If you have a subform and delete all the rows in that subform too this job is multiplied by the number of rows in the subform.

Indexes will only be used for the first part of a search so if you have a relationship with 3 field constraints the index will only be useful on the first filter.

SalesDate=01/01/19
CustomerName="British*"
CustomerActive=Yes

SalesData here being indexed will give a good precision but there is no point indexing customername and CustomerActive as they will be sorted in the result set. This is why its important to have the best filter first (the one that gives the least amount of hits).

Imagine if you flipped it and put customerActive first (a lot of people do).

Then you will get a hit on all active customers on a Yes/No field that is very bad for indexing as it will give long branches rather than many short.

We had a customer that did this on a table with over 2 million records.

Their filter was:


CustomerType=Normal

CustomerActive=Yes

SalesDate>current-date-14

So what they wanted was all active customers of "main" type for the last 14 days.
98% of their 2 million customers where of type Normal so the index was wonkey and the result set returned by it was over 1.95 Million!
So that was it for the index.
Then 60% of their customers where still active so we now had 1.18 million still.
But as the records went back over 10 years, only approximatley 9000 was from the last 14 days.

This report took 6 hours to run on a standalone computer.

We swapped the filter over:
SalesDate>current date-14
CustomerActive=Yes
CustomerType=Normal.

The report could be run in the network and it took less than 10 seconds....

This is just an example on how indexes and proper used of them can make your application and if not in practice - break it.

1. Use indexes only when you know why. Don't put them on because it could be useful. Less is more.
2. The better spread an field give, the better is the index and hence faster. 
4. Realise that an index in itself is a data file and the more you have and the more unbalanced they are (indexes on yes/no etc) the slower they are to be maintained.
5. An index file will keep on growing until it is reorganized. If you use a sorting table for instance where you pump data over,manipulate and then delete the indexes will keep on growing util you reorganize that table. SO you can have an empty table with an enormous index file(s)


Written by DataEase 04/02/19 at 11:52:18 Dataease [{8}]FIVE