estimate update statements duration

[Former Member]

Good afternoon and happy new year to all of you,

I'm working for a software company, more precisely in a team in charge of upgrading clients from version A to version B.

This upgrade implies DDL statements and DML statements and I'm often asked for an ETA for different tasks. Classical example would be

- Hi Simon, where do we stand in the upgrade process

- Well now we're running an update statement on table A (statement can used several tables) for about 2h

- OK do you have  an ETA for the end of the update or the end of the upgrade?...

Today I'm interested in update statements and I'd like to know if you have any recipe that could help me the 'estimated' duration of an update.

I came to this question because I accidentaly "Crtl-C" and update in progress and it immediately ROLLBACK

I know that since Sybase 12.5.2 9 (I think) you can use command

kill <spid> with statusonly

to have an idea of the duration of the rollback

Therefore I'm just wondering how I could estimate the duration or the remaining duration of my upgrade.

Regards

--Simon

[Former Member · Accepted solution]

Hi Simon,

1. As Mark mentioned , there is no way to get estimate for time (at least I don't know any), but you can get cost/lio/pio estimate, what may bring some idea to you about time ETA. But you should keep in mind that the main purpose for this cost to get comparable measure of the plan efficiency. And estimated lio may appear to be far from actual due to the inaccurate statistics .Also you should double check that the actual query is running with the same plan for which you got the estimate.

set plan for show_best_plan_xml to message on

go

set textsize 100000

go

set noexec on

go

<here your query>

go

set noexec off

go

select showplan_in_xml(0)

go

2. To check how far the process is (actual lio/pio) you may check following mon tables:

monProcessStatement (compare StartTime to current time to see how long the statement is runnig), monProcessObject (how many ios on the objects you have), monProcessActivity

[Former Member · Accepted solution]

but if there no so many joins and the update takes long time just because of table scan, if you will simply measure how many pages are read from disk in unit of time and then based on the table size estimate table scan duration, you will not be too far.

[Former Member · Accepted solution]

Hi Simon,

I very much agree with Mark and Oleksii the estimation of time can never be very
accurate because think if another user take a logical lock on a  page or row which this statement want to update now everything depend on this other user and when he release the look this is
not even always up to the RDBMS.

But if this is some batch related sql,  I would collect statistics for its previous runs
and work out my estimate from that.   

Rgds, Niclas 

[Former Member]

Thanks all for your feedback,

Indeed I didn't expect to have a magical solution, still all your suggestions are very valuable and I'll try to keep all of them in mind while I'm challenged on providing any ETA

Thank you

Simon