• Welcome, Guest. Please login.
 
October 25, 2021, 09:41:41 PM

News:

Welcome to the SQLitening support forums!


StringBuilder object vs Freds joins and binds

Started by cj, December 27, 2012, 06:44:06 AM

Previous topic - Next topic

cj

December 27, 2012, 06:44:06 AM Last Edit: December 27, 2012, 10:26:43 AM by cj
StringBuilder object much faster than concatenating strings.
Using the ANSI version in this example.

Fred's joins and binds are much faster than thisl
If anyone can show me how to use Freds' method to JOIN Bind instead of this it would be appreciated

Adding 200,000 items and displaying it in a textbox takes 8 seconds with fast routine and 88 seconds with the slow.


#COMPILE EXE  "\sql\bin\ShortTest"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
%MaxItem = 50000

FUNCTION PBMAIN () AS LONG
  Fast
  Slow
END FUNCTION

FUNCTION Fast AS LONG
  LOCAL x,columns AS LONG
  LOCAL delimiter, s AS STRING
  LOCAL StartTime AS STRING
  LOCAL sb AS ISTRINGBUILDERA
  StartTime = TIME$

  sb = CLASS "StringBuilderA"
  Delimiter = "        "

  slOpen "test.db3","C"
  sb.add "Begin;"
  sb.add "Drop Table IF Exists Table1;"
  sb.add "Create Table If Not Exists Table1(Field1 INTEGER PRIMARY KEY);"
  FOR x = 1 TO %MaxItem
    sb.add "Insert into Table1 values(null);"
  NEXT
  sb.add "End"
  slExe(sb.string)
  sb.clear
  sb.add "SELECT count(*) FROM Table1"
  slSel(sb.string)
  columns = slGetColumnCount
  IF columns = 0 THEN EXIT FUNCTION
  sb.clear
  DO WHILE slGetRow
    FOR x = 1 TO Columns
      sb.add slf(x) + Delimiter
    NEXT
    sb.Add $CRLF
  LOOP
  ? sb.string + $CR + StartTime + $CR + TIME$
END FUNCTION

FUNCTION Slow AS LONG
  LOCAL x,columns AS LONG
  LOCAL delimiter, s, sAll AS STRING
  LOCAL StartTime AS STRING
  StartTime = TIME$
  Delimiter = "        "

  slOpen "test.db3","C"
  s = "Begin;"
  s = s + "Drop Table IF Exists Table1;"
  s = s + "Create Table If Not Exists Table1(Field1 INTEGER PRIMARY KEY);"
  FOR x = 1 TO %MaxItem
    s = s + "Insert into Table1 values(null);"
  NEXT
  s = s + "End"
  slExe(s)

  s = "SELECT count(*) FROM Table1"
  slSel(s)
  columns = slGetColumnCount
  IF columns = 0 THEN EXIT FUNCTION

  DO WHILE slGetRow
    s = ""
    FOR x = 1 TO Columns
      s = s + slf(x) + Delimiter
    NEXT
    sAll = sAll + s + $CRLF
  LOOP
  ? sAll + $CR + StartTime + $CR + TIME$
END FUNCTION

cj


#COMPILE EXE  "\sql\bin\ShortTest2"
%MaxItem  = 50000
FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG, s AS STRING, sb AS ISTRINGBUILDERA, FastQuad,SlowQuad AS QUAD
  TIX FastQuad
  sb = CLASS "StringBuilderA"
  sb.add "Begin;"
  sb.add "Drop Table IF Exists Table1;"
  sb.add "Create Table If Not Exists Table1(Field1 INTEGER PRIMARY KEY);"
  FOR x = 1 TO %MaxItem
    sb.add "Insert into Table1 values(null);"
  NEXT
  sb.add "End"
  TIX END FastQuad
  BEEP              'signal done with StringBuilder
  sb.clear          'clear buffer, optional
  '-----------------------------------------------------------------------
  TIX SlowQuad  'Now compare concatenation
  s = "Begin;"
  s = s + "Drop Table IF Exists Table1;"
  s = s + "Create Table If Not Exists Table1(Field1 INTEGER PRIMARY KEY);"
  FOR x = 1 TO %MaxItem
    s = s + "Insert into Table1 values(null);"
  NEXT
  s = s + "End"
  TIX END SlowQuad
  BEEP
  ? "StringBuilder is " + FORMAT$(SlowQuad\FastQuad,"#,") + " times faster!",,"StringBuilder Object Test"
END FUNCTION

Bern Ertl

I have not used the StringBuilder object before, but PowerBASIC has a BUILD$ function that works well.

When you use <dynamic> STRING variables, there is a lot of memory initialization and data copying happening every time you use:s = s + "stuff".  If you know the maximum string limit and have the memory available, using an ASCIIZ string allows for better efficiency because the memory is not re-allocated and data is not copied around every time you append to the string.

cj

Quote
The BUILD$() function is most valuable when you are concatenating numerous strings all at the same time.  However, when you must add many string sections, in many separate operations, the StringBuilder object is much faster, and a more appropriate choice.


Fred Meier

QuoteFred's joins and binds are much faster than thisl
If anyone can show me how to use Freds' method to JOIN Bind instead of this it would be appreciated
The below code is an example of concatenating strings by adding eact string to an array and then using the Join$ command to build the final string.
  Local x as Long
  Local s as String
  Dim lsaA(1000) as String
  for x = 0 TO 1000
     lsaA(x) = "This is a string # " & format$(x)
  NEXT
  s = join$(lsaA(), "")

This was the best(fastest) way to concatenate large string before StringBuilder was available.
StringBuilder seems to be just as fast but don't know which uses the more resources.

Bern Ertl

cj - just curious, but have you tried testing the performance for accessing the string after building it too?  I wonder if the stringbuilder object is actually just managing pointers to each <dynamic> string segment that was added (ie. the string is not actually concatenated into one contiguous memory block).  If this were true, you would see a (slight) performance hit when accessing the string.