• Welcome, Guest. Please login.
 
November 28, 2020, 07:14:47 PM

News:

Welcome to the SQLitening support forums!


Speed optimization for SQLitening (slGetRow/slF)

Started by Bern Ertl, June 24, 2016, 12:59:13 PM

Previous topic - Next topic

Bern Ertl

I have been deep in the weeds trying to optimize my application code over the last couple of weeks.  Yesterday, I decided to have a closer look at the SQLitening code to see if I could realize any optimization gains there.  I discovered some interesting (new to me anyway) possibilities as demonstrated by this test code:#COMPILE EXE
#DIM ALL

FUNCTION PBMAIN () AS LONG

    REGISTER I AS LONG, J AS DWORD
    LOCAL lReps AS LONG
    LOCAL sText AS STRING
    LOCAL qT1 AS QUAD, qT2 AS QUAD
    LOCAL pdwValue AS DWORD PTR, pbValue AS BYTE PTR

    sText ="Some random text"

    lReps = 50000

    DO
        TIX qT1
        pbValue = STRPTR( sText)
        FOR I = 1 TO lReps
            J = @pbValue[ I MOD LEN( sText)]
        NEXT
        TIX END qT1
        TIX qT2
        FOR I = 1 TO lReps
            J = ASC( sText, (I MOD LEN( sText)) + 1)
        NEXT
        TIX END qT2
    LOOP UNTIL MSGBOX( "qT1=" + FORMAT$( qT1, "0,") + ", qT2=" + FORMAT$( qT2, "0,") + ", Do again?", %MB_YESNO) = %IDNO

    DO
        TIX qT1
        FOR I = 1 TO lReps
            J = CVDWD( sText, 5)
        NEXT
        TIX END qT1
        TIX qT2
        pdwValue = STRPTR( sText)
        FOR I = 1 TO lReps
            J = @pdwValue[ 1]
        NEXT
        TIX END qT2
    LOOP UNTIL MSGBOX( "qT1=" + FORMAT$( qT1, "0,") + ", qT2=" + FORMAT$( qT2, "0,") + ", Do again?", %MB_YESNO) = %IDNO

END FUNCTION
According to my testing, using a pointer to access numeric data embedded in a string is surprisingly more efficient than using PowerBASIC's CVDWD/CVBYT functions.  I can only assume this is because the built in statements are doing some error/bounds testing.

So, I tried modifying two functions in SQLiteningClient.BAS:'========================<[ Get Column Text ]>=========================
FUNCTION SQLiteGetColumnText ALIAS "SQLiteGetColumnText" (BYVAL rlSetNumber AS LONG, _
                                                          BYVAL rlCol AS LONG) EXPORT AS STRING
'   Returns the text data for passed SetNumber at passed Col
'   Row data chunk in array format:
'        1 -   4 = Set number as Long.
'        5 -   8 = Position of next row as Dword.
'        9 -  12 = Length of row data chunk.
'       13 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none

   REGISTER llColLen AS LONG, lhColPos AS DWORD   '6/23/16 Bernard Ertl

   LOCAL llSetIX AS LONG
   LOCAL llColCount AS LONG
'   LOCAL llColLen AS LONG            'Made REGISTER
'   LOCAL lhColPos AS DWORD      'Made REGISTER
   LOCAL lhEndOfRow AS DWORD

   LOCAL pbVal AS BYTE PTR, pdwVal AS DWORD PTR   '6/23/16 Bernard Ertl

   ' Find set
   IF UBOUND(tsaSets) > -1 THEN ARRAY SCAN tsaSets(), FROM 1 TO 4, = MKL$(rlSetNumber), TO llSetIX
   IF llSetIX THEN

      ' Init stuff
      '6/23/16 Bernard Ertl  -----------------
      pbVal = STRPTR( tsaSets(llSetIX))
      pdwVal = pbVal
      lhColPos = @pdwVal[ 1] + 4&
      pdwVal = pbVal + lhColPos - 5&
      lhEndOfRow = lhColPos + @pdwVal

'      lhColPos = CVDWD(tsaSets(llSetIX), 5) + 4
'      lhEndOfRow = lhColPos + CVDWD(tsaSets(llSetIX), lhColPos - 4)
      '6/23/16 Bernard Ertl  -----------------

      ' Locate column
      DO WHILE lhColPos < lhEndOfRow
      '6/23/16 Bernard Ertl  -----------------
'         llColLen = CVBYT(tsaSets(llSetIX), lhColPos)
         llColLen = @pbVal[ lhColPos-1]  'Base 0
      '6/23/16 Bernard Ertl  -----------------
         INCR lhColPos
         IF llColLen >= 254 THEN
            IF llColLen = 255 THEN
      '6/23/16 Bernard Ertl  -----------------
'               llColLen = CVDWD(tsaSets(llSetIX), lhColPos)
               pdwVal = pbVal + lhColPos - 1&
               llColLen = @pdwVal
      '6/23/16 Bernard Ertl  -----------------
               lhColPos = lhColPos + 4
            ELSEIF llColLen = 254 THEN
               RESET llColLen
            END IF
         END IF

         ' Check if requested column
         IF llColCount = rlCol THEN
            FUNCTION = MID$(tsaSets(llSetIX), lhColPos, llColLen)
            EXIT DO
         END IF

         ' Bump to next col
         lhColPos = lhColPos + llColLen
         INCR llColCount
      LOOP
   END IF

