• Welcome, Guest. Please login.
 
June 05, 2020, 12:46:02 am

News:

Welcome to the SQLitening support forums!


Storing UDT as BLOB in SQLitening

Started by Klaus Henn, September 26, 2012, 04:08:31 pm

Previous topic - Next topic

Klaus Henn

For a new version of one of my programs I wanted to replace TSUNAMI / TTDS by SQLitening. In SQLitening I use 2 fields in a table, one field which is used as primary index (16 chracter) and one field (BLOB) containing a UDT (4kb) . The data are loaded to SQLitening record for Record by slBind. The Tsunami version only uses the UDT (4kB)
A small test program showed huge speed differences in the comparison of SQLitening and TTDS/TSUNAMI. Both systems are used on the same PC in local mode.

Inserting 10000 Records
SQLitening 108 sec.          TTDS / TSUNAMI. 3 sec.

Reading 10000 Records
SQLitening 113 sec.          TTDS / TSUNAMI. 2 sec.


I expected a speed loss when converting from a simple record manager to a SQL database, but this difference is too big.
How can I speed up the SQLiting version?




Function Write_Record_Wert_BIN(ByVal MWD As MessWert_Dec) As Long
    Local Record As String
    Local Schluessel As String

    Record = MWD   
    Schluessel = MWD.Schluessel
   
    slExeBind slBuildInsertOrUpdate ("Wert_BIN", Schluessel & $Nul & "?"), slBuildBindDat(Record)                                         

End Function



Function Read_Record_Wert_BIN(MWD As MessWert_DEC, ByVal Schluessel As String) As Long
    Local Record As String
   
    slSel("Select * From Wert_BIN where Schluessel = '" & Schluessel & "'" )
    If slGetRow Then
        Record = slFX(2)
        LSet MWD = Record
        Function = %WL_OK
    Else
        Function = %WL_Not_OK
    End If
End Function

D. Wilson

Wrap your inserts in a transaction. You can insert thousands of records in one call to the server. You will need to create the sql statement then use pass that statement to the server. Sqlitening will be much faster than Tsunami. ( I have used both ).

Paul Squires

Are you calling Read_Record_Wert_BIN for every record you are trying to retrieve?
If yes, then that would be 10,000 times you are calling slSel to compile the SQL statement and retrieve the record. You should only have to run the slSel once in order to retrieve the 10,000 records.

Klaus Henn

Thank you for your suggestions!

My example was used get an idea of the average reaction time of the both systems. In the mode I used both systems in my example  TSUNAMI / TTDS  is aout 50 times faster.

In my real application there is a minor difference. While  TSUNAMI / TTDS  responds in "real time", there is a noticeable delay in the responce  of SQLitening.  I think  I need to change the conception of my program  to use the full speed of SQLitening.

Fred Meier

You can insert all your records with just one slExeBind by using the V ModChar.  Set the doc below and also see ExampleB.

QuoteslExeBind                  (rsStatement String, rsBindDats String, [rsModChars String]) Long
   Statement contains one (no multiples like slExe) Insert or Update
   statement.  Will replace all of the '?' expressions in Statement with the
   corresponding BindDat entries passed in BindDats.  A BindDat entry is the
   specially formated string returned by the slBuildBindDat function.  Pass
   BindDats as multiply concatenated BindDat entries to handle multiple '?'
   expressions.  The first '?' will be replaced with the first BindDat entry,
   the second '?' with the second BindDat entry, etc.  Excess '?' expressions
   will be set to Null while excess BindDat entries will raise an error.
   You may use slExeBind to affect one or multiple records.  The default is to
   affect only one record.  Add the V ModChar (see below) to affect multiple
   records.  Using the V ModChar is a highly effecent/fast way to insert or
   update many records.  Using slExeBind this way tells SQLite3 to only
   prepare/compile the SQL statement one time and then affect each row re-using
   the prepared/compiled statement. See the example below and also look at
   ExampleB.Bas.
   The following example will insert four columns into a single record:
     The column types are --
     1 = Blob, 2 = Compressed Text, 3 = Compressed Encrypted Blob, 4 = Double .
        slExeBind "Insert into T1 values(?, ?, ?, ?)", _
                                         slBuildBindDat("This is some Blob data") & _
                                         slBuildBindDat("This is some compressed Text", "TC") & _
                                         slBuildBindDat("This is a compressed and encrypted Blob", "CE") & _
                                         slBuildBindDat("123.456", "D")
   The following example will insert three columns into 50 records:
     The column types are --
     1 = Ingeger 32 bit, 2 = Null, 3 = Integer 64 bit
        dim lsaRows(1 to 100) as String
        for llDo = 1 to ubound(lsaRows)
           lsaRows(llDo) = iif$(llDo mod 2, slBuildBindDat(format$(llDo), "i"), slBuildBindDat(format$(llDo + 1000^4), "I"))
        next
        slExeBind "Insert into T1 values(?, Null, ?)", join$(lsaRows(), ""), "V2"
     Note the use of an array and the join$ command which is much faster then
     standard concatenation.
   ModChars:
      Em = Return errors. This will override the global return errors flag.
           m is the optional message display modifier and can be:
              0 = No message is displayed.  This is the default.
              1 = Display a warning message with OK button.  Error is
                  returned when OK pressed.
              2 = Display a question message with OK and Cancel buttons.
                  If they press OK, error is returned.  If they press
                  Cancel, will exit process.
      e  = Do not return errors, display message and exit process. This
           will override the global return errors flag.
      R =  Release all named locks owned by this connection after doing
           the ExeBind.
      Vn = Optional.  Required if you want to Insert/Update more than one record.
           Using this to affect many records will greatly improve the speed of
           Inserts/Updates.  n is the number of "?"s in the Insert/Update statement.
           If omitted or n is zero then only one record will be affected.  If n is
           >zero then it must equal the number of "?"s.  See example above.
   Returns zero if processed OK. Else, depending on ModChars and the global
   return errors flag, will either display error and exit or will return
   the error number.