I have a SQL script that I want to output progress messages as it runs. Having it output messages between SQL statements is easy, however I have some very long running INSERT INTO SELECTs. Is there a way to have a select statement output messages as it goes, for example after every 1000 rows, or every 5 seconds?
Note: This is for SQL Anywhere, but answers in any SQL dialect will be fine.
There's no way to retrieve the execution status of a single query. None of the mainstream database engines provide this functionality.
Furthermore, a measurable overhead would be generated from any progress implementation were one to exist, so if a query is already taking an uncomfortably long time such that you want to show progress, causing additional slowdown by showing said progress might not be a design goal.
You may find this article on estimating SQL execution progress helpful, though its practical implications are limited.
SQL itself has no provision for this kind of thing. Any way of doing this would involve talking directly to the database engine, and would not be standard across databases.
Really the idea of progress with set based operations (which is what a relational database uses) wouldn't be too helpful, at least not as displayed with a progress bar (percent done vs total). By the time the optimizer figured out what it needed to do and really understood the full cost of the operation, you have already completed a significant portion of the operation. Progress displays are really meant for iterative operations rather than set operations.
That's talking about your general SELECT statement execution. For inserts that are separate statements there are all kinds of ways to do that from the submitter by monitoring the consumption rate of the statements. If they are bulk inserts (select into, insert from, and the like) then you really have the same problem that I described above. Set operations are batched in a way that make a progress bar type of display somewhat meaningless.
I am on the SQL Anywhere engine development team and there is currently no way to do this. I can't promise anything, but we are considering adding this type of functionality to a future release.
There's certainly no SQL-standard solution to this. Sorry to be doom-laden, but I haven't seen anything that can do this in Oracle, SQL Server, Sybase or MySQL, so I wouldn't be too hopeful for SQLAnywhere.
I agree that SQL does not have a way to do this directly. One way might be to only insert the TOP 1000 at a time and then print your status message. Then keep repeating this as needed (in a loop of some kind). The downside is that you would then need a way to keep track of where you are.
I should note that this approach will not be as efficient as just doing one big INSERT
Here's what I would do (Sybase / SQL Server syntax):
DECLARE @total_rows int
SELECT @total_rows = count(*)
FROM Source_Table
WHILE @total_rows > (SELECT count(*) FROM Target_Table)
BEGIN
SET rowcount 1000
print 'inserting 1000 rows'
INSERT Target_Table
SELECT *
FROM Source_Table s
WHERE NOT EXISTS( SELECT 1
FROM Target_Table t
WHERE t.id = s.id )
END
set rowcount 0
print 'done'
Or you could do it based on IDs (assumes Id is a number):
DECLARE @min_id int,
@max_id int,
@start_id int,
@end_id int
SELECT @min_id = min(id) ,
@max_id = max(id)
FROM Source_Table
SELECT @start_id = @min_id ,
@end_id = @min_id + 1000
WHILE @end_id <= @max_id
BEGIN
print 'inserting id range: ' + convert(varchar,@start_id) + ' to ' + convert(varchar,@end_id)
INSERT Target_Table
SELECT *
FROM Source_Table s
WHERE id BETWEEN @start_id AND @end_id
SELECT @start_id = @end_id + 1,
@end_id = @end_id + 1000
END
set rowcount 0
print 'done'
One thought might to have another separate process count the number of rows in the table where the insert is being done to determine what percentage of them are there already. This of course would require that you know the total in the end. This would probably only be okay if this you're not too worried about server load.
On the off chance you're using Toad, you can generate a set of INSERT statements from a table and configure it to commit at a user input frequency. You could modify your scripts a little bit and then see how much of the new data has been commited as you go.
You can simulate the effect for your users by timing several runs, then having a progress bar advance at the average records / second rate.
The only other ways will be
1 - Refer to the API of your database engine to see if it makes any provision for that
or
2 - Break your INSERT into many smaller statements, and report on them as you go. But that will have a significant negative performance impact.
If you need to have it or you die, for insert,update,delete you can use some trigger logic with db variables, and time by time you do sql to retrieve variable data and display some progress to user.
If you wan`t to use it, I can write an example and send it.
Stumbled upon this old thread looking for something else. I disagree with the idea that we don't want progress information just because it's a set operation. Users will often tolerate even a long wait if they know how long it is.
Here's what I suggest:
Each time this runs, log the number of rows inserted and the total time, then add a step at the beginning of that process to query that log and calculate an estimated total time. If you base your estimate on the last runs, you should be able to present an acceptably good guess for the wait time for the thing to finish.
