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

[César Augusto Muñoz Palominos]

Tanks, I'm sorry, I'm newer in gambas, but how I do this, how I do a transaction??  

.

-----Mensaje original-----
De: Benoît Minisini [mailto:gambas at ...1...] 
Enviado el: jueves, 08 de septiembre de 2011 02:37 p.m.
Para: mailing list for gambas users
Asunto: [?? Probable Spam] Re: [Gambas-user] why,Import CSV file into Sqlite3 database is too slow,very very slow en Gambas2 :(

> 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 at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user


Correo explorado por Tixtla McAfee Groupshield 7.0