• Welcome, Guest. Please login.
 
October 21, 2019, 02:36:52 am

News:

Welcome to the SQLitening support forums!


Supercharge creating recordsets with StringBuilder

Started by cj, May 07, 2015, 04:25:15 pm

Previous topic - Next topic

cj

PowerBasic's StringBuilder creates large strings MANY times faster than using concatenation.
When creating recordsets you may find the concatenation takes MUCH longer than the SQL.
This will dramatically increase performance returning recordsets.

On my test system returning the 10,000 records in the parts table in sample.db3 takes a few seconds.
With string builder they are returned in well under a second.

To use stringbuilder is very easy:

LOCAL sb AS ISTRINGBUILDERA
sb = CLASS "StringBuilderA"

sb.add "line one"
sb.add $CRLF
sb.add "line two"
sb.add $CRLF
s$ = sb.string

Quit using these very slow methods creating record sets.
s$ = s$ + "something"
s$ = s$ & "something"
s$+= "something"


DECLARE FUNCTION ShellExecute LIB "SHELL32.DLL" ALIAS "ShellExecuteA" _
        (BYVAL hwnd AS DWORD, lpOperation AS ASCIIZ, lpFile AS ASCIIZ,_
         lpParameters AS ASCIIZ, lpDirectory AS ASCIIZ, BYVAL nShowCmd AS LONG) AS DWORD
#INCLUDE "sqlitening.inc"                'all sqlitening routines
FUNCTION PBMAIN () AS LONG               'start of PowerBASIC program
  SlowOldWay                            '
  FastNewWay                             'Use string builder
END FUNCTION
'
SUB SlowOldWay
  sOutputFile$ = "temp.txt"              'will shell to this file
  slopen "sample.db3","C"                'open a database or create
  slsel  "select rowid,* from parts"     'execute sql statement
  DO WHILE slGetRow                      'fill recordset
    sLine$ = ""                          'init row data
    FOR ColumnNumber& = 1 TO slGetColumnCount 'build row
      sLine$ = sLine$ + slf(ColumnNumber&) + "," 'add column
    NEXT                                 'loop
    sLine$=LEFT$(sLine$,LEN(sLine$)-1)   'get rid of last comma
    sRecordSet$=sRecordSet$+sLine$+$CRLF 'add to recordset with $CRLF
  LOOP
  hFile& = FREEFILE                      'get a file handle
  OPEN sOutputFile$ FOR OUTPUT AS #hFile&'open file
  PRINT #hFile&, sRecordSet$             'write recordset to file
  CLOSE #hFile&                          'close file
  ShellExecute BYVAL 0, "OPEN", sOutputFile$+ $NUL, BYVAL 0, $NUL, BYVAL 1
END SUB
'
SUB FastNewWay
  ? "Begin StringBuilder Way"
  LOCAL sb AS ISTRINGBUILDERA
  sb = CLASS "StringBuilderA"
  sOutputFile$ = "temp.txt"              'will shell to this file
  slopen "sample.db3","C"                'open a database or create
  slsel  "select rowid, * from parts"     'execute sql statement
  DO WHILE slGetRow                      'fill recordset
    FOR ColumnNumber& = 1 TO slGetColumnCount 'build row
      sb.add slf(ColumnNumber&)
      sb.add ","
    NEXT                                 'loop
    sb.delete(sb.len,1)
    sb.add $CRLF
  LOOP
  RecordSet$ = sb.string
  BEEP 'signal done and write to disk
  hFile& = FREEFILE                      'get a file handle
  OPEN sOutputFile$ FOR OUTPUT AS #hFile&'open file
  PRINT #hFile&, RecordSet$              'write recordset to file
  CLOSE #hFile&                          'close file
  ShellExecute BYVAL 0, "OPEN", sOutputFile$+ $NUL, BYVAL 0, $NUL, BYVAL 1
END SUB

Bern Ertl

Have you tried benchmarking the StringBuilder method against using a string array and JOIN$() ?  I'm processing recordsets by record (ie. building a $TAB delimited string per record, not one giant string for the whole recordset) and it seems to be working reasonably fast.

