How do I output progress messages from a SELECT statement?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

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'