"Rick Ratchford" <r...@amazingaccuracy.com>
schrieb im Newsbeitrag news:c9ce387e92004e7b9c16ddaa2bd36...@dolphin...
> So modifying TmpTable, which will still be needed for
> other procedures, is not preferred. It would be great if
> a recordset could be derived from it instead that contains
> the DIRECTION results. Once the procedure exits, the
> recordset would just go away.
>
> My original plan was to create the recordset from TmpTable,
> with the added DIRECTION column.
>
> Ex: 0 as Direction FROM TmpTable
>
> Then, either loop through the recordset doing the comparisons
> and filling in DIRECTION, ...
That "original plan" is the fastest way, you can achieve
your desired result.
So I'm basically with Simons suggestion - don't "overuse" the
SQL-engine - just find a good mix between "pure SQL" and
additional (explicit) Rs-iterations.
The returned recordsets of my wrapper are writable - and
as long as you don't perform an Rs.UpdateBatch on them,
these Fieldchanges never reach the underlying table(s), the
Recordset was originally derived from - and the changes on
such an Rs are "forgotten" as soon as that Recordset goes
out of scope (terminates).
But as you already stated in your reply to Simon - SQL-queries,
working against your InMemory-DB-Tables are definitely
useful for many (most) of the tasks in your rewrite/update-
process whilst dealing with your stock-data ... but *this* kind
of query (dealing with previous, or following records) is
relative costly, since solutions usually depend on appropriate
Sub-Selects.
You don't need that much VB-Code, to perform the same
task much faster ("much faster" of course relative - the
recordsize in your tables is currently in a range, where
even the Sub-Select-based approach will perform "fast
enough" IMO - but you should be aware, that such queries
are more expensive than explicit Rs-loops - and if you
can avoid them with some lines of "non-SQL-code", you
should do so - your current "direction-field" calculation
presumably being one of the simpler requirements in that
category - but if these kind of queries need to calculate
e.g. sliding-averages over larger "window-sizes", you (or
"the user") will probably already "feel" the speed-differences
compared with dedicated looping.
'here a simple function, which delivers an appropriately filled Rs...
Function GetRsWithDirection(Cnn as cConnection) as cRecordset
Dim Rs as cRecordset, SQL as String
Dim LastTax As Double, CurTax As Double
SQL = "Select *, 0 As Direction From TmpTable Order By ID"
Set Rs = Cnn.OpenRecordset(SQL)
LastTax = Rs!Tax
Rs.MoveNext 'leave the first Record (Direction remains at 0)
Do Until Rs.EOF
CurTax = Rs!Tax
Rs!Direction = IIF(CurTax > LastTax, 1, -1)
LastTax = CurTax
Rs.MoveNext
Loop
Set GetRsWithDirection = Rs 'return the Rs-Result
End Function
If you compare the code-volume you'd have "to type"
with that of a similar routine that does it with a larger SQL-
string, you will not find all that much of a difference:
e.g. "borrowed" from Wes' reply (and not yet wrapped in a
Rs-delivering function):
select *,
(select
case when b.tax < MarketTable .tax
then "Up"
when b.tax>=MarketTable .tax
then "Down"
else null
end
from MarketTable b
where b.rowid=MarketTable.rowid-1) from MarketTable
And as said, the explicit loop, changing some Rs-Values
temporarily, should perform much faster, since esp. the line:
LastTax = CurTax 'copying only the content of a Double-Var
is barely measurable, compared with the time for the
needed SubSelect in a "plain SQL"-solution based on:
"where b.rowid=MarketTable.rowid-1"
So, yeah - "horses for courses" (at least in these special
cases, where you have to deal with previous or following
record-values)... ;-)
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
