Slow insert speed

[Pat Dooley]

I'm awfully new to SQLite but a long time Cheetah user.  So I was pretty amazed at how long it took to insert 30,000 records into a new db3 table. Over an hour.  Each line in the text file being read into a$ is preformatted to use in an insert statement (I think). For example:

'00003','DOUDS','HEATHER','M','811 SPAIGHT ST','BOCA RATON','FL','33428'

My example is pretty simple but I often need to take things a step at a time.

Code Select Expand

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
DIM a$
slOpen ("MyInsertTest.db3","C")
slExe "Create Table CONTACTS (CODE,LAST,FIRST,MID,ADDRESS,CITY,STATE,ZIP)
OPEN "data30k2.txt" FOR INPUT AS #1
DO WHILE NOT EOF(1)
    LINE INPUT #1, a$
    slExe "Insert into CONTACTS values (" & a$ & ")"
LOOP
CLOSE #1
END FUNCTION
Is there a way to speed up inserts?  Or to put it another way, am I doing something wrong?
Thanks...

[Paul Squires]

Hi Pat,

The easy solution is to wrap your INSERTS into a transaction. You will see a TREMENDOUS speed boost. Believe me. 

Code Select Expand


slExe "BEGIN IMMEDIATE TRANSACTION "
Do While Not Eof(1)
    Line Input #1, a$
    slExe "Insert into CONTACTS values (" & a$ & ")"
Loop
slExe "END TRANSACTION"


[Paul Squires]

I created a sample 30,000 line text file based on your '00003','DOUDS','HEATHER','M','811 SPAIGHT ST','BOCA RATON','FL','33428'

I wrapped the INSERT in an IMMEDIATE transaction and it only took a second or two to insert the data.

[Pat Dooley]

 Wow!  It is now maybe 3000 times faster.  My faith is wholly restored.
It is interesting that if you rem out all the sqlitening statements and just
read the text file it doesn't really make that much difference.
I need to read up on BEGIN/END TRANSACTION.
Thanks..

[Paul Squires]

SQLite works in auto commit mode by default. This means that each INSERT is implicitly wrapped in a transaction. For a few INSERTs this is acceptable but once you start to do bulk inserts then you will quickly stumble on the overhead involved in locking and dealing with the journal page file. Doing a BEGIN IMMEDIATE turns off the auto commit and gets an exclusive lock on the database. Once you have that, the database engine flies. It is so incredibly fast. You could also get even more fancy and PREPARE the statement outside the DO/LOOP and then BIND the data parameters to the SQL statement in the loop. This saves SQLite from having to recompile the INSERT into its byte code format for every INSERT. However, as you have seen the engine is fast enough without having to do much extra work.

Once I found SQLite and SQLitening for Client/Server, I knew that I found the database of choice for me. Cheetah is good... but SQLite is GREAT !!!! 

[Paul Squires]

Also, if you haven't already, download SQL Expert Personal (free) Edition. It will save you a lot of time when dealign with your SQLite databases.  http://www.sqliteexpert.com/

[mikedoty]

I was still waiting for the original code to finish, came back and you had all solved the problem.
It takes longer to create a dummy input file using WRITE# than to write
to the database using SQLitening!

Code Select Expand


#COMPILE EXE
#DIM ALL
#INCLUDE "..\inc\sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG, a AS STRING, sFile AS STRING
  sFile = "dummy.txt"
  'Kill sFile
  IF NOT Exist(sFile) THEN
    ? "Create a dummy input file"
    OPEN sFile FOR OUTPUT AS #1   'This is much slower than writing to the database
    FOR x = 1 TO 30000
      WRITE #1, FORMAT$(x);"LAST NAME";"FIRST NAME";"I";"ADDRESS HERE";"CITY HERE";"STATE";"ZIP CODE"
      IF x MOD 1000 = 0 THEN
        LOCATE 1,26:PRINT x;
      END IF
    NEXT
    CLOSE #1
  END IF
 
  KILL "dummy.db3"
  LOCATE 2,1:PRINT "Write to database";
  slOpen ("dummy.db3","C")
  slEXE "Create Table CONTACTS (CODE,LAST,FIRST,MID,ADDRESS,CITY,STATE,ZIP)
  PRINT "Begin";
  slExe "BEGIN IMMEDIATE;"
  OPEN sFile FOR INPUT AS #1
  x = 0
  DO WHILE NOT EOF(1)
    LINE INPUT #1, a$
    INCR x
    slEXE "Insert into CONTACTS values (" & a$ & ")"
    IF x MOD 5000 = 0 THEN  'this is slow so only do every so often
      LOCATE 2,18
      PRINT x;
      SLEEP 10
    END IF
  LOOP
  CLOSE #1
  slEXE "END;"
  BEEP

