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)
Similar improvement for SQLiteningClient.BAS
In DoRequest(), I made the following changes:
DIM lsaDataIn(0 TO 4) AS STRINGReplace with
DIM lsaDataIn( 4) AS STRING 'More efficient DIM
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn(0 TO llB + 5)Replace with
IF llB > UBOUND(lsaDataIn) THEN REDIM PRESERVE lsaDataIn( llB + 5) 'More efficient DIM
lsDataIn = JOIN$(lsaDataIn(), "")Replace with
lsDataIn = mcat$( llB + 1, lsaDataIn())
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?
FUNCTION PBMAIN () AS LONG 'jointest.bas
? JoinTest("select * from parts") '351,000,000 to 364,000,000 tix
FUNCTION JoinTest(sql AS STRING) AS STRING
LOCAL q AS QUAD
LOCAL s AS STRING
REDIM sArray(0) AS STRING
s = JOIN$(sArray(),$CR)
TIX END q
FUNCTION = USING$("TIX #,",q)
Thanks Bern! I didn't get to testing it. 1/18/2018
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).
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
> Efficiency improvement for SQLitening Server
I am studying thread pools.