Author Topic: Efficiency improvements for SQLiteningServer  (Read 121 times)

Bern Ertl

  • Master Geek
  • ****
  • Posts: 470
  • Excellent
    • View Profile
    • InterPlan Systems
Efficiency improvements for SQLiteningServer
« on: January 11, 2018, 08:39:45 AM »
Steve Hutchesson posted some ASM code that can replace the PB JOIN$() function for certain special cases and I've incorporated it into my SQLiteningServer build and it seems to be running solidly during my initial testing.  I haven't been able to benchmark the improvement to SQLiteningServer itself, but mcat$() is running roughly 5 times faster than JOIN$() in my tests, so in theory, it should help the SQLiteningServer respond to client requests for data quicker.

I made the following changes:

First, save the following code block as mcat.inc:
Code: [Select]
' mcat$() developed by Steve Hutchesson:
'   Alternative to JOIN$.   (Dec. 29, 2017)
'   https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-inline-assembler/768261-alternative-to-join/page2

' ¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

 FUNCTION mcat$(BYVAL icnt AS DWORD,varr() AS STRING)
  ' ----------------------------------------------------
  ' icnt is the number of strings to append to a buffer
  ' varr() is the string array that contains the strings
  ' varr() must be dimensioned with a lower bound of zero
  ' ----------------------------------------------------
    #REGISTER NONE

    LOCAL cloc AS DWORD                 ' current location pointer
    LOCAL lcnt AS DWORD                 ' loop counter
    LOCAL totl AS DWORD                 ' buffer size variable
    LOCAL pstr AS DWORD                 ' string pointer for append loop
    LOCAL pbuf AS DWORD                 ' buffer pointer
    LOCAL sbuff AS STRING

  ' -------------------------
  ' calculate the buffer size
  ' -------------------------
    ! mov lcnt, 0                       ' set the loop counter to zero
    ! xor esi, esi                      ' zero esi
    ! mov edi, icnt                     ' load icnt into register to reduce instruction count

  lbl0:
    pstr = LEN(varr(lcnt))
    ! add esi, eax                      ' add each string length to esi
    ! add lcnt, 1                       ' increment the loop counter
    ! cmp lcnt, edi                     ' test lcnt against icnt
    ! jb  lbl0                          ' jump back if below

    ! mov totl, esi                     ' store esi (combined string lengths) in totl
  ' -------------------------

  ' -----------------------------------------
  ' sequentially append string data to buffer
  ' -----------------------------------------
    sbuff = SPACE$( totl)                ' allocate string space
    pbuf = STRPTR( sbuff)                ' get its address
    ! mov cloc, 0                       ' zero the location counter
    ! mov lcnt, 0                       ' set the loop counter to zero

  lbl1:
    pstr = STRPTR(varr(lcnt))           ' string address of each array member

    ! mov edx, cloc                     ' pass arguments in registers
    ! mov ecx, pstr
    ! mov eax, pbuf
    ! call fcappend                     ' append each string
    ! mov cloc, eax                     ' update cloc

    ! add lcnt, 1                       ' increment the loop counter
    ! cmp lcnt, edi                     ' test lcnt against icnt
    ! jb  lbl1                          ' jump back if below
  ' -----------------------------------------

    FUNCTION = sbuff                    ' return the combined string

 END FUNCTION

' ¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

 FASTPROC fcappend

  ' --------------------------------------------------------------
  ' FASTCALL emulation
  ' pass 3 arguments in registers to reduce call overhead

  ' eax = pstring     the main buffer to append extra data to.
  ' ecx = buffer      the byte data to append to the main buffer
  ' edx = location    current location pointer
  ' --------------------------------------------------------------

    PREFIX "!"

    push ebx
    push esi
    push edi

    mov ebx, ecx                        ' load the string address
    sub ebx, 4                          ' sub 4 to get stored string length
    mov ebx, [ebx]                      ' write it back to ebx

    mov esi, eax                        ' pstring
    mov ecx, ecx                        ' buffer
    add esi, edx                        ' location
    mov edi, edx                        ' update return value variable
    or eax, -1                          ' set eax to -1
    add ebx, 1                          ' correct ebx for following loop

  ' -----------
  ' unroll by 2
  ' -----------
  lbl0:
    add eax, 1
    movzx edx, BYTE PTR [ecx+eax]
    mov [esi+eax], dl
    sub ebx, 1
    jz lbl1                             ' exit on written terminator

    add eax, 1
    movzx edx, BYTE PTR [ecx+eax]
    mov [esi+eax], dl
    sub ebx, 1
    jnz lbl0                            ' exit on written terminator

  lbl1:
    add eax, edi                        ' location

    pop edi
    pop esi
    pop ebx

    ret

    END PREFIX

 END FASTPROC

' ¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

Second, in SQLiteningServer.BAS, after the last #RESOURCE statement, add:
Code: [Select]
#INCLUDE "mcat.inc"

In ConnectionMain(), I made the following changes:

