• Welcome, Guest. Please login.
 
March 29, 2020, 12:57:09 am

News:

Welcome to the SQLitening support forums!


Speed probem

Started by Richard, March 21, 2013, 02:57:07 pm

Previous topic - Next topic

Richard

Hello,

I am translating SQlitening for PUREBASIC.
Following code is inserting 1000 rows in a table and runs 125 ms in local mode.
In remote mode it takes 15 seconds!!
What is wrong in my logic, or what am I overlooking? (in the examples 50000 inserts are done in 1 sec)

Thanks
Richard

Quote;sqliteningmultitest3.pb
XIncludeFile "SQLiteningPB.inc"
Define name.s,id.s,q.s
dll = SQLiteningU_LoadDLL()
dll2 = SQLitening_LoadDLL()
      sluconnect("192.168.1.103")
      err = sluOpen ("pb\tsun1.db") 
   
      If Not err
        StartTime = ElapsedMilliseconds() 
        sluExe("begin") 
        For i= 1 To 1000
          name="W7-" + Str(i)
          q="INSERT INTO test (Lastname) " + " VALUES ('" + name + "')"
          If  sluExe(q)
             Debug slGetError()
          EndIf
        Next
        sluExe("commit")        ; end transaction
        el = ElapsedMilliseconds()-StartTime 
        Debug Str(el)
      Else
        Debug "can't open sample.db3 - Error code: "+Str(err)
      EndIf
      slclose()           
      CloseLibrary(dll)
      CloseLibrary(dll2)
End

cj

This returns the count of 1000 in under 1-second.

#COMPILE EXE "\sql\bin\test3"
#DIM ALL
#INCLUDE ONCE "\sql\inc\sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  DIM ROW AS LONG, COL AS LONG,i AS LONG, rows AS LONG, cols AS LONG
  DIM  s AS STRING, nam AS STRING
  DIM sArray(0) AS STRING
  slConnect "192.168.1.2"
  slOpen "tsun1.db","C"
  slexe "Drop Table if exists Test"
  slExe "Create Table if not exists Test(LastName)"
  s = "Begin;"
  FOR i = 1 TO 1000
    nam="W7-" + STR$(i)
    s = s  + "Insert into test(Lastname) values('" + nam + "');"
  NEXT
  s = s + "End"
  slExe s
  s = "Select count(*) from test"
  slSelAry(s,sArray())
  Rows = UBOUND(sArray(2))
  Cols = UBOUND(sArray(1))
  IF cols = 0 THEN
    ? "no data"
    EXIT FUNCTION
  END IF
  s = ""
  FOR ROW = 1 TO Rows
    FOR COL = 1 TO cols
      s = s + sArray(COL,ROW) + "  "
    NEXT
    s = s + $CRLF
  NEXT
  ? s,,"Results"
END FUNCTION

Richard

Thanks, I understand now one has to make a long string to minimize traffic to the server.
Your code is POWERBASIC code.
As an illustration the following code is PUREBASIC.
It runs at under  300 ms to insert 1000 rows.  GREAT!!!!! :D

Richard
;;sqliteningmultitest4.pb
XIncludeFile "SQLiteningPB.inc"
dll = SQLiteningU_LoadDLL()
dll2 = SQLitening_LoadDLL()
     
      sluconnect("192.168.1.103")
      err = sluOpen ("pb\tsun1.db")   ; laptop & desktop
       
      If Not err
        StartTime = ElapsedMilliseconds() 
        s$=""
        s$ = "Begin;"
        For i = 1 To 1000
          name$=ComputerName()  + Str(i)
          s$ = s$  + "INSERT INTO test (Lastname) " + " VALUES ('" + name$ + "');"
        Next
        s$ = s$ + "commit"
        sluExe(s$)   
        el = ElapsedMilliseconds()-StartTime 
        Debug Str(el)
      Else
        Debug "can't open sample.db3 - Error code: "+Str(err)
      EndIf
      slclose()           
      CloseLibrary(dll)
      CloseLibrary(dll2)
End