

お世話になります。

データベースはSQLite限定で、ある特定の2000件ほどのテーブルがあるとし、  
これにランダムで番号を順に振っていく、『高速でできる』方法を探しています。

id,name,price,index  
1,りんご,300,0  
2,いちご、150,0  
3,すもも,250,0  
.  
.  
.  
2000,ぶどう,470,0

上記のようなテーブルがあるとし、一番後ろのデータ(indexのところ)に  
1から2000の重複しないユニークなデータを振りたいと思っています。

このようなことは、SQLだけで実現は可能でしょうか。そして、肝心なのは  
高速でできるか、ということです。

現在の私の知識では解らないので、コードと併用して下記のように行っています。

1,データをSELECTで ORDER BYRANDOM()で一覧を配列として取得  
2,コード上でforで回しながら、配列の順番にindexをUPDATEする(UPDATE xxx SET index = i WHERE id =
配列n番目のid)  
3,indexをインクリメントする(i++)  
4,2のforを配列の要素数分繰り返す

順序立てて行えば、このパターンで実行できますが、SQL単体で完結しない上に  
動作が遅いです。2000行処理するのに1分ほどかかってしまいます。  
勿論、PCのスペックなども影響はしますが、行数が多い状態で一行毎UPDATEするのが  
時間がかかっていると思います。

これを高速で、できるならばSQLだけで実現することはできますでしょうか。

よろしくお願いいたします。


CC BY-SA 3.0
2015-04-28T08:00:52.307
9640
2015-06-08T10:17:29.413
2015-05-07T01:47:27.267
4236
9374
question
c# sql sqlite
SQLiteを使用して、テーブルに番号を高速で振る方法


きちんとトランザクションを管理していますか？  
SQLiteはデフォルトでは一件更新毎にコミットが走るため、遅いです。

2000件程度ならば一気に`select`したものを一回のトランザクションで済ませれば質問者さんのロジックで5秒もかかりません。  
言語の指定がないのでPythonで書くならこんな感じです。

[code]

    connection = sqlite3.connect(DBファイル, isolation_level='EXCLUSIVE')
    
    # selectしてリスト化
    
    connection.execute('BEGIN IMMEDIATE')
    connection.executemany(insert文, タプルのリスト(全件分)) # または一件ずつinsert
    connection.commit()
    
[/code]

言語はC#とのことですので、未検証ですが、C#だと以下のような感じで良いと思います。

[code]

    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();
        }
    }
    
[/code]


CC BY-SA 3.0
2015-04-28T08:23:55.947
9642
2015-05-07T00:28:52.917
2015-05-07T00:28:52.917
7214
7214
9640
answer


参考までに INTEGER PRIMARY KEY AUTOINCREMENT  
<http://www.dbonline.jp/sqlite/table/index9.html>


CC BY-SA 3.0
2015-04-28T11:03:40.210
9650
2015-04-28T11:03:40.210
9528
9640
answer


どうしても SQL だけでということなら、

[code]

    create table tbl_new (
      id integer,
      name text,
      price integer,
      idx integer primary key autoincrement
    );
    insert into tbl_new (id, name, price) select id, name, price from tbl_test;
    alter table tbl_test rename to tbl_old;
    alter table tbl_new rename to tbl_test;
    
[/code]

という方法もあります。カラムの型が若干変わってしまいますが。  
手元では1秒かからず一瞬で完了しました。


CC BY-SA 3.0
2015-04-29T22:52:44.630
9671
2015-04-29T22:52:44.630
5288
9640
answer


> ユニークであり大小関係さえわかれば、 1からじゅんでなく、427などから始まっても、6の次が7でなく、300になっても 構いません。

この条件でいいのでしたら、ランダムな数に「idの最大値 + 1」を掛けて、「idの値」を足せば、ユニークになることが保証されると思います。

そしてSQLだけで更新できます。

[code]

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


CC BY-SA 3.0
2015-06-08T10:17:29.413
11121
2015-06-08T10:17:29.413
9999
9640
answer