B4J Question Refresh data in SQlite table

[Sergey_New]

The SQlite database has a table persons:

id	key
@1@	1
@3@	2
@4@	3

The id column is filled from an external file, the key column is auto-incremented.
After filling the table, you need to order the data in the id column according to the data in the key column:

id	key
@1@	1
@2@	2
@3@	3

There can be several thousand records in a table.
What is the most efficient way to do this?

 

[Erel]

I'm not sure that I understand, however a table with several thousands of records is a small table and any proper solution should run very fast. Have you encountered any performance issue?

 

[Sergey_New]

Corrected the first post for better readability.
Is it possible to create a query to update the id column according to the second figure?

 

[Sergey_New]

any proper solution should run very fast

I would like to know what solutions there might be.

 

[Sergey_New]

Did this:

db.ExecNonQuery("UPDATE persons SET id='@' || key || '@'")

Or is it better somehow?

 

[DarkoT]

Did this:

db.ExecNonQuery("UPDATE persons SET id='@' || key || '@'")

Or is it better somehow?

I'm not sure that I understand your question... But - when you want to update just xxx records in database (in one table) - this is correct syntax and will be fast (I can not see here other way which will be faster...
Other way is top open cursor = resultset and go to update record by record in Do while loop... (this will be slower).

 

[Sergey_New]

I'm not sure that I understand your question...

In the external text file from where I get the data for the ID column, this data is not ordered. In the first figure, for example, the value "@2@" is missing. I need to establish the correct sequence.

 

[aeric]

I also do not understand what you want to achieve. I guess you can use a temporary table to store the data from external file then use SELECT query and ORDER BY to sort the records first before inserting into the destination table.

 

[Sergey_New]

The "persons" table has several tables linked by the "ID" field. I need to order data in all tables. In general, my example works. The issue is resolved, thanks to everyone!

 

[Sergey_New]

table with several thousands of records is a small table and any proper solution should run very fast.

Unfortunately, I have to return to the question about the execution time of the code:

    db.InitializeSQLite(DBFilePath, DBFileName, True)
    Dim t1,t2 As Long
    db.BeginTransaction
    parser.ReadFile(DBFilePath,GedFileName) 'reading a text file
    db.TransactionSuccessful
    db.ExecNonQuery("PRAGMA foreign_keys = ON")
    t1=DateTime.Now
    db.ExecNonQuery("UPDATE persons SET id='@' || key || '@'")
    t2=DateTime.Now
    Log("Completed in " & ((t2-t1)/1000) & " s")

The "persons" table is linked by the "id" column to five other tables by cascading updates and deletes. Number of fields to update in subordinates tables about 15000.
When testing this table with 300 records (the number of updatable fields in the subordinate tables about 1500) execution time is about 0.1 sec.
When the number of records is more than 5000 (the number of updated fields in the subordinate tables is about 15000), the execution time is more than 30 seconds.
How can you improve performance?

 

[Erel]

I still don't understand. You are overwriting the id column. What's the purpose of the id column? Why not use the key column directly?

 

[Sergey_New]

When working with the database, some rows are deleted, others are added. Column numbering sequence "id" is out of order. This is important for further saving the database to a text file to ensure work with it in other genealogical programs.

 

[Erel]

Why not add ORDER BY keys in your query?

It is impossible to say how this query can be optimized without a full understanding of the tables structures. Maybe you are missing an index and it requires multiple full scans.

You can use the async SQL commands so it will at least not freeze the app.

 

[Sergey_New]

Why not add ORDER BY keys in your query?

What is needed is not sorting, but so that the numbers in the identifier are ordered according to the field "key"

 

[Erel]

1. It looks like you can ignore the id completely and use the key field instead.
2. It is very strange that you modify a key that is used as a foreign key.

As I wrote, we need to better understand the tables in order to actually suggest anything useful.

 

[Sergey_New]

Attached my "raw" project.
Please note that the subordinate tables are filled in sequentially for each person, and not at the end, when all persons have already been recorded.

 

[DarkoT]

Attached my "raw" project.
Please note that the subordinate tables are filled in sequentially for each person, and not at the end, when all persons have already been recorded.

I think the problem is in creation of the table and relationships (foreign keys) from all "persons*" tables (persons_citat, persons_fams, ...) which are related to person table.

Try to create just simple person table without any relationship from another tables and update keys... Table should be like this:

CREATE TABLE persons (
    ident INTEGER NOT NULL
                  PRIMARY KEY AUTOINCREMENT
                  UNIQUE,
    id    TEXT,
    sex   TEXT,
    [key] INTEGER
);

I'm sure the system will update 6k records in two sec...

 

[Sergey_New]

I'm sure the system will update 6k records in two sec...

Will update, of course, very quickly. But you need to update all related tables too

 

[cklester]

Will update, of course, very quickly. But you need to update all related tables too

Without having looked at your code, this question makes me think you have not properly set up your database and tables, or you misunderstand how a relational database is supposed to work.

If you have properly set up your database/tables, you would never need to "update related tables."

As an example, say I have three tables: Users, Books, and Book Users.

Users

ID	first	last
1	Michael	Jordan

Books

ID	Name
1	How to Program with B4X

Book Users

ID	User_ID	Book_ID
1	1	1

I can change the "first" and "last" fields of Users and the "Name" field of Books without ever having to update any other tables due to those changes.

For you, it sounds like if you're doing a people hierarchy, so you would set up something like this:

People

ID	First	Last
1	John	Kennedy
2	Jackie	Kennedy

Relatives

ID	User_ID	Relative_ID	Relation_Type
1	1	2	Husband
2	2	1	Wife

Making changes to any of those fields (other than ID, which you cannot change anyway), does not require changes to any other tables. In fact, even if you delete records in either People or Relatives, you don't have to "update" other related tables because of the cascading deletion of records (if available).

Is it a genealogy? You might need to adjust the structure of your database to better facilitate genealogical records management.

 

[Sergey_New]

Without having looked at your code, this question makes me think you have not properly set up your database and tables, or you misunderstand how a relational database is supposed to work.

I have configured my database and tables correctly and have a good understanding of how a relational database is supposed to work.

you would never need to "update related tables."

Naturally, the data is updated automatically. You have not figured out the task at hand: you need to update all identifiers in the main table, of course, this will update all subordinate tables. The shown example does this, only very slowly.
Why this is needed is another question. I tried to explain that this is related to the Gedcom standard for compatibility with other programs.