> 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 ------------------------------------------------------------------------------ Doing More with Less: The Next Generation Virtual Desktop What are the key obstacles that have prevented many mid-market businesses from deploying virtual desktops? How do next-generation virtual desktops provide companies an easier-to-deploy, easier-to-manage and more affordable virtual desktop model.http://www.accelacomm.com/jaw/sfnl/114/51426474/ _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user