Begin and monitor progress on long running SQL queries via ajax

Question

Is it possible to start a query that will take a significant amount of time and monitor progress from the UI via Ajax?

I considered starting the process as a "run once" job that is scheduled to run immediately. I could store the results in a temporary table for quick retrieval once it's complete. I could also log the run time of the report and average that out, to guestimate the running time for the progress bar.

I use Microsoft SQL 2005 at the moment, but I'm willing to other DBMS such as SQL 2008, MySQL, etc if necessary.

Answer 1

One idea, if the long running job populates another table.

You have a 2nd database connection to monitor how many rows are processed out of the source rows, and show a simple "x rows processed" every few second

SELECT COUNT(*) FROM TargetTable WITH (NOLOCK)

If you have a source table too:

SELECT COUNT(*) FROM SourceTable WITH (NOLOCK)

..then you can use "x of y rows processed"

Basically, you have to use a 2nd connection to monitor the first. However, you also need something to measure...