Sets from Cursors Re: VCursor.AddRecord

[Ed Kleban]

On 12/2/05 10:31 AM, "Sims, John" <ayu8 at cdc.gov> wrote:

>     
>>    Is there a technical reason why you could not provide the
>> functions:
>> 
>>        VCursor.AsArraySet() as VArraySet
>>        Vcursor.AsBitSet() as VBitSet
>> 
>> Which is different from the question:
>> 
>>    Is there a philisophical reason why you would NOT like to
>> provide these functions?
>> 
>> 
> 
> Hi Ed,
> 
> I certainly can't answer for Ruslan,

Certainly none of us can.  We users may be many things, but being Ruslan is
not one of them.   
-- Ya gotta wonder how well that sentence translates to Ukrainian.

> but one thing that occurred to me
> immediately is the fact that VArraySets and VBitSets only hold the
> RecID's of the records and, of course, the Vcursor may hold many
> different fields.

Yes, but in the cases I'd want to use this I would likely issue an SQL
command that ONLY returned RecID fields.  In fact if possible, I'd have it
return no fields at all, because in the end what I'm after is the bitSet or
ArraySet.

> I'm not sure what the use of converting the Vcursor
> to a Vset would be unless you just wanted to get the RecID's of the
> records in the Vcursor to iterate through them outside of the
> Vcursor...

That's exactly what I want it for.

> which would allow you to make modifications to fields not in
> the Vcursor, meaning you wouldn't have to request that data in the first
> place, which lowers the amount of data moving back-and-forth/network
> traffic ... 

You make a great straight man.  How much would you charge for this service
on an ongoing basis?  Perhaps we can work out a deal.

> Ok, I've kind of talked myself into liking this suggestion
> :-)
> 
> Ruslan?

Yeah, you've got the idea alright.  Consider the following.  There are a
good many things where either the most efficient or the ONLY way to
accomplish them is by issuing a SQL query to obtain a cursor.  But there is
also a complete set of very efficient search commands available through the
V2 API that are much faster than using SQL for simple queries since: 1) You
don't need to write the SQL, 2) The SQL parser doesn't need to read and mess
with the SQL, and most importantly 3) Using the API calls you get sheer
Ruslan-hand optimized speed as opposed to the best that the Ruslan SQL
optimization logic can come up with.

Unfortunately, there is no bridge between these two mechanisms since the
Find commands work only on tables, not cursors, and the first time I even
hinted at it Ruslan almost had a major heart attack right here in the email
list.  Which is certainly something none of us want.

Consider an example:

Ruslan has just added bit_and & friends which can now be used do blindingly
fast genetic searches on a gazillion records to find the 1% that most likely
have the candidates you care about.  But you still have to weed through all
those candidates.  You could write a far more complex query in SQL that
would eventually get the job done and return fully qualified matches.
Alternatively you could write a loop that manually traversed over each
record returned in the cursor and apply whatever logic you'd like written in
the native development language.  But I'm guessing that there is a large
class of sophisticated conditional checks you might want to do for which by
far the most efficient tool to weed these out is some variation of:

  aVBitSetOfRecsIwant = FindXxxx( withinVBitSetFromCursor ... )

Or possibly a successive chain of such calls.

Now it may turn out that Ruslan will end up telling us this is foolish,
because no matter how much less efficient SQL is compared with the API
calls, that the overhead of Creating, Parsing, and running a single SQL call
is so minimal that you're far better off investing the time in letting SQL
do all the work in a single query.  That may be true, but I'd like to hear
him chime in on the topic.  I always learn good things when he does.