Re: dhSQLite IFunction

Come to think of it maybe this codeblock could be done faster as these
actions will always be the same for every row of the ADO recordset:

For c = 0 To LC
Select Case arrDataTypes2(c)
Case 1
Cmd.SetText c + 1, rs(c)
Case 2
Cmd.SetInt32 c + 1, rs(c)
Case 3
Cmd.SetDouble c + 1, rs(c)
End Select
Next c

Not sure what the logic should be, and pity you can't make an array of actions ...

RBS

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:e8XUHnduHHA.3400@xxxxxxxxxxxxxxxxxxxxxxx
Had a look at this now and have settled on the Command-Object.
This is indeed a lot faster than running INSERT INTO directly with a concatenated
value string.
Also had a look at the method with cRecordset, but as this was a bit slower than my
old method and no real benefit in my particular situation I went with method 1.
As I am dealing with only 3 SQLite field types, text, integer and real it was quite simple
to alter my code and it simply came down to this:

Set Cmd = CreateSQLiteCommand("Insert Into " & strTable & _
" Values(" & MakeParameterString(LC + 1) & ")")

On Error Resume Next 'TO COVER NULL VALUES OF rs(c) !!

While Not rs.EOF
For c = 0 To LC
Select Case arrDataTypes2(c)
Case 1
Cmd.SetText c + 1, rs(c)
Case 2
Cmd.SetInt32 c + 1, rs(c)
Case 3
Cmd.SetDouble c + 1, rs(c)
End Select
Next c

Cmd.Execute
lRowCount = lRowCount + 1
rs.MoveNext
Wend

End If 'If bNilFromIB = False

On Error GoTo ERROROUT


This is nice as apart from being faster it also simplifies the code as I don't have to worry about
single quotes etc.

Could I use the Command object in other situations such as for example doing an update
on a SQLite table?


RBS


"Schmidt" <sss@xxxxxxxxx> wrote in message news:%234eZ9bZuHHA.3480@xxxxxxxxxxxxxxxxxxxxxxx

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:%238v7nXYuHHA.736@xxxxxxxxxxxxxxxxxxxxxxx

Had a look at your demo and noticed you use a dhSQLite
recordset when moving data from an ADO recordset to SQLite.

When I move data from an ADO recordset I loop through the
ADO recordset and run direct queries like this:

"INSERT INTO " & strTable & _
" " & strINTO & _
" VALUES (" & strValues & ")"

where strTable is the SQLite table and strValues are the
concatenated values for that row of the ADO recordset.

Could you tell me what the merits are of using the dhSQLite
recordset in this situation?

In short, better Type-Safety/Auto-Typeconversion (e.g.
DateColumns, correct transported Null-Values, Blob-Content,
etc.) whilst achieving a good insert-performance, since the builtin
UpdateBatch-Method uses Command-Objects under the
hood now.

The new Demo contains an Insert-Example using Command-
Objects (50000 Records are inserted in typically only half a
second this way).
Then compare the timing-results of this demo with the insert-
timings, wich you achieve using 50000 explicit Insert-Statements.

And then compare these timings with the Insert-Performance,
wich you can achieve from 50000 AddNews and a final
UpdateBatch.
The pure (final) UpdateBatch-Call, wich writes out all the
previously added and stored field-values at ones, should
perform better than a loop with 50000 explicit Insert-Strings,
due to the underlying Command-Objects.
A "complete UpdateBatch-based Insert-Approach"
(including 50000 AddNews and explicitely setting
the appropriate FieldValues) takes a bit of additional
time, so that in sum (Populating the Rs with new Values +
the final UpdateBatch-Call) it is somewhat slower than
the 50000 explicitely executed Insert-Strings, but you
don't have to fiddle this way with correct types (be it
VB-Dates, or Blob-ByteArrays, VB-Strings wich
contain Commas or other special Chars) - filling it
prior into a Recordset ensures, that all types and all
content is handled properly.

So you have 3 ways of Inserting new values using
dhSQLite:

1. using Command-Objects (explicite Typing that you
can control, best performance)
2. using explicite executed Insert-Statements (ca. 2-4
times slower than Command-Objects, everything needs
to be converted into a appropriate text-representation
by hand, wich is sometimes "unhandy" regarding blobs or
strings wich contain "special chars" )
3. using Recordsets with AddNew and a final UpdateBatch
(this is the "Full-Auto-Mode", where every new value is
appropriately converted regarding the matching field-type-
description - performance not much slower than [2.]

Olaf




.

Relevant Pages

  • Re: dhSQLite IFunction
    ... recordset when moving data from an ADO recordset to SQLite. ... UpdateBatch-Method uses Command-Objects under the ... wich you achieve using 50000 explicit Insert-Statements. ... And then compare these timings with the Insert-Performance, ...
    (microsoft.public.vb.general.discussion)
  • Re: dhSQLite IFunction
    ... recordset when moving data from an ADO recordset to SQLite. ... wich you achieve using 50000 explicit Insert-Statements. ... And then compare these timings with the Insert-Performance, ...
    (microsoft.public.vb.general.discussion)
  • Re: dhSQLite IFunction
    ... I will see if I can go with option 1, Command-Objects ... recordset when moving data from an ADO recordset to SQLite. ... And then compare these timings with the Insert-Performance, ...
    (microsoft.public.vb.general.discussion)
  • Re: Using WSH to populate a pivot cache with an ADO recordset
    ... I've tried this method and it works OK from WSH. ... However, in this case, I still need to be able to populate the pivot cache ... using an ADO recordset, because I've run previous statements on the ADO data ...
    (microsoft.public.excel.programming)
  • Re: Using WSH to populate a pivot cache with an ADO recordset
    ... cache using an ADO recordset, because I've run previous statements on ... the ADO data connection to create temporary tables which are then ... "keepITcool" wrote: ...
    (microsoft.public.excel.programming)