Asynch Cursor and Indexing?

[dunc0029]

Just curious if anyone had a solution for this??? I have data classes that uses asynch fetching from an Oracle backend. One feature of our record select screens is creating indexes on the cursor to allow the users to sort by various fields and lookup values. However, when you try to create an index on the result cursor, it pulls the whole result set before returning control, so you lose the asynchronicity.

I tried using a "no data on load", create indexes, then Requerying, but that has the same problem.

Does anyone know of a way to implement this behavior of building the index asynchronously as it pulls the data?

 

[ramani]

Hi

I have not done drawing from oracle. But just thinking that this idea may help you.

When I need an empty skeleton... I query the table with a WHERE .f.

This gets an equivalent of NO DATA ON LOAD. Then index and keep the cursor for requery() with appropriate where class.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[dunc0029]

I did try your suggestion, which had similar results to using the nodataonload. It does create the indexes on the empty table. However, once you requery, it goes into synchronous-like behavior ( similar to if you issue a GO BOTTOM on an Asynch cursor ). I'm trying to brainstorm about a workaround. I'll post something if I can figure out a solution. Otherwise, feel free to help!

 

[craigsboyd]

dunc0029,

How would an index be created on a result set client-side without pulling the entire result set down from the server? Doesn't seem possible to me. VFP has to know the entire result set before being able to index it properly. Seems like something would need to be done server-side instead in order to have a chance of this working. That's my thinking anyways, but maybe I don't fully understand what you are trying to do here?

http://www.sweetpotatosoftware.com

 

[Mike Lewis]

Dunc,

As an alternative appoach, how about this: instead of indexing the cursor, you could bring down multiple cursors, one for each sort sequence that you want to let the user see. The sorting would be achieved by an ORDER BY clause.

Although this might sound slower (depending on how many sort orders you want to support), the advantage is that the sorting would be done on the server, so you would save the time it takes to create the indexes within VFP. Also, your user would be able to access the first sort order while the others were still downloading, therefore maintaining ansynchronousness.

This is just off the top of my head. If if doesn't fit in with what you are trying to achieve, just forget it.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Olaf Doschke]

VFP has to know the entire result set before being able to index it properly.

I disagree, why shouldn't it be possible? It's also possible to create indexes on an empty table and each record added also get's added to the index. The incapability of vfp remote views has to be related to something diffrent.

I'd suggest using 2 cursors, one for asynchronous fetching of data and one that you fill from the first one and index.

eg. one is a remote view as is, the other one you create as a local view to this view and you add the indexes you want on the fly to that local view.

It will bring in new problems: You have to update your remote view from the local view before that updates the oracle table...

Bye, Olaf.

 

[craigsboyd]

OlafDoschke,

Let's say that your entire result set is made up of two fields (simplified example here), one for the Name and one for the Age of a table full of people information...

JIM 27
KELLY 35
SUSAN 30
BOB 65

Now the user sorts on NAME so BOB should come first, then JIM, and so on. Then they sort on AGE which obviously would be JIM, then SUSAN, and so on. There is no way for VFP to index those records Asynchronously unless they are coming down from the server in the correct order to begin with, but then indexing (for the purposes of sorting) wouldn't be necessary to begin with. I still maintain that it is impossible for VFP to index records correctly from client-side like that.

Sure, you could conceive of something where VFP indexes the records as the data is downloaded, but that defeats almost all of the advantages that one would hope to gain from this type of Asynchronous fetching. And, it is completely unacceptable given what it is being proposed for here, which is sorting. Its of no use to the user to see the records indexed (sorted) as they are fetched, they need to see them in the Ascending or Descending order in which they would be given the entire result set. The problems that could occur by giving the user access to an imcomplete sorted result set are many (goes without saying really).

The best solution to the problem that I can see is to have oracle send the result set down in the order in which it is needed (faster initial display and perceived application reaction time, but cost in bandwidth and resources every time the user changes the sort), or wait until the entire result set has been fetched to index/sort (large initial resource hit, but then things become easier - also reduces the complexity of the code and development/maintenance time). Or, a little of both and some slight of hand (best of both worlds, but a bear to implement and maintain).

That's my take on it, and I guess I can't understand why you'd disagree with those assertions. Perhaps I am missing something here?

http://www.sweetpotatosoftware.com

 

[Olaf Doschke]

Hi Craig,

well, you are mainly argumenting with performance and that an ordered intermediate result is of no use. But that's different to saying it's impossible.

I see the points you make and the problems that arise. Agreed, it may not be the best solution if the purpose of asynchronous fetching is mainly performance.

But even if the performance would be slower than with an synchronous fetching of the resultset, I'd say being able to scroll through an intermediate and uncomplete result may be sufficient, it depends on what that list is for.

An example outside of Foxpro: Search for files (eg minimum 100 KB) on C: and you'll get in results and can scroll through them and order them the way you want. You don't have to wait for the whole drive to be searched and can cancel the search if you see what you were perhaps looking for.

I'll give it a try and maybe post an example within this year, but I'm afraid I won't make it as a christmas gift.

Bye, Olaf.

 

[craigsboyd]

An example outside of Foxpro: Search for files (eg minimum 100 KB) on C: and you'll get in results and can scroll through them and order them the way you want. You don't have to wait for the whole drive to be searched and can cancel the search if you see what you were perhaps looking for.

Point is well taken, nice job of giving an example where this functionality is desirable.

http://www.sweetpotatosoftware.com

 

[dunc0029]

Thanks for the discussion. As Olaf mentioned, the behavior I was hoping for was the "intermediate results" sorting. It seems like something that should be possible. I just want a record-select screen to come up FAST, have the results returning asynchronously, and let the user sort/lookup as the results are being returned (without having to cancel the query, change the order by clause and resubmit).

 

[Olaf Doschke]

Hi Dunc,

this thread is not forgotten. I still had no time to work on a concrete example as I outlined.

I think it should be possible by SQL-Passthrough in a asynchronous connection. You need one View that get's queried asynchronous, and one that's indexed and get's filled by that view in a loop that runs as the source view is asynchronously fetched.

Let's say we have Queryview and Showview, then while SQLEXEC runs a loop fills Showview from Queryview with

SELECT Showview
APPEND FROM DBF("Queryview");
For primarykeyfield>maxkeyinShowview

Or some appropriate other for clause only appending records not already appended.

I fear, that you don't have access to the asnchronously built view or cursor unless the SQLExec finished. And even if that doesn't fail, you'd have problems in browsing through the Showview (perhaps as a recorssource of a grid) while there are permanently records appended, which moves the recordpointer.

A solution may be to provide a "Pause" button, to suspend fetching into Showview from Queryview, while SQLEXEC will not pause and continue adding records to Queryview.

Bye, Olaf.