END FUNCTION

FUNCTION Exist(File$) AS LONG
  LOCAL Dummy&
  Dummy& = GETATTR(File$)
  FUNCTION = (ERRCLEAR = 0)
END FUNCTION

[mikedoty]

Code Select Expand


#COMPILE EXE
#DIM ALL
#INCLUDE "..\inc\sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG, a AS STRING, sFile AS STRING
  sFile = "dummy.txt"
  'Kill sFile
  IF NOT Exist(sFile) THEN
    ? "Create a dummy input file"
    OPEN sFile FOR OUTPUT AS #1   'This is much slower than writing to the database
    FOR x = 1 TO 30000
      WRITE #1, FORMAT$(x);"LAST NAME";"FIRST NAME";"I";"ADDRESS HERE";"CITY HERE";"STATE";"ZIP CODE"
      IF x MOD 1000 = 0 THEN
        LOCATE 1,26:PRINT x;
      END IF
    NEXT
    CLOSE #1
  END IF

  KILL "dummy.db3"
  IF ERR = 70 THEN
     ? "Database already in use"
     SLEEP 1000
     EXIT FUNCTION
  END IF
  ERRCLEAR

  LOCATE 2,1:PRINT "Write to database";
  slOpen ("dummy.db3","C")
  slEXE "Create Table CONTACTS (CODE,LAST,FIRST,MID,ADDRESS,CITY,STATE,ZIP)
  PRINT "Begin";
  slExe "BEGIN IMMEDIATE;"
 
  OPEN sFile FOR INPUT AS #1
  x = 0
  DO WHILE NOT EOF(1)
    LINE INPUT #1, a$
    INCR x
    slEXE "Insert into CONTACTS values (" & a$ & ")"
    IF x MOD 5000 = 0 THEN  'this is slow so only do every so often
      LOCATE 2,18
      PRINT x;
      SLEEP 10
    END IF
  LOOP
  CLOSE #1
 
  'Throw in some indexes
  slEXE "Create INDEX I1 on CONTACTS(Code);
  slEXE "Create INDEX I2 on CONTACTS(Last);
  slEXE "Create INDEX I3 on contacts(first);
  slexe "create index I4 on contacts(mid);
  slexe "Create index I5 on contacts(address);
  slexe "create index I6 on contacts(city);
  slexe "create index I7 on contacts(state);
  slexe "create index I8 on contacts(zip);
  slEXE "END;"
 

   'Drop some indexes
'  slexe "Begin Immediate;"
'  slexe "Drop index I1"
'  slexe "Drop index I2"
'  slexe "Drop index I3"
'  slexe "Drop index I4"
'  slexe "Drop index I5"
'  slexe "Drop index I6"
'  slexe "Drop index I7"
'  slexe "Drop index I8"
'  slEXE "END"

  ? "Done":BEEP:SLEEP 500
END FUNCTION

FUNCTION Exist(File$) AS LONG
  LOCAL Dummy&
  Dummy& = GETATTR(File$)
  FUNCTION = (ERRCLEAR = 0)
END FUNCTION

[mikedoty]

Might vacuum after dropping the indexes.
Don't know if SQLite has a size of database function.

Code Select Expand


OPEN "dummy.db3" FOR BINARY SHARED AS #1
PRINT "Size of dummy.db3 before vacuum";LOF(1)
slexe "vacuum"
PRINT "Size of dummy.db3 after vacuum";LOF(1)
CLOSE #1                                       


[Paul Squires]

Mike - your user name has 3 or 4 times in the last day or so. Are you having an identity crisis?