SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

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

Bern Ertl

  • Master Geek
  • ****
  • Posts: 481
  • Excellent
    • View Profile
    • InterPlan Systems
Efficiency improvements for SQLiteningServer
« on: January 11, 2018, 08:39:45 AM »

Bern Ertl

  • Master Geek
  • ****
  • Posts: 481
  • 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: 636
    • 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 »
Logged

Bern Ertl

  • Master Geek
  • ****
  • Posts: 481
  • 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: 481
  • 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: 636
    • 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.