sColData(  1)  = slf( 1, lSetNum)
sColData( 2) = FormattedDataFromRawData( slf( 2,lSetNum))
...
sRecord = JOIN$( sColData(), $TAB)

cj

I compared to string concatenation and the results are night and day.
Concatenation gets worse as the string builds.

Creating an array is also much faster than concatenation.
To test using stringbuilder vs slSelArray with JOIN$ will take some more thought.

Here is a test creating an identical recordset and stringbuilder easily wins.

It must be taken into account that slSelAry has a great advantage giving you an array
to work with.  This enables working with the recordset after it is created to do things
like first page, last page, previous page, next page.

#DIM ALL
#INCLUDE "sqlitening.inc"   'comparespeed.bas
#INCLUDE "win32api.inc"
GLOBAL gSBStartTick,gSBEndTick               AS DWORD
GLOBAL gArrayStartTick,gArrayEndTick         AS DWORD
GLOBAL gConcatenateStartTick,gConcatenateEndTick AS DWORD
'
FUNCTION PBMAIN () AS LONG
  LOCAL sResult AS STRING
  ArrayMethod
  StringBuilderMethod
  ConcatenationMethod
  sResult = USING$("StringBuilder ticks #",gSBEndTick-gSBStartTick)       + $CR +_
    USING$("ArrayMethod   ticks #",gArrayEndTick-gArrayStartTick) + $CR +_
    USING$("Concatenate   ticks #",gConcatenateEndTick-gConcatenateStartTick) + $CR + $CR
  'do in another order to take into account cache
  ConcatenationMethod
  ArrayMethod
  StringBuilderMethod
  sResult+= USING$("StringBuilder ticks #",gSBEndTick-gSBStartTick)       + $CR +_
    USING$("ArrayMethod   ticks #",gArrayEndTick-gArrayStartTick) + $CR +_
    USING$("Concatenate   ticks #",gConcatenateEndTick-gConcatenateStartTick)
  ? sResult
END FUNCTION
'
SUB StringBuilderMethod
  gSBStartTick = GetTickCount
  LOCAL sb AS ISTRINGBUILDERA
  LOCAL sOutputFile,RecordSet AS STRING
  LOCAL ColumnNumber,hFile AS LONG
  sb = CLASS "StringBuilderA"
  sOutputFile$ = "temp.txt"              'will shell to this file
  slopen "sample.db3","C"                'open a database or create
  slsel  "select rowid, * from parts"     'execute sql statement
  DO WHILE slGetRow                      'fill recordset
    FOR ColumnNumber& = 1 TO slGetColumnCount 'build row
      sb.add slf(ColumnNumber&)
      sb.add ","
    NEXT                                 'loop
    sb.delete(sb.len,1)
    sb.add $CRLF
  LOOP
  RecordSet$ = sb.string
  gSBEndTick = GetTickCount
  BEEP 'signal done and write to disk
  hFile& = FREEFILE                      'get a file handle
  OPEN sOutputFile$ FOR OUTPUT AS #hFile&'open file
  PRINT #hFile&, RecordSet$              'write recordset to file
  CLOSE #hFile&                          'close file
  ShellExecute BYVAL 0, "OPEN", sOutputFile$+ $NUL, BYVAL 0, $NUL, BYVAL 1
END SUB
'
SUB ArrayMethod
  gArrayStartTick = GetTickCount
  LOCAL hFile AS LONG
  LOCAL sOutputFile AS STRING
  LOCAL RecordSet() AS STRING

  sOutputFile$ = "temp.txt"              'will shell to this file
  slopen "sample.db3","C"                'open a database or create
  slselAry  "select rowid, * from parts",RecordSet(),"Q44c"
  gArrayEndTick = GetTickCount
  BEEP 'signal done and write to disk
  hFile& = FREEFILE                      'get a file handle
  OPEN sOutputFile$ FOR OUTPUT AS #hFile&'open file
  PRINT #hFile&, RecordSet()             'write recordset to file
  CLOSE #hFile&                          'close file
  ShellExecute BYVAL 0, "OPEN", sOutputFile$+ $NUL, BYVAL 0, $NUL, BYVAL 1
