Dear Ff, what's going on under the hood?

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Dear Ff, what's going on under the hood?

Postby KjetilUrne » Tue Jun 18, 2013 8:11 am

One simple form, 8 fields, none unique.
1 indexed text field (tried both the Fast Text Index checked and unchecked).

Opening the form with its records: no problem.
Browsing through the records: no problem.

When I set a search criteria in the indexed field ("xyz*", which probably applies to 50%+ of the records) Ff displays the first record quite instantly and the apparently starts searching on and on and on. Screen freezes, task manager reports that more ore less 100% of CPU resources is used. Nothing changes for hours.

By the way, the form has 9 million records. Is it simply way beyond what Ff is able to deal with?

According to
viewtopic.php?f=2&t=44
it should handle 16 million records
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Dear Ff, what's going on under the hood?

Postby Graham Smith » Tue Jun 18, 2013 12:09 pm

With that high a percentage of the fields fitting the criteria, you are essentially doing a table scan.

How long is the text field?

Is this just an academic exercise or is there a actual need for this?

If half the records fit a particular search criteria, then there may be ways to narrow the search.

There is a difference between how many record can be held in a table and what's practically manageable.
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: Dear Ff, what's going on under the hood?

Postby KjetilUrne » Tue Jul 02, 2013 7:16 pm

Been away on holiday, Graham :-) (still am)

The text field is/was only 18 chr, so probably no need for the long function

Not really a academic thing, the data-set was a combo from two different Oracle tables, and I hoped to do it rather in FF than SQL
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Dear Ff, what's going on under the hood?

Postby Pete Tabord » Tue Jun 09, 2015 10:04 am

With 9 million records, you have to think about the balance of an index. If the field being indexed has a lot of similar or identical values (half start with XYZ, for example) then the index will be badly unbalanced and may take considerably longer than a flat file search. This is not normally a consideration with less than say a million records.

Equally, since I assume you are importing this data, it would be bad for index balance if the field you are indexing is already in an order, be it ascending or descending. And this can be a problem with quite a small number of records, say 50000 . Best way to resolve this with a reasonable number of records is to cluster the file on some other (non-indexed) field that has no relation to the order of the field you want to index. It might take a long time with 9 million records though!

Basically, the index algorithm we use works best if the field is unique, if the contents are highly random, and the data occurs in a very random order.

The maximum records FF will hold in a single form is 16million. But whether it is practical in terms of performance to approach that limit is another matter. I have seen an application with more than 10 million records, and we are currently working on one with several forms with > 1 million records.
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Dear Ff, what's going on under the hood?

Postby Graham Smith » Tue Jun 09, 2015 12:25 pm

Pete, any particular reason why you dusted off and posted to a 2 year old thread?
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: Dear Ff, what's going on under the hood?

Postby Pete Tabord » Tue Jun 09, 2015 3:18 pm

Because for some reason it was flagged as new activity!
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron