Indexing how to properly implement in Firebird with delphi?



1. Firebird DB with two tables:

— table1 (ID, NAME, IDCOUNT)

— table2 (ID, HASHCOUNT) hashcount — string, the encrypted number.

2. the program in delphi that is able to load this data and display decoding.

When you output the table needs to sort the data by the value of the property that is the number.

Came to the conclusion that you need to make the indexing data and store indices.

But index creation is very taxing happened. Can anyone have idea about this.

The index is created like this:

1. Get all values from table2

2. Desirous and placed in an array, this creates and array indexes.

3. Sort the indexes according to the method of sorting Hoare.

4. Unload the indexes in the database.

While fetching data from database and choose the indexes and sort them.

It seems like everything is necessary. but creating indexes is very slow out.

Data is updated frequently, and this often need to be indexed it is very slow.

If anyone have some thoughts on another solution I will be very grateful.
October 10th 19 at 14:22
2 answers
October 10th 19 at 14:24
bad it is clear that for some reason but Firebird, accordingly, to sort the results when the result can not, because the index must be constructed by the true value of the field and not on hash.
there are 3 solutions:
1. calculated in the regiment we will decode your value and sort in the program (on client): DataTable.Filter:='FIELDNAME',DataTable.Filered:=true;
2. nezamorachivayas for encryption (because your encoder is single-handedly will be revealed as a thoughtful Pro), and stored everything in plaintext
3. will work almost as well as you have a slow current on the server (to write UDF for decryption on the server)
3. Most effective! already testing! the performance is excellent. - Haven commented on October 10th 19 at 14:27
October 10th 19 at 14:26
there is still a 4th option: to write an external function for FB, decrypts the value and work with it directly in the database.
There is a fifth option. Decrypt into a temporary table and work with it. After completion of the work the table to kill.

Find more questions by tags FirebirdDelphi