END SUB

SUB ConcatenationMethod
  gConcatenateStartTick = GetTickCount
  LOCAL ColumnNumber,hFile AS LONG
  LOCAL sOutputFile,sLine,sRecordSet AS STRING
  sOutputFile$ = "temp.txt"              'will shell to this file
  slopen "sample.db3","C"                'open a database or create
  slsel  "select rowid,* from parts"     'execute sql statement
  DO WHILE slGetRow                      'fill recordset
    sLine$ = ""                          'init row data
    FOR ColumnNumber& = 1 TO slGetColumnCount 'build row
      sLine$ = sLine$ + slf(ColumnNumber&) + "," 'add column
    NEXT                                 'loop
    sLine$=LEFT$(sLine$,LEN(sLine$)-1)   'get rid of last comma
    sRecordSet$=sRecordSet$+sLine$+$CRLF 'add to recordset with $CRLF
  LOOP
  gConcatenateEndTick = GetTickCount
  hFile& = FREEFILE                      'get a file handle
  OPEN sOutputFile$ FOR OUTPUT AS #hFile&'open file
  PRINT #hFile&, sRecordSet$             'write recordset to file
  CLOSE #hFile&                          'close file
  ShellExecute BYVAL 0, "OPEN", sOutputFile$+ $NUL, BYVAL 0, $NUL, BYVAL 1
END SUB
                                                                             

cj

May 09, 2015, 06:42:27 pm #3 Last Edit: May 09, 2015, 07:40:23 pm by cj
Quote
Have you tried benchmarking the StringBuilder method against using a string array and JOIN$() ?  I'm processing recordsets by record (ie. building a $TAB delimited string per record, not one giant string for the whole recordset) and it seems to be working reasonably fast.

sColData(  1)  = slf( 1, lSetNum)
sColData( 2) = FormattedDataFromRawData( slf( 2,lSetNum))
...
sRecord = JOIN$( sColData(), $TAB)


Bern,
Your method is just as fast as stringbuilder from my tests.
This demonstrates using JOIN$ also creates an array of all records which is extremely useful.

4 different methods are demonstrated here with my results at the bottom:

#DIM ALL
#INCLUDE "sqlitening.inc"   'comparespeed.bas
#INCLUDE "win32api.inc"
GLOBAL gSBStartTick,gSBEndTick               AS DWORD
GLOBAL gArrayStartTick,gArrayEndTick         AS DWORD
GLOBAL gConcatStartTick,gConcatEndTick AS DWORD
GLOBAL gJoinStartTick,gJoinEndTick AS DWORD
$Delimiter = "|" 'CHR$(160) 'chr$(160)
$OutputFile = "temp.txt"
$SQL = "select * from parts"
%ShowRecordset=0 'show recordset
'
FUNCTION PBMAIN () AS LONG
  LOCAL sResult AS STRING
  LOCAL NumberOfTestLoops AS LONG
  slopen "sample.db3","C"
FOR NumberOfTestLoops = 1 TO 1
  JoinMethod    : sResult+= USING$("Join method   ticks #",gJoinEndTick-gJoinStartTick)    + $CR
  SBMethod      : sResult+= USING$("StringBuilder ticks #",gSBEndTick-gSBStartTick)                + $CR
  slSelAryMethod: sResult+= USING$("slSelAry      ticks #",gArrayEndTick-gArrayStartTick) + $CR
  ConcatMethod: sResult+= USING$("Concatenation   ticks #",gConcatEndTick-gConcatStartTick)    + $CR
NEXT
  ? sResult,%MB_SYSTEMMODAL,EXE.NAME$
