• Welcome, Guest. Please login.
 
July 17, 2019, 04:23:33 pm

News:

Welcome to the SQLitening support forums!


Insert speed tests with and without binding

Started by cj, May 24, 2015, 02:19:04 am

Previous topic - Next topic

cj

'Test inserting with and without binding
'Bind with V option is 3 times faster than bind  100,000 records
'Bind with V option is 2 times faster no binding 100,000 records
'To encrypt or compress requires adding C or N to $Bind
'Tests were performed with $Bind = "T" only
'----------------------------------------------------------------
'The real performance is seen when connecting to remote server
'Inserting 1,000 records took:
'Bind    30,639 ticks
'Bind+V      93 ticks
'No bind3 31,262 ticks
'---------------------------------------------------------------
'The huge difference is in the number of trips to the server
'and not having to prepare each insert when using Bind+V.
'Inserted 200,000 to remote server in under a second!
'Always place multiple inserts within a transaction for speed.
'
%RunBindTest   = 0
%RunBindTestV  = 1 '<--- really fast inserts
%RunNoBindTest = 0
%NumberOfRows = 100000
#DIM ALL
#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
$Bind = "T" 'TC TN TCN
DECLARE FUNCTION GetTickCount LIB "KERNEL32.DLL" ALIAS "GetTickCount" () AS DWORD
'
FUNCTION PBMAIN () AS LONG 'Insert test.bas

  LOCAL x AS LONG
  LOCAL time1Start,Time1End,time2Start,time2End,Time3Start,Time3End AS DWORD
  IF INSTR($Bind,"N") THEN slSetProcessMods "K" + STRING$(32,0) 'encrypt key
  slconnect  "intimacy",23947
  slopen     "sample.db3","C"
  slexe      "drop table if exists test"
  slexe      "create table if not exists test(c1 integer primary key,c2 text)"

'--------------------------------------------------------------------------------------
IF %RunNoBindTest = 0 THEN GOTO BindTestWithV
  Time3Start = GetTickCount            'NO BINDING
  slexe "Begin exclusive"
  FOR x = 1 TO %NumberOfRows
    slexe "insert into test values(null,'column 2')"
  NEXT
  slexe "End"
  Time3End = GetTickCount
'--------------------------------------------------------------------------------------
  slexe  "drop table if exists test"
  slexe  "create table if not exists test(c1 integer primary key,c2)"
'--------------------------------------------------------------------------------------
  Time1Start = GetTickCount            'BIND WITHOUT V option
  slexe "begin exclusive"
  FOR x = 1 TO %NumberOfRows
    slexeBind  "Insert into test values(null,?)", _
                  slBuildBindDat("column 2",$Bind)
  NEXT
  slexe "end"
  Time1End = GetTickCount

BindTestWithV:
IF %RunBindTestV = 0 THEN GOTO Totals
'--------------------------------------------------------------------------------------
  slexe  "drop table if exists test"
  slexe  "create table if not exists test(c1 integer primary key,c2)"
'--------------------------------------------------------------------------------------
  REDIM sRow(%NumberOfRows) AS STRING   'BIND WITH V option
  Time2Start = GetTickCount
  slexe "begin exclusive"
  FOR x = 1 TO %NumberOfRows
    sRow(x) = slBuildBindDat("column 2",$Bind)
  NEXT
  slExebind  "insert into test values(null,?)",JOIN$(sRow(),""),"V1" 'V + ?'s
  slexe "end"
  Time2End = GetTickCount

Totals:
'--------------------------------------------------------------------------------------
  ? "Bind " + FORMAT$(Time1End-Time1Start,"#,") + $CR + _    '983 ticks
    "Bind+V " + FORMAT$(Time2End-Time2Start,"#,") + $CR +_   '312 ticks
    "Normal " + FORMAT$(Time3End-Time3Start,"#,")            '655 ticks
  ? "Create recordset test",,"Click to start"
  LOCAL other$
  IF INSTR($Bind,"N") THEN Other = "D2" 'decrypt   column 2
  IF INSTR($Bind,"C") THEN Other+= "U2" 'uncompress column 2
  slSelAry "select c1,c2 from test",sRow(),"Q9c" + other$
  ? "Click to write to disk",,USING$("Rows #,",UBOUND(sRow))
  ArrayToDisk sRow(), "JUNK.TXT"
  ? "Write complete. Click to view file",,"Click to view"
  ShellIt "JUNK.TXT"
END FUNCTION
'--------------------------------------------------------------------------------------
FUNCTION ArrayToDisk(sArray() AS STRING, sFileName AS STRING, OPTIONAL ViewFile AS LONG) AS LONG
  LOCAL hFile AS LONG
  hFile = FREEFILE
  OPEN sFileName FOR OUTPUT AS #hFile
  IF ERR THEN FUNCTION = ERR:BEEP:EXIT FUNCTION
  PRINT #hFile, sArray()
  IF ERR THEN BEEP:FUNCTION = ERR
  CLOSE #hFile
  IF ERR = 0 AND ISMISSING(ViewFile) = 0 THEN
     SLEEP 500
     ShellIt sFileName
  END IF
END FUNCTION
'--------------------------------------------------------------------------------------
SUB Shellit(sFileName AS STRING)
  ShellExecute BYVAL 0, "OPEN", sFileName+$NUL, BYVAL 0, $NUL, BYVAL 1
END SUB