Post reply

Performance problems with (unnecessary) nested stored procedures

  • November 12, 2009 at 7:37 am

    #1078341

    Sounds like a job for CONTEXT_INFO.

    If you put "SET CONTEXT_INFO 0x0" before every BEGIN TRAN (and possibly after every COMMIT/ROLLBACK), put "IF CONTEXT_INFO() = 0x0" before every "EXEC ContextCheck", and "SET CONTEXT_INFO 0x1" in the ContextCheck procedure when successful, it might do what you are looking for.

  • November 12, 2009 at 7:53 am

    #1078359

    Sorry, "SET CONTEXT_INFO {binary value}" works in SQL 2000 but the CONTEXT_INFO() function does not exist. You have to use

    SELECT context_info

    FROM master.dbo.sysprocesses

    WHERE spid = @@SPID

    Since it still requires a SELECT, I don't know whether it would result in a major performance improvement or not.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply