[Gambas-user] why, Import CSV file into Sqlite3 database is too slow, very very slow en Gambas2 :(

[Benoît Minisini]

> Hello:
> 
> 	I want to import CSV file into Sqlite3 db in gambas2, but it's Working 
> too slow, the process takes 2 - 3 hours or more. I used ".import
> csv_file.csv prov"  sqlite3 command and works very fast, one minute or
> less.
> 
> The CSV file has 35,657 records and 4 data per record. The database has
> five tables. Some fields name are repeated in other tables.
> 
> This is the structure of "prov" table in sqlite3 db
> Sqlite3> .schema prov
> CREATE TABLE prov("idkey" INTEGER PRIMARY KEY AUTOINCREMENT, "idsuc"
> NUMERIC, "idrta" NUMERIC, "idprod" NUMERIC, "idcases" NUMERIC);
> 
> *** I don't used INTEGER type because has problems in gambas2, not update
> data, I don't know why :(
> 
> I used two ways to import CSV file:
> 
> First:
> PUBLIC SUB Button1_Click()
>    DIM hFile as File
>    DIM wsales as Result
>    DIM wline as String
>    DIM wdata as String[]
> 
>    hFile  = OPEN "CSV_Filename.csv" FOR READ
>    WHILE NOT Eof(hFile)
>       LINE INPUT #hFile, wline
>       wdata = Split(wline, ",")
>       wsales = Fmain.Conexion.Exec("Insert into prov (idsuc, idrta, idprod,
> idcases) values (" & wdata[0]  & "," & wdata[1]  & "," & wdata[2]  & "," &
> wdata[3]  & ")" ) WEND
>    hFile.Close
> END
> 
> Second:
> PUBLIC SUB Button1_Click()
>    DIM hFile as File
>    DIM wsales as Result
>    DIM wline as String
>    DIM wdata as String[]
> 
>    hFile  = OPEN "CSV_Filename.csv" FOR READ
>    WHILE NOT Eof(hFile)
>       LINE INPUT #hFile, wline
>       wdata = Split(wline, ",")
>       wsales = Fmain.Conexion.Create("prov")
>       wsales["idsuc"] = wdata[0]
>       wsales["idrta"] = wdata[1]
>       wsales["idprod"] = wdata[2]
>       wsales["idcases"] = wdata[3]
>       wsales.Update
>    WEND
>    hFile.Close
> END
> 
> Both codes work fine, but very slow 2 hours or more, why?, I used ".import
> csv_file.csv prov"  sqlite3 command and works very fast, one minute or
> less
> 
> Somebody help me.................
> 
> 

It's slow because you don't use transaction. Do the entire import inside a 
transaction.

Regards,

-- 
Benoît Minisini