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.

Saffier

Answer 1

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.

Orthopterous

I thought so too, but a little too much theory to be particularly useful I think. It's too bad there's no "with statistics" kind of query hint in any rdbms we know, but thinking about it, implementation would be so difficult and resource-consuming I guess it's not surprising. – Orthopterous

"None of the mainstream database engines provide this functionality - that's not true. Oracle has v$session_longops: docs.oracle.com/cd/E11882_01/server.112/e25513/… – Regiment

Answer 2

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.

Camaraderie

Answer 3

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.

Traumatism

Answer 4

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.

Krems

Answer 5

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.

Dereism

Oracle has v$session_longops: docs.oracle.com/cd/E11882_01/server.112/e25513/… – Regiment

Answer 6

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

Mannino

this is the way I've done it before. Affirm that it is way slower. – Hueston

I might give it a try anyways. add 'where row_id between ...' to the main table in the join, then loop for each partition. I can deal with it being a little slower, so we'll have to see just how much slower it is. – Saffier

Answer 7

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'

Tipster

Answer 8

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.

Glaucescent

Assuming you are using a weak form of acid that lets you see results mid-transaction that would work, yep. – Saffier

Answer 9

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.

Cylindrical

Nope, not using Toad. If it was going to be easy to partition the INSERT statements then I could get a progress update between them, but that doesn't work for INSERT INTO SELECT statements. – Saffier

Answer 10

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.

Nombles

Answer 11

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.

Lymphangial

Answer 12

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.

Leodora