August 23, 2021 at 11:36 pm
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
August 25, 2021 at 12:25 am
Do you need to see other pieces of the code?
Thanks
August 25, 2021 at 1:38 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2021 at 7:09 am
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.
August 25, 2021 at 11:47 am
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
August 27, 2021 at 7:15 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2021 at 12:02 pm
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
September 3, 2021 at 4:08 pm
Any update Scott?
Thx.
September 20, 2021 at 6:43 pm
Scott have you had any chance to get back to this?
Thanks.
October 12, 2021 at 8:29 pm
and what progresses have you done over the last 2 month on rewriting it yourself based on all pointers given here?
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply