SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Bern Ertl on January 11, 2018, 10:09:45 AM

Title: Efficiency improvements for SQLiteningServer
Post by: Bern Ertl on January 11, 2018, 10:09: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]' 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

'
Title: Re: Efficiency improvements for SQLiteningServer
Post by: Bern Ertl on January 11, 2018, 10:24:37 AM
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())
Title: Re: Efficiency improvements for SQLiteningServer
Post by: cj on January 11, 2018, 02:20: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

Title: Re: Efficiency improvements for SQLiteningServer
Post by: Bern Ertl on January 11, 2018, 03:55: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). 
Title: Re: Efficiency improvements for SQLiteningServer
Post by: Bern Ertl on January 18, 2018, 12:11:49 PM
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
Title: Re: Efficiency improvements for SQLiteningServer - Thread Pool
Post by: cj on January 19, 2018, 09:40:40 PM
> Efficiency improvement for SQLitening Server
I am studying thread pools.