Find
Code: [Select]
REDIM lsaDataIn(0 TO 4) AS STRINGReplace with
Code: [Select]
REDIM lsaDataIn( 4) AS STRING   'More efficient DIM
Find
Code: [Select]
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn(0 TO llB + 5)Replace with
Code: [Select]
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn( llB + 5)  'More efficient DIM
Find
Code: [Select]
lsDataIn = JOIN$(lsaDataIn(), "")Replace with
Code: [Select]
lsDataIn = mcat$( llB + 1, lsaDataIn())

Last, in BuildRowDataChunk(), I made the following changes:

Find
Code: [Select]
DIM lsaColumns(0 TO llColCount - 1) AS STRINGReplace with
Code: [Select]
DIM lsaColumns( llColCount - 1) AS STRING  'More efficient DIM
Find
Code: [Select]
lsRow = JOIN$(lsaColumns(), "")Replace with
Code: [Select]
lsRow = mcat$( llColCount, lsaColumns())
Find
Code: [Select]
DIM lsaRows( 1 TO 8000) AS STRINGReplace with
Code: [Select]
DIM lsaRows( 8000) AS STRING  'Zero lower bound; efficient DIM
Find
Code: [Select]
INCR llRowIX
IF llRowIX > UBOUND(lsaRows) THEN REDIM PRESERVE lsaRows(1 TO llRowIX + 2000)
lsaRows(llRowIX) = MKL$(llRowLength) & lsRow
Replace with
Code: [Select]
IF llRowIX = UBOUND(lsaRows) THEN REDIM PRESERVE lsaRows( llRowIX + 2000)  'Zero lower bound; efficient DIM
lsaRows(llRowIX) = MKL$(llRowLength) & lsRow
INCR llRowIX

Find
Code: [Select]
wsRowDataChunk = MKL$(llChunkSize) & JOIN$(lsaRows(), "")Replace with
Code: [Select]
wsRowDataChunk = MKL$(llChunkSize) & mcat$( llRowIX, lsaRows())

Bern Ertl

  • Master Geek
  • ****
  • Posts: 470
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Efficiency improvements for SQLiteningServer
« Reply #1 on: January 11, 2018, 08:54:37 AM »
Similar improvement for SQLiteningClient.BAS

In DoRequest(), I made the following changes:

Find
Code: [Select]
DIM lsaDataIn(0 TO 4) AS STRINGReplace with
Code: [Select]
DIM lsaDataIn( 4) AS STRING   'More efficient DIM
Find
Code: [Select]
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn(0 TO llB + 5)Replace with
Code: [Select]
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn( llB + 5)  'More efficient DIM
Find
Code: [Select]
lsDataIn = JOIN$(lsaDataIn(), "")Replace with
Code: [Select]
lsDataIn = mcat$( llB + 1, lsaDataIn())

cj

  • Master Geek
  • ****
  • Posts: 618
    • View Profile
Re: Efficiency improvements for SQLiteningServer
« Reply #2 on: January 11, 2018, 12:50:52 PM »
I will incorporate your tests and get back.
I'd be curious what results you get.
This was run on a 2600K processor

Only did a few tests with this and the results were about the same with or without the JOIN
so a better test would have to be done.  Running the server will be hard to test since most
time will be based on the transfer and being sure virus checkers, etc aren't slowing it down.

JOIN$ is sure fast so I'm wondering if 5 times faster is going to be a valuable improvement?


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN ()  AS LONG   'jointest.bas
 ? JoinTest("select * from parts")  '351,000,000 to 364,000,000 tix
END FUNCTION

FUNCTION JoinTest(sql AS STRING) AS STRING
 LOCAL q AS QUAD
 LOCAL s AS STRING
 slOpen "sample.db3"
 REDIM sArray(0) AS STRING
 TIX q
 slselary sql,sArray(),"Q9"
 s = JOIN$(sArray(),$CR)
 TIX END q
 FUNCTION = USING$("TIX #,",q)
END FUNCTION



Thanks Bern!   I didn't get to testing it.   1/18/2018

« Last Edit: January 18, 2018, 10:47:01 AM by cj »

Bern Ertl

  • Master Geek
  • ****
  • Posts: 470
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Efficiency improvements for SQLiteningServer
« Reply #3 on: January 11, 2018, 02:25:35 PM »
The biggest optimization gain should be in the BuildRowDataChunk() function, which is joining/concatenating the data inside a loop, so I think you will likely only notice a difference if you are retrieving a lot of data at a time (ie. lots of string/binary data in one row/statement). 

Bern Ertl

  • Master Geek
  • ****
  • Posts: 470
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Efficiency improvements for SQLiteningServer
« Reply #4 on: January 18, 2018, 10:41:49 AM »
After some further testing, it seems like the mcat$ function only out performs JOIN$ in certain cases.  I tested with some real world data in a larger data set and JOIN$ actually performed twice as fast as mcat$.  I think I'm going to stick with JOIN$ until mcat$ or another alternative is proven more efficient for all cases.

FYI, the details:  https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-inline-assembler/768261-alternative-to-join?p=768782#post768782

cj

  • Master Geek
  • ****
  • Posts: 618
    • View Profile
Re: Efficiency improvements for SQLiteningServer - Thread Pool
« Reply #5 on: January 19, 2018, 08:10:40 PM »
> Efficiency improvement for SQLitening Server
I am studying thread pools.