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 FUNCTIONAccording 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 FUNCTIONThese 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.
I would appreciate feedback* if anyone else tests this modification.
* [1] did you experience any errors? [2] did you realize any speed gains?
Currently I'm in vacation.
I will do some tests when I will be back.
It's an interesting topic.
Regards.
Jean-Pierre