Sign in to follow this  
Followers 0
sshrum

Need a faster way to input A LOT of records in SQLite db

4 posts in this topic

#1 ·  Posted (edited)

I'm looking to see if I can just construct a large string with everything in it and then flush it to the db with the INSERT command.

Possible? How do I struct the string?

TIA

EDIT: did some SQL research and just tried something like this:

Insert into xxx (field1, field2) VALUES ("11", "12"), ("21", "22"), ..., ("91","92")

No joy. Doesn't work. Seems to not like the ',' after the ")"s

Seems to work fine if I do it one at a time but that's too slow.

Edited by sshrum

Sean Shrum :: http://www.shrum.net

All my published AU3-based apps and utilities

'Make it idiot-proof, and someone will make a better idiot'

 

Share this post


Link to post
Share on other sites

2.41 secs per insert sounds wrong... wheres you db placed ??

Try to do it like this

open connection to db
$begin = TimerInit()
Insert into xxx (field1, field2) VALUES ("11", "12")
$dif = TimerDiff($begin)
MsgBox(0,"Time Difference",$dif)
close connection to db

Start here if you are new Valuater's AutoIT 1-2-3Looking for an UDF - Look hereDo you need to do it twice - Autoit

Share this post


Link to post
Share on other sites

Hi,

try begin, commit, and then just one sqlexe?

In old versions, I pulled a func;

Func _SQL_CreateRandomDb(ByRef $sDB, ByRef $sDbTable1, $TotalRandomRows, $MaxStringCreate = 100000)
    $sDbTable1 = "test"
    Local $sErrorMsg, $a;
    If Not FileExists($sDB) Then $sDB = FileGetShortName(@ScriptDir & "\" & "sqlite.db3")
    $sDB = FileGetShortName($sDB)
    If FileExists($sDB) Then FileDelete($sDB)
    $nTimerQuery = TimerInit()
    Local $sInput, $iRval, $sOutput
    $sInput = "DROP TABLE IF EXISTS " & $sDbTable1 & " ;"
    $sInput &= "CREATE  TABLE " & $sDbTable1 & "( a INTEGER PRIMARY KEY, b TEXT, c INTEGER, d FLOAT );" & @CRLF
    $sInput &= "DROP INDEX IF EXISTS idxD ;"
    $sInput &= "CREATE INDEX idxD ON " & $sDbTable1 & "(d);" & @CRLF
    _SQLiteExe($sDB, $sInput, $sOutput)
    $TotalRandomRowsINT = Int(($TotalRandomRows) / $MaxStringCreate)
    $s_Insert = "a , b, c , d"
    For $j = 0 To $TotalRandomRowsINT
        $sInput = "BEGIN TRANSACTION;" & @CRLF
        $BaseStringLength = $MaxStringCreate * $j
        For $i = $BaseStringLength + 1 To _Iif($TotalRandomRows> ($BaseStringLength + $MaxStringCreate), $BaseStringLength + $MaxStringCreate, $TotalRandomRows)
            $s_ValueLine = "Data:" & $i & "','" & Random(-10, 10, 1) & "','" & Random(1, 10)
            $sInput &= "INSERT INTO " & $sDbTable1 & "( b, c, d) VALUES('" & $s_ValueLine & "');"
        Next
        $sInput &= "COMMIT TRANSACTION;" & @CRLF
        _SQLiteExe($sDB, $sInput, $sOutput)
        ConsoleWrite('@@ Debug(193) : ' & "createData" & $i & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
    Next
    ConsoleWrite('@@ Debug(193) : ' & "createData" & $i & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
EndFunc   ;==>_SQL_CreateRandomDb
No longer working, but gives the idea?

does that help?

best, randall

Share this post


Link to post
Share on other sites

Begin/Commit worked.

A bit redundant having to pass the fields all the time, but whatever.

Sorta surprised that the normal way didn't work. Coulndn't find anything on the sqlite site to say that it was r wasn't supported.

Thanx


Sean Shrum :: http://www.shrum.net

All my published AU3-based apps and utilities

'Make it idiot-proof, and someone will make a better idiot'

 

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
Sign in to follow this  
Followers 0