July 5, 2006 at 12:28 pm
Hi Experts.
-In my program(a Win32 Application .. using Borland Delphi) I must show a Progress bar to the user.
-In my SQL code,I have used Cursors.
Now I need a way to get the Cursor location ( that is runing in its while loop) and pass it to my program, So that I can show the PROGRESS to clients
Is it Possible ?
How ?
What is your solution ?
DearExperts and Friends, I need your help.
__..._-__
July 5, 2006 at 12:58 pm
You could do the process in batches keeping track of how many batches of what size have been executed so far. If you have more than one connection to the DB, the process that is running the cursor could update a table (without holding locks) which contains the current (and expected?) rowcount. Another process could then poll this table for progress reports. Must you use a cursor?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 5, 2006 at 2:24 pm
You could store the count of all the rows that will be processed by the cursor in a variable.
Then use another variable as a counter and increment it by one each time you use the FETCH command to get the next value from the cursor.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 5, 2006 at 3:18 pm
The question is, how to pass this back to the client while the cursor is processing.
To expand on the idea of processing batches, you can open the cursor first, then scroll through it in batches using repeated invocations of a proc whose progress can be tracked by the client. You can increase batch size to improve performance, or reduce it for finer-grained progress tracking.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 5, 2006 at 4:16 pm
And if there is a transaction you may deadlock it forever.
I would suggest to get rid of the cursor and get it finished before progress bar can appear.
_____________
Code for TallyGenerator
July 5, 2006 at 4:29 pm
Yes, no dangling transactions please. Remiss not to mention it. And yes, once again, (all together now) must you use a cursor?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 6, 2006 at 2:14 am
Dear Experts, Thank you for your help.
Yes, Unfortunetly, I have to use Cursors.
The thing that I am doubtful about, is that Is it possible to get the value of a Global Variable or even calling a stored procedure that is responsible to return the row number ( tha same as counter) to my Client program ? do not forget that this stored procedure or the global variable ( counter) should be in the "while" loop of the cursor....
Thank YOU
__..._-__
July 6, 2006 at 7:19 am
To elaborate on Tim's initial response:
You will have to create a global variable for this using persistent storage. (A Table) Ignoring all philosophical/best practice concerns, I will offer a simple solution:
ProgressTable (
PID uniqueidentifier,
PCTcompletion tinyint)
The calling process creates a unique identifier. A GUID, perhaps. Caller must remember this identifier. This identifier is also passed to the SQL procedure.
The SQL procedure creates an entry in ProgressTable that contains the GUID and the progress indicator set to zero, and then begins its work. As the work progresses, the SQL procedure updates the progress indicator table for the particular GUID.
While this is happening, the caller, on a different execution thread, reads the progress value by looking up the status for the GUID and updates the UI to reflect the current state of things.
Once the SQL procedure returns to the caller, the caller will shut down the monitor thread, and then delete the progress record from the progress table corresponding to the GUID.
I don't think this is a great idea, but it should work. This will probably add a bunch of overhead to the process, but if the business requirements dictate a status bar, then you are stuck doing it somehow.
Good Luck!
jg
July 7, 2006 at 8:37 am
Thanks alot Jeff,Tim and other experts.
I will keep working on your ideas and will tell you the result later.
__..._-__
July 7, 2006 at 9:41 am
This requirement seems to be a self-fulfilling prophecy.
(1) Users usually want a progress bar because the query in question has a history/experience of being long-running.
(2) Original design may have used a record-at-a-time approach.
(3) SQL is most efficient (in most cases) when using set-based operations. Cursors are usually to be avoided at all costs.
(4) Progress bar requirement cannot be met without having a cursor somewhere in the solution, possibly involving a row-by-row execution of some additional i/o solely to produce stats for progress bar.
(5) Result: Need a progress bar to track long-running, cursor-based operation!
One might first want to stand back and look at the true business requirement (minus the progress bar) and test out a set-based alternative to meeting that business requirement. If the set-based alternative is highly performant (something which a cursor-based can rarely be), one could implement the set-based solution and - if the users are really adamant - "fake" a progress bar using timed delays, so as not to upset the users' expectations of a long-running query.
Just MHO.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply