"Rick Ratchford" <r...@amazingaccuracy.com> schrieb im Newsbeitrag news:152a3111f1ab4a9891b9158580cb7...@dolphin...
> 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. I don't understand Simons reply that way (to either use one or the other)... He clearly states: "Keeping the previous value of a field in a variable does not take lots of programming " That means, he suggests basically the same thing, which I tried to cover in my small example-routine (note, that he's mentioning "values of fields"). Do use the DB (retrieving your records, containing your fields) - but the initial query should be a "simpler" one, which does not stress the SQL-engine all that much with special requirements which are difficult to formulate or to perform - just use the programming-language too where it has obvious advantages ... against intermediate Rs-results. > 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. Nothing wrong with that - especially for *these* kind of queries (where you have to "park" some record-values for a later calculation). > 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? Also nothing wrong with that - especially if you ask in that group here, where you never know, what the SQL-experts are able to come up with... :-) > #>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? In that case I'd say yes, definitely - within an Rs-Loop you can simply buffer the previous record-value(s) - and do your calculations then on these temporary variable-structures, holding the previous data, easy to combine "some lines later" with the current record-values. The needed SubSelects in the pure SQL-alternatives have to be performed for each "next record" the SQL- engine "collates" (in your concrete case here: Recordcount times) > As you noted, perhaps not in my case since the amount > of data is not that extensive to begin with. Yep, that "hides" the performance-issues, since even these slower performing queries will probably return below the 50msec-interval a user is able to "note". > 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). I'd say no - he was basically suggesting the same thing IMO - to just use the right mix. > So if the advice is not to abandon the SQL/SQLite > approach, but to stick to simple SQL ... "Simple" is relative - as you write yourself - your App already performs faster using SQL for the right things - and that don't have to be only "simple queries" - what you already do with all these nice Group By queries - directly delivering weekly or monthly stock-data, derived from your daily-based records-table - with all these Min, Max, Avg, etc...aggregated- values ... - this is the area, where SQL shines. > 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. Yep - compared with Arrays, these Objects are simply the more dynamic and flexible container, which you can pass around over your routines as a 4-Byte-ObjectPointer. Since interfaces were already mentioned - here you have already a very flexible "generic-base-container-interface" as your workhorse - able to access the content in an array- like way - or per FieldName and Rs-Cursor-shifting ... you can do Rs-local sorting in different ways (without touching the DB-engine again with these sorts, Find-Methods, etc.). [Demo-routine] > 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. As already said - you never know in that group... And it is always something like a sportive challenge, to make the "seemingly impossible possible" with plain SQL for the Gurus here, just for fun, to stay fit... ;-) > 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. That's good, since that works even faster - no need, to do unnecessary FieldName-String-Resolving into the Rs.Fieldlist on each iteration (just posted that for more clarity). > Okay. The final verdict is to go ahead with the original plan >... > Then using that recordset, I will loop through it and perform > the comparison and recordset update... > > I think this would be much more preferrable than transferring > the data into arrays and then manipulating them. Yep, as long as you don't have to squeeze out the last bit of performance, then working with the more universal Rs-Containers and their flexible, generic Interface would be the way to go IMO. Olaf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
