Non Blocking revExecuteSQL?

[davec]

I have a query which takes a long time to complete on the backend DB (PostgreSQL 8.1.4). Using revExecuteSQL hangs the script until the query has finished.

I can't see any mention of prcesses in RR (I'm using 2.1 Win32) but is there anyway to put a script or handler into a background process?

BTW, the query does not produce a results set, it just runs a stored procedure and passes in parms from RR. i.e. "SELECT proc(:1, :2)"

I'm thinking I might have to shell out to an OS process, or hand off to a middleware server to run this.

Thanks in advance.
Dave.

[marielle]

I can't see any mention of prcesses in RR

In the dictionary, check out under:

open process - Use the open process command to start up a process you want to either send data to or get data from or both.

openProcesses - Returns the names of processes that have been opened with the open process command, but have not yet exited.

I will let more expert than I am on this chime in.

[davec]

open process - Use the open process command to start up a process you want to either send data to or get data from or both.

Thanks for this marielle. I was aware of the open process command, and I may still use that to run an exe that triggers my DB process.

I suppose what I'm looking for is a thread mechanism (maybe I should have used this terminology) , so that a handler can run as a background process within the RR engine. I don't think it has this, which is a pity as the whole GUI locks up waiting for the db command to finish.

There may be tips & tricks to workaround this.

[marielle]

Multi-threading (running a process in the background), no, not built-in as far as I know. Some persons have done some work on this. They tend to be use-list users... It could be worth posting there (use-list) as well.

[davec]

They tend to be use-list users... It could be worth posting there (use-list) as well.

Would have a link for this please. Is this a usenet group? Thanks.

I've been working on optimising the SQL today and managed a big speed up. Given the amount of data processed I will have to impliment a process outside of RR anyway.

Cheers

[Bernard]

Dave,

here's a link to the mailing lists page:

http://support.runrev.com/lists/

I recommend not subscribing to the digest version - with single messages you can use the subject line in search results in your mail client.

You can also browse the archive going back to November 2001 from within the runrev site (but no search unfortunately). Google used to do a good job of indexing it, but about 6 months ago something got screwed up (you can do a google search along these lines "group OR background site:runrev.com"). There is a utility tool to search the archive via various interfaces (google, gmane, etc). Let us know if you want that.

Also, if you click the 'Documentation' menu in rev, the window that appears has a 'search' button on the far right. Clicking that brings up a smaller window, and the 2nd button of that window is 'Gmane'. You can search the archive from there.

[davec]

Dave,

here's a link to the mailing lists page:

http://support.runrev.com/lists/

Ah, I'd forgotten about those lists. Thanks. I'll have a look see on the lists. I did subscribe to one many moons ago when we first purchased RR. I've sort of gone off mail lists and prefer forums. They are, however, a useful knowledge base.

I realise that the magnitude of the problem mentioned requires that I trigger a backgound process on the server. But for any query that takes >= 1sec to execute it would be annoying to a user if the GUI keeps locking up. BTW, I don't know if this is occures on other platforms or is particular to the GUI model used on the Windows platform. Perhaps I should suggest a feature request to allow asynchronous executeSQL in that SQL that does not return anything can be sent and the script carry on execution. Maybe something like "put exectuteASQL <SQL> into errorCode". A "isASQLComplete errorCode" command could then check in the future for any error/non-error codes returned from the operation.

Best,
Dave

[Bernard]

Hi Dave,

About this long running SQL query.

I don't use the revDB functionality myself, but I would be surprised to hear there is no callback mechanism.

I have seen people on the list recommending Trevor Devore's database library over the revDB commands (I believe his is a wrapper for the revDB stuff). Here is a link to his libDatabase:

http://www.mangomultimedia.com/developer/revolution/

I just did a quick search but I can't find anything on callbacks in the connection of the revDB library. Hopefully someone else can tell us there is a way round this. Other parts of rev do use callbacks (e.g. URL loading, sockets, quicktime too I think).

I can recognize that what you are asking for is a good enhancement. However, can you explain the context in which there is this perceived delay - is your app drawing itself based on the SQL results? If so, I can imagine how awful those 1 second delays must be. If it's just a question of bringing back data to display, then can that data not be just brought into Rev and then manipulated (filtered, sorted, etc) in Rev? Are you keeping an open connection to Postgres? What sizes are the tables you are manipulating? How complex is the SQL? What kind of performance do you get from the same SQL running throught Postgres isql utility?

My questions may be totally off the mark - since I've never used Postrgres or the revDB library. I am only asking these to see if there is some way you can get your response time down by a significant factor. It might take runrev a long time to implement something like the callback mechanism you are suggesting.

I am using Java to talk to the database, and then having rev talk to the Java servlet. I have queries that do 3-way joins to tables containing between 10,000 and 100,000 records. If it is an exact match query, the results are returned in less than a second despite passing through several layers of abstraction. If the query requires a full table scan and returns 4000 results, then it can take much, much longer. However, once the data is in Rev I have no need to return to the database, unless to find a totally different result set.

[davec]

http://www.mangomultimedia.com/developer/revolution/

