Using SQLite to Express Numbering to Tables

Thank you for your help.

The database is limited to SQLite, and there are about 2000 specific tables. I'm looking for a "fast" way to do this by randomly wielding numbers.

id, name, price, index
1,Apple,300,0
2, Strawberries, 150,0
3, plum, 250,0
.
.
.
2000, grapes, 470,0

Suppose there is a table like the one above, and the last data (index) is
I would like to send you unique data that does not overlap from 1 to 2000.

Is this possible with SQL alone?And the point is
Can you do it fast?

I don't understand it with my current knowledge, so I use it with the code as follows.

1. Get data as an array in ORDER BYRANDOM() by SELECT
2. UPDATE the index in the order of the array while rotating with for on the code (UPDATE xxx SET index=i WHERE id=array nth id)
3, Increment index (i++)
Repeat 4,2 for for a few minutes of array elements

If you do it in order, you can do it in this pattern, but it doesn't end with SQL alone, and
The operation is slow.It takes about a minute to process 2000 lines.
Of course, the specifications of the PC are affected, but updating each line with many lines is
I think it's taking a long time.

Can you do this faster, if possible, only with SQL?

Thank you for your cooperation.

c# sql sqlite

2022-09-30 20:46

4 Answers

Are you managing transactions properly?
SQLite is slow by default because it commits every update.

If there are about 2000 cases, select can be done in one transaction, and it will take less than 5 seconds due to the logic of the questioner.
There is no language specification, so if you write in Python, it looks like this.

 connection=sqlite3.connect(DB file, isolation_level='EXCLUSIVE')

# select to list

connection.execute('BEGIN IMMEDIATE')
connection.execution(insert statement, list of tuples (for all cases))# or insert one at a time
connection.commit()

I understand that the language is C#, so it has not been verified yet, but with C#, I think the following would be fine.

public static void execSQL (string SQL)
{
    using (SQLiteConnection cnn = new SQLiteConnection (DATABASE_PATH))
    {
        cnn.Open();
        using(SQLiteTransaction trans=con.BeginTransaction())
        {
            using(SQLiteCommand cmd=cnn.CreateCommand())
            {
                cmd.CommandText=SQL;
                cmd.ExecuteNonQuery();
            }
            trans.Commit();
        }
        cnn.Close();
    }
}

2022-09-30 20:46

If it's just SQL,

create table tbl_new(
  id integer,
  name text,
  price integer,
  idx integer primary key autoinclement
);
insert into tbl_new(id, name, price) select id, name, price from tbl_test;
altern table tbl_test rename to tbl_old;
altern table tbl_new rename to tbl_test;

There is also a way.The type of column is slightly different.
It took less than a second to complete.

2022-09-30 20:46

INTEGER PRIMARY KEY AUTOINCREMENT
for your reference http://www.dbonline.jp/sqlite/table/index9.html

2022-09-30 20:46

As long as it's unique and you know the size, it doesn't matter if it starts from 1 to 427, or if it goes from 6 to 300 instead of 7.

If this condition is acceptable, multiplying the random number by "id maximum +1" and adding "id value" will ensure that it will be unique.

And it can only be updated with SQL.

UPDATE table SET index=(abs(random())%100000)*2001)+id;

2022-09-30 20:46

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656