mrtweaver

Array to SQL

3 posts in this topic

Ok here is my question and i hope it is clear. I have been writing an application, one of the features is to take a text file, pull out the data i require, and put it in a SQL table

Now the thing is these text files can vary in size from 40000 up to 8 million records.

Right now due to the limitations of 5000 characters i have my write to SQL broken down where it writes 120 records per execution, however this slows down as the file size grows, when you get to 1.5 million records lets just say that takes about 15 min.

So in a nutshell here is what i have:

$one = (i, array[0]. array[1]) , (i +1, array[i+1][0], array[i+1][1]........ up to 59

$two = same as above but 60 through 119

$limit = ubound($array)

for i = 0 to $limit step 120

Then in the SQL statement i just add the two like SQL_Query(insert into table (num, stored, linking) values $one, $two

I am sorry this is not the actual code but i do hope it gives you an idea of what i am doing.

I am hoping someone here might have a better or faster approach to dump an array down to an SQL table.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

mrtweaver,

You did not give any real specs/criteria/code so I made up my own.

 I was curious and hadn't done any chained insertion before so I cranked up this little doggy.  I experimented to get the right chunk size by using a large number and stepping it down till I no longer got "term limit exceeded" errors.  There may also be a length threshold but I did not exceed this as my input is simply integers.  I took a cursory glance at the SQLite doc but could not find the limits.

This was run under WIN7 x64 and written to a DB on an SSD drive.

; code based on example by jchd found in this thread "https://www.autoitscript.com/forum/topic/164719-sqlite-in-memory-db-inserts-too-slow/"

#include <array.au3>
#include <date.au3>
#include <file.au3>
#include <sqlite.au3>

; --------------------------------------------------------------------------------------------------------------------------------------

filedelete(@scriptdir & '\test.db3')

_SQLite_Startup()
Local $hDB = _SQLite_Open(@scriptdir & '\test.db3')
If Not $hDB Then _fini()
OnAutoItExitRegister('_fini')
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists t1 (num int, stored int, linking int);')

If $ret <> $SQLITE_OK then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Table Failed'))

; --------------------------------------------------------------------------------------------------------------------------------------

local $iNumRecs = 8000001
local $iMaxInsert = 500
local $sSQLHead = 'insert into t1 values '

local $st = timerinit(), $aTest[$iNumRecs][2]

; generate test file

for $i = 1 to $iNumRecs - 1
    $aTest[$i][0] = random(10000,19999,1)
    $aTest[$i][1] = random(50000,59999,1)
Next

ConsoleWrite('Time to generate test array = ' & stringformat('%02.3f',timerdiff($st)/1000) & ' seconds' & @CRLF)

$st = timerinit()

_SQLite_Exec($hDB, 'begin immediate transaction;')

local $sSQLWork = $sSQLHead
for $i = 1 to ubound($aTest) - 1
    $sSQLWork &= '(' & $i & ',' & $aTest[$i][0] & ',' & $aTest[$i][1] & ')'
    if mod($i,$iMaxInsert) then
        $sSQLWork &= ','
    Else
        _sqlite_exec($hDB,$sSQLWork)
        $sSQLWork = $sSQLHead
    endif
    ;if mod($i,10000) = 0 then ConsoleWrite($i & ' records inserted     ' & _nowtime() & @CRLF)
next

_SQLite_Exec($hDB,';commit;')

ConsoleWrite('Time to insert ' & $iNumRecs - 1 & ' records = ' & stringformat('%02.3f',timerdiff($st)/1000) & ' seconds' & @CRLF)

Func _fini()
    ConsoleWrite('Total changes made = ' & _SQLite_TotalChanges($hDB) & @CRLF)
    _SQLite_Close($hDB)
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

#cs

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\ADMIN010\Documents\sqlite stress test example.au3" /UserParams
+>22:15:07 Starting AutoIt3Wrapper v.14.801.2025.0 SciTE v.3.4.4.0   Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:0409)
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE
>Running AU3Check (3.3.13.19)  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\ADMIN010\Documents\sqlite stress test example.au3
+>22:15:07 AU3Check ended.rc:0
>Running:(3.3.12.0):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\ADMIN010\Documents\sqlite stress test example.au3"
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
_SQLite_LibVersion=3.8.4.3
Time to generate test array = 42.929 seconds
Time to insert 8000000 records = 112.342 seconds
Total changes made = 8000000
+>22:17:44 AutoIt3.exe ended.rc:0
+>22:17:44 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 157.1

#ce

kylomas

Edit2: I found the limits doc here.  I accidentally hit the max term limit.

 

Edited by kylomas
additional info

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

IFF the input file is a .CSV then using the command-line interface (CLI) sqlite3.exe with the .import command will certainly be much faster than any AutoIt code which requires reading the file, splitting rows, then columns into an array and feeding the baby to SQL in chunks. You may have to create the table first with correct datatypes and experiment a bit to make sure the delimiter is correct.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now