#>"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.
Maybe I misunderstood, but the impression I got was that I should solve this
problem using my VB language rather than dealing with the DB. As you already
noted, my "original plan" was to just create a recordset of my table with
the additional column, then 'loop' through it and fill it in programmically.
I was hoping that all this could have been done internally and the resultant
recordset returned already having the new column filled out. Why not, if it
can be done without adding overhead?
#>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).
Yes. This is exactly what I rely on. My application has several procedures
that makes use of a particular recordset, so I made it GLOBAL and is not
destroyed. Other procedures may need a different set of data from the main
table, and these will create recordsets that are then destroyed when going
out of scope. An no, my application has no need for Rs.UpdateBatch because
the main table should never be modified in any way. It simply holds the
price data for one market at a time.
#>
#>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.
Is it any slower than having to loop through the recordset, comparing one
record with another and then directly writing back into the recordset the
result? As you noted, perhaps not in my case since the amount of data is not
that extensive to begin with.
When I read Simon's reply, I did not get the sense that he was suggesting I
do a Rs-Loop. It appeared to me, and I could be mistaken of course, that he
was referring to pure programming in by language (VB).
So if the advice is not to abandon the SQL/SQLite approach, but to stick to
simple SQL and use VB code to loop through the recordset result, that works
just fine. I prefer referencing recordsets over arrays when there isn't a
speed cost.
#>'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
Oh, I agree that there is not much difference in the two code sets above.
And I am certainly much more comfortable with the VB style since I
understand it and am still having trouble getting a grip on SQL structure.
As already mentioned, it as my 'original plan', and the 'how-to' as well, to
create a basic recordset and then loop through it. I was trying to find out
if it was the BEST WAY, or if there was a way to do it all via SQL. It was
an academic question, as I wanted to understand the power and limitations
involved.
I will admit, however, that my VB code to do this was going to be looping
through the recordset by way of Rs.ValueMatrix() rather than using
Rs.MoveNext (cursor moving). The .ValueMatrix indexes I would have garnered
using .IndexInFieldList.
#>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
#>
Okay. The final verdict is to go ahead with the original plan and create my
recordset (which, by the way, is not all the data but only the complete
years as mentioned in previous messages).
Then using that recordset, I will loop through it and perform the comparison
and recordset update (without the UpdateBatch, I know). :)
I think this would be much more preferrable than transferring the data into
arrays and then manipulating them.
Thanks a bunch!
Rick
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
