Cursor replacement suggestions

  • Error-Log process

    Thanks Scott.

    ALTER PROCEDURE [dbo].[Write_Error_Log]
    -- Input parameters.
    @Type nvarchar(50) = N'ERROR',
    @Description nvarchar(MAX) = NULL,
    @Procedure nvarchar(50) = NULL,
    @Code int = NULL,

    -- Output parameters.
    @Result nvarchar(MAX) = NULL OUT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    BEGIN TRY
    -- Internal Variables.
    DECLARE @Error AS int = 0
    DECLARE @DateTime datetime = SYSDATETIME()
    DECLARE @User nvarchar(50) = SUSER_SNAME()
    DECLARE @Session nvarchar(50) = @@SPID
    DECLARE @Workstation nvarchar(50) = HOST_NAME()
    DECLARE @Application nvarchar(100) = APP_NAME()
    DECLARE @Severity int = ERROR_SEVERITY()
    DECLARE @State int = ERROR_STATE()
    DECLARE @Line int = ERROR_LINE()

    -- Comment this section to log informational messages as well.
    --IF @Type = 'INFORMATION'
    -- BEGIN
    --GOTO Check_For_Errors
    -- END

    -- Get the error message from ERROR_MESSAGE function if blank.
    IF ISNULL(@Description,'') = ''
    BEGIN
    SET @Description = ISNULL(ERROR_MESSAGE(),'UNKNOWN')
    END

    -- Get the error procedure from ERROR_PROCEDURE function if blank.
    IF ISNULL(@Procedure,'') = ''
    BEGIN
    SET @Procedure = ISNULL(ERROR_PROCEDURE(),'UNKNOWN')
    END

    -- Get the error code from ERROR_NUMBER function if blank.
    IF ISNULL(@Code,'') = ''
    BEGIN
    SET @Code = ERROR_NUMBER()

    -- Get the error code from description if blank.
    IF ISNULL(@Code,'') = ''
    BEGIN
    SET @Code = dbo.ParseText(@Description,'ERROR CODE:',2)

    -- Set the error code to 0 if blank.
    IF ISNULL(@Code,'') = ''
    BEGIN
    SET @Code = 0
    END
    END
    END

    -- Add error log record.
    INSERT INTO Error_Log (Error_DateTime,Error_Type,Error_Description,Error_User,Error_Session,[Error_Procedure],
    Error_Workstation,Error_Application,[Error_Severity],[Error_State],[Error_Line],Error_Code)
    VALUES (@DateTime,@Type,@Description,@User,@Session,@Procedure,@Workstation,@Application,@Severity,@State,@Line,@Code)

    Check_For_Errors:
    -- Check for errors occurred during procedure.
    IF @Error = 0
    -- When no errors are generated during procedure, set result to SUCCESS.
    BEGIN
    SET @Result = 'SUCCESS'
    END
    ELSE
    -- When errors are generated during procedure, set result to FAIL.
    BEGIN
    SET @Result = 'FAIL'
    END
    END TRY

    BEGIN CATCH
    SET @Result = 'ERROR - ' + CAST(ERROR_NUMBER() AS nvarchar(MAX)) + ', '
    SET @Result += 'MESSAGE - ' + CAST(ERROR_MESSAGE() AS nvarchar(MAX)) + ', '
    SET @Result += 'SEVERITY - ' + CAST(ERROR_SEVERITY() AS nvarchar(MAX)) + ', '
    SET @Result += 'STATE - ' + CAST(ERROR_STATE() AS nvarchar(MAX)) + ', '
    SET @Result += 'LINE - ' + CAST(ERROR_LINE() AS nvarchar(MAX)) + ', '
    SET @Result += 'PROCEDURE - ' + CAST(ERROR_PROCEDURE() AS nvarchar(MAX))
    SET @Error = ERROR_NUMBER()
    END CATCH

    Exit_Procedure:
    -- Display result and return error code.
    IF ISNULL(@Result,'SUCCESS') <> 'SUCCESS'
    BEGIN
    SELECT @Result
    END

    RETURN @Error
    END

     

     

  • Do you need to see other pieces of the code?

    Thanks

  • Sorry, no, I just need to get time to get back to it.  I should be able to do that tomorrow.

    We have to reduce the overhead in the Write proc as well, even with having to lookup the strings to get the encoded equivalent.  That will make sense once we look at possible new code.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Bruin wrote:

    Do you need to see other pieces of the code?

    Thanks

    I find this a bit amusing - you ask for help, we give you some and we point you to what you need to do to rewrite the code, yet we see no attempt by yourself to rewrite it and it seems you just trying to get other folks to completely rewrite it for you (for free).

    and unless Scott is willing to do the rewrite he will only present you with a solution to improve it up to a point instead of fixing the issue at hand.

    with the information supplied so far you should have already started to rewrite your code - and if you do do that you would more than likely find further help here to solve the particular issues you will find on such rewrite.

    but you need to think sets instead of rows - and that seems to be one of your main problems - and until you understand sets you will have code like this that will never really scale to any volume.

  • It's not that I'm unwilling to do a rewrite, it's I just like to make what is running today more efficient. I have already begun the process

    to tear apart the code and try and make it more set based. I appreciate Scott's input  on what is currently running and how it could be tuned. I responded to your Post of a suggestion you had but never heard back from the comments I made.

    I'm trying to understand the logic behind this, but it was coded over 7 years ago, and no one if left that understands the process so that is where I'm at trying not to break something that is working, maybe not optimal but taking small steps.

    Thanks

  • To allow me to write full code, would you provide the full DDL for the Error_Log table?  The big gain will come from encoding strings as numbers using a conversion / lookup table.

    Are you on Enterprise Edition?  If so, we can specify ROW compression which will also help performance.  And we can test whether PAGE compression would be worthwhile or not: most likely not once we remove the strings, but it won't hurt to check.  If you're on Standard Edition 2012, compression is not available, so we'll skip that part.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I'm on Standard 2012 edition.

    Thanks for your efforts and input.

    CREATE TABLE [dbo].[Error_Log](
    [Error_Log_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Error_DateTime] [datetime] NULL,
    [Error_Type] [nvarchar](50) NULL,
    [Error_Description] [nvarchar](max) NOT NULL,
    [Error_User] [nvarchar](50) NULL,
    [Error_Session] [nvarchar](50) NULL,
    [Error_Procedure] [nvarchar](50) NOT NULL,
    [Error_Workstation] [nvarchar](50) NULL,
    [Error_Application] [nvarchar](100) NULL,
    [Error_Severity] [smallint] NULL,
    [Error_State] [smallint] NULL,
    [Error_Line] [int] NULL,
    [Error_Code] [int] NULL,
    CONSTRAINT [PK_Error_Log] PRIMARY KEY CLUSTERED
    (
    [Error_Log_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
  • Any update Scott?

     

    Thx.

  • @scott   do you need any more info ?

     

    Thx.

Viewing 9 posts - 46 through 54 (of 54 total)

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