Re: Stored procedures and blocking Sybase processes

["Wanko, Christopher G, CFCTR" <apollo@ATT.COM>]

> When we run SQR and allow it to create stored procedures, the stored
> procedure that is created will put exclusive table locks on all the
> database system tables (syscolumns, sysindexes, etc..) thus
> locking every one out until the sqr finishes.  So, we are currently
running
> in production with the -XP flag set, this prevents the locking / blocking
> problem, but the reports run far too slowly for a production system (a 45
> minute report went to 7 hours!!!)  In some benchmarking that I have done I
find that
> sqrs' that create stored procedures run 8 to 10 times quicker
> than without stored procedures.  I have tried a couple of things such as
> setting the isolation level to 0, ensuring chained transaction is set to
> on, etc...

I wonder if the SQR *has* to create the stored procedure... I mean, if you
created it once, can't you make it persistent so you won't have to keep
creating it?  Or is it one of those on-the-fly constructions that
specifically addresses user input at the time?

I mean, exclusive locks on system tables for nearly an hour is one hell of a
report.  Can't you run it at night?

If you have to run it against system tables, you're going to want those
locks, I suspect.  Short of making the SPs persistent, I don't know of any
general tips to dealing with the problem.  I'd sure like to see who would,
as this sounds interesting.

-Chris