Re: Stored procedures and blocking Sybase processes

[Steve Krim <SMKrim@TELEWEB.NET>]

I'm sorry if I wasn't as clear as I should be, I've been fighting this for
2 weeks now...

For the Sybase Platform, SQR will create a stored procedure for most of the
sql that is embeded within the SQR (The exact rules are documented, but
they escape me now...)  The reports that we are having problems with don't
touch the system tables at all, rather 100% user tables.  I _think_ the
exclusive locks are put in during the creation and for the lifetime of the
stored procedures that are being created by SQR.  Additionally, the client
library has changed between SQR 3.0.12.x and 3.0.18.1, from db-lib (older -
clunkier) to ct-lib (latest and greatest...;-<>)  This really seems to be
the killer, db-lib vs ct-lib...  Any and ALL ideas - suggestions are
greatly appreciated!!!!

Thanks,

Steve

At 03:14 PM 10/27/98 -0500, you wrote:
>> 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
>