Moving from Vcursor to Vsets

[Dave Addey]

> From: Ruslan Zasukhin <sunshine at public.kherson.ua>
> Reply-To: Valentina Developers <valentina at lists.macserve.net>
> Date: Fri, 20 Jul 2007 11:09:43 +0300
> To: "valentina at lists.macserve.net" <valentina at lists.macserve.net>
> Conversation: Moving from Vcursor to Vsets
> Subject: Re: Moving from Vcursor to Vsets
> 
> On 20/7/07 1:30 AM, "Dave Addey" <listmail1 at dsl.pipex.com> wrote:
> 
> Hi Dave,
> 
>> Hi there,
>> 
>> I¹m trying to split up my SELECT statements into smaller chunks, using VSets
>> and the FindXxxx methods.  I¹m wondering ­ how would I select a primary key
>> such as this:
>> 
>> SELECT * FROM Tracks, PlaylistTracks WHERE PlaylistTracks.TrackUniqueIDPtr =
>> Tracks.UniqueID AND PlaylistTracks.PlaylistUniqueIDPtr = 3
> 
> And how you DISPLAY/USE this cursor later?

It is displayed in an Einhugur DataGrid, so it is accessed in both
directions, and is kept open.  It is:

VDatabase.SqlSelect(fullSQLText, EVCursorLocation.kServerSide,
EVLockType.kNoLocks, EVCursorDirection.kRandom)

>> (PlaylistTracks.TrackUniqueIDPtr is a Foreign Key Link to Tracks.UniqueID,
>> and PlaylistTracks.PlaylistUniqueIDPtr is a Foreign Key Link to
>> Playlists.UniqueID)
>> 
>> ...using VSets?  I can see how to do the individual selects using VSets, but
>> I can¹t see how to join the two together with the foreign key links.
> 
> Vsets and joins ==== it needs change mind :)
> 
> * SQL JOIN produce NEW TABLE that have fields of other tables.
> 
> * this new table already LOOSE info about original entities: Tracks and
> PlayList.
> 
> * SETS in contrast allow you keep this info, but require a lots more manual
> work. And I am not sure that in REVOLUTION its very good idea to use SETs
> for intensive calculation (or with vserver).

This makes a lot more sense now.  (btw, I'm using V4RB, not V4REV.)

> Sets can give you win if you really will reduce with them access to db info.
> 
> ------------------------------
> Steps are:
> 
> 1) SEARCH in PlaylistTracks table
> 
>     set1 = PlaylistTracks.PlaylistUniqueIDPtr.FindValue( 3 )

This is very quick.

> 2) FIND linked records from table Tracks
> 
>     set2 = linkFK.FindLinked( PlaylistTracks, Tracks, set1 )

For a small set from 1), this is also very quick.  For a large set (70,000
records, VLink.FindLinkedAsBitSet can take 4 or 5 seconds.  So maybe this
isn't the way for me to do it.

>> I¹ve been doing it the VCursor way so far, but some of my SELECT statements
>> take too long to execute, so I want to chop them up into smaller chunks.
>> I¹m thinking of doing this using several VSet methods, each of which reduces
>> the set another step, bit by bit.
> 
> May be better find why they are slow. May be indexes are missing.

Actually, they aren't that "slow" - it's just that the SQL is reasonably
complex (table JOIN, multiple ORDER BY fields, multiple LIKE statements).
The problem is, I'm playing QuickTime audio on the main thread, and
*anything* which takes a while to complete ( > 0.5sec ) can cause the audio
to drop out.

I think I may just need to find a way to perform my large SQL selects on a
background thread.  No matter how much I speed things up, or no matter how
much I chop the select / order / find into smaller VSets, someone will still
do a large enough search on a slow enough machine, and get audio dropouts.

Thanks for the help!

Dave.

> -- 
> Best regards,
> 
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
> 
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
> 
> [I feel the need: the need for speed]
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina