• Welcome, Guest. Please login.
 
May 25, 2019, 01:09:57 am

News:

Welcome to the SQLitening support forums!


Efficiency improvements for SQLiteningServer

Started by Bern Ertl, January 11, 2018, 10:09:45 am

Previous topic - Next topic

Bern Ertl

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]' 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

'

Bern Ertl

Similar improvement for SQLiteningClient.BAS

In DoRequest(), I made the following changes:

FindDIM lsaDataIn(0 TO 4) AS STRINGReplace withDIM lsaDataIn( 4) AS STRING   'More efficient DIM

FindIF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn(0 TO llB + 5)Replace withIF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn( llB + 5)  'More efficient DIM

FindlsDataIn = JOIN$(lsaDataIn(), "")Replace withlsDataIn = mcat$( llB + 1, lsaDataIn())

cj

January 11, 2018, 02:20:52 pm #2 Last Edit: January 18, 2018, 12:17:01 pm by cj
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


Bern Ertl

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

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

> Efficiency improvement for SQLitening Server
I am studying thread pools.