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

[César Augusto Muñoz Palominos]

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.................



Correo explorado por Tixtla McAfee Groupshield 7.0