November 2, 2006 at 11:01 am
Hello,
My C# program places queries to an SQL database that sometimes take a very long time to finish.
Is there any way to set up a query to report back its progress ever X seconds, so my user interface can tell the user the current status?
Ideally the report-back would even ask for permission to continue, so the user could cancel if he wanted.
Thanks if you can help,
John
November 2, 2006 at 5:12 pm
November 3, 2006 at 4:56 am
You might want to consider breaking the queries down into chunks using temp tables(or permanent tables you later drop). you would then be able to do some actions in between.
Depends on whether your queries allow for this. This may also increase the total amount of time required to run the query.
November 3, 2006 at 6:02 am
In ADO the Open method of the Recordset object allows you to code the adFetchAsync parameter. I have never used it. But I presume it means:
1. code execution continues after the Open method was issued
2. it will fire an event (which you have to set up) when fetching has completed
At least your program can do something useful, like playing a song telling the user to be patient...
It's not quite what you are looking for. But it's going in that direction...
November 3, 2006 at 9:10 am
Hi mate,
you should run the query on a worker thread, show a status form of some kind (with real progress bar if you want to break the query into some parts, or just an eye catching looping animation) and have a cancel button on that form.
Check out the code sample here (look for the function RunOnWorker): http://www.albahari.com/queryexpress.html
November 3, 2006 at 9:39 am
QueryExpress: very nice little tool indeed.
November 3, 2006 at 10:20 am
Thanks for your suggestions to run the query in the background various ways, but I already do run the query in a separate thread while an animated gif in the main window tells the user to wait.
When the query returns the data, we display it in a bar graph.
We were hoping to be able to
1. animate the bar graph growing every few seconds to show query progress, and
2. allow the user to kill the query before it's finished in case he changes his mind.
From your information it sounds as if our only hope for #1 is to break the query up into many smaller queries. So let's focus on #2: Is there any way to kill a query in progress? Perhaps from a separate thread?
Thanks,
John
November 29, 2006 at 11:37 pm
After opening the connection, but before running your long query, store the results from this in a variable in your app/report: @@SPID
@@SPID is the current SQL thread that is executing your query. Say you stored @@SPID in a variable called MySPID You can then open a new connection and execute the following SQL: KILL MySPID.
Note: This gets more complicated if application connection pooling is being used...
You should really look into optimizing your query or caching the data returned (i.e. scheduling the report to run once an hour, day, week, or whenever), especially if it takes close to that long to run anyway OR if the report has the possibility of being executed by more than one person at a time.
You can do this using a temp table, and another table. First, generate the data (run the query) and place it in the temp table. Once that's complete, delete the data from the other table and insert the the data from the temp table.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply