1 Reply Latest reply on Jul 1, 2014 8:59 AM by philmodjunk

    Single Field making file very large



      Single Field making file very large


           Still using FM11 on FM11 Advanced Server connected via IWP.

           Our file has over 120,000 records. One field in a related table holds approx. 1500 characters per record. Performing Find within this field is slow. Even with desktop connection. This one field makes the file very large. Over 2.7GB.

           This field is text only.

           Better solution ideas?




        • 1. Re: Single Field making file very large

               Can you replace that field with several text fields that hold a portion of this entire text? That could significantly shrink the indexes associated with this text.

               I must assume that this field is an indexed field as you would want that for fastest possible results when performing finds or sorts that refer to this field.

               The indexes for such a field can grow to be very large data structures that thus increase the size of your file. Each and every unique word in this field in each and every record in the table has an entry in that index.

               One trick for speeding up finds that I use when I have a lot of data in a table and also an unindexed field into which I need to specify search criteria is to do a "two stage" find. I perform a find specifying criteria in all possible indexed fields in stage one. Then I return to find mode, specify criteria in any unstored or unindexed fields and constrain the found set. This can be literally hundreds of times faster than a single stage find when there are very large numbers of records in your table.

               This may also work for your large text field if your finds specify criteria in other fields in addition to this text field. You can specify your criteria in the other fields first and then refine the results with a "constrain.." using criteria in the text field.