END FUNCTION

and:'===========================<[ Next Row ]>============================
FUNCTION SQLiteNextRow ALIAS "SQLiteNextRow" (BYVAL rhDab AS DWORD, _
                                              BYVAL rlSetNumber AS LONG) EXPORT AS LONG
'   Bumps to next row within the row data chunk which is stored in the
'   global sets array.  If no more rows in current row data chunk and
'   there are more rows at server then will request next row data chunk
'   from server.
'   Returns -1 if there is a next row or 0 if no row available any other
'   value is an error.
'   Row data chunk in array format:
'        1 -   4 = Set number as Long.
'        5 -   8 = Position of next row as Dword.
'        9 -  12 = Length of row data chunk.
'       13 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none
'   Row data chunk returned from server format:
'        1 -   4 = Length of row data chunk.
'        5 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none

    REGISTER lhCurRowPos AS DWORD                 '6/23/16 Bernard Ertl

   LOCAL llRC AS LONG
   LOCAL llSetIX AS LONG
'   LOCAL lhCurRowPos AS DWORD         'Made REGISTER
   LOCAL lsRowDataChunk AS STRING

   LOCAL pbVal AS BYTE PTR, pdwVal AS DWORD PTR   '6/23/16 Bernard Ertl

   ' Find set
   IF UBOUND(tsaSets) > -1 THEN ARRAY SCAN tsaSets(), FROM 1 TO 4, = MKL$(rlSetNumber), TO llSetIX
   IF llSetIX THEN
      '6/23/16 Bernard Ertl  -----------------
       pdwVal = STRPTR( tsaSets(llSetIX))
       pbVal = pdwVal

      ' Set ptr to first row or bump to next
'      lhCurRowPos = CVDWD(tsaSets(llSetIX), 5)
      lhCurRowPos = @pdwVal[ 1]         'Bytes 5-8  (or 4-7 if considering base 0)
      '6/23/16 Bernard Ertl  -----------------
      IF lhCurRowPos THEN
         ' Bump to next
      '6/23/16 Bernard Ertl  -----------------
'         lhCurRowPos = lhCurRowPos + CVDWD(tsaSets(llSetIX), lhCurRowPos) + 4
         pdwVal = pbVal + lhCurRowPos - 1&
         lhCurRowPos = lhCurRowPos + @pdwVal + 4&
         pdwVal = pbVal
      '6/23/16 Bernard Ertl  -----------------
      ELSE
         ' Set to first
         lhCurRowPos = 13&
      END IF

      ' Check for last row
      '6/23/16 Bernard Ertl  -----------------
'      IF lhCurRowPos >= CVDWD(tsaSets(llSetIX), 9) + 13 THEN
      IF lhCurRowPos >= @pdwVal[ 2] + 13& THEN    'Bytes 9-12  (or 8-11 if considering base 0)

         ' Check if there are more rows available
'         IF CVDWD(tsaSets(llSetIX), lhCurRowPos) THEN
         pdwVal = pbVal + lhCurRowPos - 1&
         IF @pdwVal THEN
      '6/23/16 Bernard Ertl  -----------------

            ' Get next row data chunk
            llRC = DoRequest(%reqGetRowDataChunk, rhDab, rlSetNumber, MID$(tsaSets(llSetIX), lhCurRowPos), 0, lsRowDataChunk)
            IF llRC <> -1 THEN FUNCTION = llRC : GOTO NoMoreRowsAvailable
            tsaSets(llSetIX) = MKL$(rlSetNumber) & MKDWD$(13) & lsRowDataChunk

         ELSE

            ' No more rows available or error so empty the entry for this set and exit function returning non -1
            NoMoreRowsAvailable:
            RESET tsaSets(llSetIX)
            EXIT FUNCTION

         END IF
      ELSE

         ' Update current row position
         MID$(tsaSets(llSetIX), 5) = MKDWD$(lhCurRowPos)

      END IF

      ' We are now pointing to next row so return -1
      FUNCTION = -1

   END IF

END FUNCTION
These two functions are used by slGetRow, slF (and slFX, slFN, slFNX) to retrieve data from the database.  I tested the modified SQLIteningClient.DLL against the original using a test application that reads 20 columns for 3,000 rows of data (ie. 20x slF calls for 3,000 rows = 60,000 data elements read in remote mode).  In my testing, the modified SQLIteningClient.DLL ran an average of 7.6% faster.

It looks to me like the two functions I modified are doing their own bounds checking, so I think the modifications should be safe. 

Bern Ertl

I would appreciate feedback* if anyone else tests this modification.

* [1] did you experience any errors?  [2] did you realize any speed gains?

Jean-Pierre LEROY

Currently I'm in vacation.

I will do some tests when I will be back.

It's an interesting topic.

Regards.
Jean-Pierre