END FUNCTION
'
SUB JoinMethod
  gJoinStartTick = GetTickCount
  LOCAL ColumnNumber,Columns,rows AS LONG
  slsel  $SQL
  Columns  = slGetColumnCount
  IF Columns > 0 THEN
    DIM sRecordSet(1 TO 20000) AS STRING
    DIM sColData(1 TO Columns) AS STRING
    DO WHILE slGetRow
      INCR rows
      IF Rows MOD 20000 = 0 THEN 'increase array size every MOD x rows
        REDIM PRESERVE sRecordSet(1 TO UBOUND(sRecordSet) + 20000)
      END IF
      FOR ColumnNumber& = 1 TO Columns
        sColData(ColumnNumber) = slf(ColumnNumber&)
      NEXT
      sRecordSet(rows) = JOIN$(sColData(),$Delimiter)
    LOOP
    REDIM PRESERVE sRecordSet(1 TO Rows) 'sRecordSet(actual rows)
    gJoinEndTick = GetTickCount
    SaveArrayAndDisplay sRecordSet()
  ELSE
    gJoinStartTick = 0
  END IF
END SUB
'
SUB SBMethod
  gSBStartTick = GetTickCount
  LOCAL sb AS ISTRINGBUILDERA
  LOCAL sRecordSet AS STRING
  LOCAL ColumnNumber,hFile AS LONG
  sb = CLASS "StringBuilderA"
  slsel  $SQL
  DO WHILE slGetRow
    FOR ColumnNumber& = 1 TO slGetColumnCount
      sb.add slf(ColumnNumber&)
      sb.add $Delimiter
    NEXT
    sb.delete(sb.len,1)
    sb.add $CRLF
  LOOP
  sRecordSet = sb.string
  gSBEndTick = GetTickCount
  SaveStringAndDisplay sRecordSet
END SUB
'
SUB slSelAryMethod
  gArrayStartTick = GetTickCount
  LOCAL sRecordSet() AS STRING
  slselAry  $SQL,sRecordSet(),"Q" + STR$(ASC($Delimiter)) + "c"
  gArrayEndTick = GetTickCount
  SaveArrayAndDisplay sRecordSet()
END SUB
'
SUB ConcatMethod
  gConcatStartTick = GetTickCount
  LOCAL ColumnNumber AS LONG
  LOCAL sLine,sRecordSet AS STRING
  slsel  $SQL
  DO WHILE slGetRow                      'fill recordset
    sLine$ = ""                          'init row data
    FOR ColumnNumber = 1 TO slGetColumnCount 'build row
      sLine = sLine + slf(ColumnNumber) + $Delimiter 'add column
    NEXT                                 'loop
    sLine$=LEFT$(sLine,LEN(sLine)-1)   'get rid of last delimiter
    sRecordSet=sRecordSet+sLine+$CRLF 'add line to recordset with $CRLF
  LOOP
  gConcatEndTick = GetTickCount
  SaveStringAndDisplay sRecordSet
END SUB
'
SUB SaveStringAndDisplay(sRecordset AS STRING)
  IF %ShowRecordset THEN
    LOCAL hFile AS LONG
    hFile = FREEFILE                      'get a file handle
    OPEN $OutputFile FOR OUTPUT AS #hFile&'open file
    PRINT #hFile&, sRecordSet             'write recordset to file
    CLOSE #hFile&                          'close file
    ShellExecute BYVAL 0, "OPEN", $OutputFile+ $NUL, BYVAL 0, $NUL, BYVAL 1
  END IF
END SUB
'
SUB SaveArrayAndDisplay(sRecordset() AS STRING)
  IF %ShowRecordset THEN
    LOCAL hFile AS LONG
    hFile = FREEFILE                      'get a file handle
    OPEN $OutputFile FOR OUTPUT AS #hFile&'open file
    PRINT #hFile&, sRecordSet()           'write recordset to file
    CLOSE #hFile&                         'close file
    ShellExecute BYVAL 0, "OPEN", $OutputFile+ $NUL, BYVAL 0, $NUL, BYVAL 1
  END IF
END SUB

Bern Ertl


cj

I should thank you, Bern!
I did not think filling an array of strings avoided concatenation.
I am also thinking about switching to 2-dimensional arrays to get data by sArray(col,row).
The formatting of the columns can be done while filling the array or while displaying them.