I can recognize that what you are asking for is a good enhancement. However, can you explain the context in which there is this perceived delay - is your app drawing itself based on the SQL results? If so, I can imagine how awful those 1 second delays must be. If it's just a question of bringing back data to display, then can that data not be just brought into Rev and then manipulated (filtered, sorted, etc) in Rev? Are you keeping an open connection to Postgres? What sizes are the tables you are manipulating? How complex is the SQL? What kind of performance do you get from the same SQL running throught Postgres isql utility?
.

Once again, thanks for a link . I'll have a look at this.

These are good questions. When I say GUI lock ups, I should say that the RR app itself does not respond to user interaction. For example mouse click events are not propagated. If I click on the window title bar I get "Not Responding" added by the OS. This is typical of many apps on Windows. (I don't consider Windows in general to be very good at multitasking when there is data intensive processing going on).

I know a couple of seconds does not seem like a lot, but it prevents users switching away from the window which initiated the query to do something else with the system. Also, depending on the criteria the user selects, a query could run for quite some time or, inversely, very quickly. (For the query I'm talking about its running at about 115 completed transactions per sec which is after refactoring, and I doubt I'll get it any faster).

There is no redrawing based on the SQL. It's simply that the executeSQL statement waits for the server to respond.The SQL isn't that complex, but it builds new data from an underlying model which is in itself quite complex (it's not an in-house model, but one which we have to use to comply with national standards). The model deals with biodiversity data and we have approx one million taxonomic observations for our geographic area.

I do test with psql on UNIX as well as pgAdin3. At the moment I keep one connection open in RR for all SQL work.

Best,
Dave.

[Bernard]

Clearly your dataset is non-trivial, and you sound very knowledgeable, so I don't think there is much more I can offer you.

One last question: is the database on the same machine as your Rev app? It sounds like the whole machine is compute-bound - I mean, is it the db that is churning and causing your problem or is definitely Rev that is causing the lock-up?. I use OS X, and I almost never see that kind of 'not responding' message you are getting on Windows, even when I have switch from one very busy app (Revolution or something else) to use another app while I wait for the busy task complete.

[davec]

One last question: is the database on the same machine as your Rev app? It sounds like the whole machine is compute-bound - I mean, is it the db that is churning and causing your problem or is definitely Rev that is causing the lock-up?.

Good thought Bernard,

I am testing RR on the same machine that I'm using as a development PG server. (The production DB is on a UNIX server, but I wont be moving any of the code over until after Christmas) . So, I've just logged into my machine at work to test this and yes, the DB process sucks 100% CPU cycles. RR just shows the hour glass while my test query ran (~70secs). It does this, of course, when the query is executed in PgAdmin3 on the same box, only this time I can get a response from my RR GUI.

Anyway, it's been a great introduction to this Rev forum and thanks for your input. I'm going to wait until we get to play with a new Server after Christmas to see if I need a 2 or 3 tier architecture with Rev as the front end. Like you I have a middleware server (exept we use Smalltalk ) so I could hand off to a servlet too. That would also allow me to use the load command in the backgound too I think.

Best,
Dave

[marielle]

Like you I have a middleware server (exept we use Smalltalk ) so I could hand off to a servlet too. That would also allow me to use the load command in the backgound too I think.

It's nice to hear these words here.... do you have more details you are in a position to share on your current settings (middleware architecture)?

[Bernard]

I've just logged into my machine at work to test this and yes, the DB process sucks 100% CPU cycles. RR just shows the hour glass while my test query ran (~70secs)

Sorry to be a bit thick, but am I right in thinking that you're saying that it is the db that was causing the general unresponsiveness, and that you are now expecting Rev to seem like less of a hog? I still think your idea for some kind of callback architecture in the revDB library sounds like a good idea.

Like Marielle, I too would be interested to hear more about your Smalltalk middleware. Smalltalk has been on my list of things to learn for a couple of years.

[davec]

If you notice the time of this post, yes it is early AM - I've been up with my 3yr old daughter since 5am

I right in thinking that you're saying that it is the db that was causing the general unresponsiveness, and that you are now expecting Rev to seem like less of a hog?

Yes & No. I think we have a combination of these factors;

1) The DB query is processor intensive.
2) I'm using Windows XP Pro, not a server OS on my development machine. Rev & the DB have to share resources shared out by XP.
3) The Windows GUI, to me anyway, always blocks an app that is processor or network intensive.
4) Rev is waiting for the executeSQL command to complete.

So, I think I'm saying that it's not entrely Revs fault, but the combination of these factors. I still think Rev will hang even when the DB I'm working on is moved onto the server.

I still think your idea for some kind of callback architecture in the revDB library sounds like a good idea.

It would be great to see that in revDB sometime.

Like Marielle, I too would be interested to hear more about your Smalltalk middleware. Smalltalk has been on my list of things to learn for a couple of years.

To Marielle also, that would be no problem. Maybe I should repost the details of that in "off topic" some time. Basically we use Cincom's Visualworks NC (Non commercial as we are a not for profit, funded organisation). You can download from here http://www.cincomsmalltalk.com

I find Smalltalk sits very well with Transcript in terms of productivity and the art & craft of writing software. Both are "live" systems - make changes see the effects immediately.

Visualworks has a GUI builder and is multi-threaded, so the possibility is there to build my front end with that. Except this is RRs strength - it's just so nice to work with to build network aware apps. Also, It's easier for me to build GUIs in Rev as I'm haven't the skills to do the same in Smalltalk yet.

Best,
Dave