Temporary Tables performance issue

  • Hi All,

    One of my colleagues is having the following situation and i think it's a design problem but need you guys to confirm and tell me what are possible problems that could occur.

    To save some audit trail for system events they have triggers on all tables on (INSERT/UPDATE/DELETE) operations.

    Those triggers insert the new added or updated record in a temporary table then call a stored procedure that then reads reads from this temporary table , do some parsing on record then insert the result in an audit trail table.

    I need to know please the effect of that on :

    1. Locking tempdb (is whole db locked) meaning no one can access table indexes.

    2. With multi user enviroment,many users inserting and updating records in different table , will they wait for each other to finish although different tables are being updated.

    3. Will that consume much memory?.

    4. Should the temporary table be dropped in the Stored procedure that parses on records or no need for that?.

    Those are the concerns i have so far about this design but if you have more please let me know.

    Thanks in advance waiting eagerly for you replies :-).

    Nader

  • nadersam (10/3/2010)


    1. Locking tempdb (is whole db locked) meaning no one can access table indexes.

    NO.Locking mechanism work on row level --> page level ---> then table level.no on database level

    nadersam (10/3/2010)


    2. With multi user enviroment,many users inserting and updating records in different table , will they wait for each other to finish although different tables are being updated.

    True thats the drawback of trigger from performance point of view .So better/intelligently design logic of triggers

    nadersam (10/3/2010)


    3. Will that consume much memory?.

    i dont think..much but the magic tables surely take tempdb space or memory.

    nadersam (10/3/2010)


    4. Should the temporary table be dropped in the Stored procedure that parses on records or no need for that?.

    Why ? and then how you will handle the intermediate data ?

    i still didnt get your exact problem ? can you be more specific ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Thanks for your reply.

    1. Locking tempdb (is whole db locked) meaning no one can access table indexes.

    NO.Locking mechanism work on row level --> page level ---> then table level.no on database level

    Not sure i understand that but do you mean the lock is only on affected table?

    Also i tried doing the following run an insert statement and put wait for delay '00:01' in it when temporary table is still in scope, then from management studio just select properties of tempdb, i got error time out, once the insert statement finished , i could access tempdb again.

    2. My question is more about temporary table created inside trigger, how will that affect in mutli user environment.

    4.I am asking should i drop the temporary table in the stored procedure after it finishes using it or just leave SQL handle that?.

    Thanks again waiting for you feedback.

    Nader

  • nadersam (10/4/2010)


    NO.Locking mechanism work on row level --> page level ---> then table level.no on database level

    this is lock escalation see these links

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx

    http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx

    nadersam (10/4/2010)


    Also i tried doing the following run an insert statement and put wait for delay '00:01' in it when temporary table is still in scope, then from management studio just select properties of tempdb, i got error time out, once the insert statement finished , i could access tempdb again.

    Post the error you got.

    nadersam (10/4/2010)


    2. My question is more about temporary table created inside trigger, how will that affect in mutli user environment.

    I really didnt get this , if you talk about blocking because of one and another then dont worry . these temp tables work/created mutually exclusive to each other.and if you are talking about usage of temp table as performance point of view then it will make impact but that depends on the trigger design and amount of DML operation happening.

    nadersam (10/4/2010)


    4.I am asking should i drop the temporary table in the stored procedure after it finishes using it or just leave SQL handle that?.

    Sql server it self handles it. but you can also drop them explicitly.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • The error is:

    Lock request time out exceeded error:1222

    Thanks

  • nadersam (10/4/2010)


    The error is:

    Lock request time out exceeded error:1222

    Thanks

    I think this article can help you.

    http://support.microsoft.com/kb/308518/en-us

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Thanks for the link.

    But does that mean that SQL server will not be able to access indexes and temporary tables , till the lock caused by the creation of the temporary table is freed?

  • I would like to see the trigger definition now

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Here is the trigger code

    Create TRIGGER [TR_Audit_Trail_Insert_TableName] ON TableName FOR INSERT AS

    SET NOCOUNT ON;

    SELECT * INTO #ins FROM INSERTED;

    EXEC SP_Audit_Trail_Transaction_Insert 'TableName';

    and this is the SP called from within the trigger

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [SP_Audit_Trail_Transaction_Insert] (@Table_Name VARCHAR(128)) AS

    DECLARE @Field_Position AS INT;

    DECLARE @Field_Name AS VARCHAR(128);

    DECLARE @Field_Type AS VARCHAR(128);

    DECLARE @Conversion AS VARCHAR(128);

    SELECT @Field_Position = 0;

    WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name) )

    BEGIN

    SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;

    SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position;

    IF ( @Field_Name NOT IN (N'ID', N'Performed_By', N'Performed_In', N'Performed_At') )

    BEGIN

    --waitfor delay '00:01'

    IF ( @Field_Type IN (N'DATETIME') )

    SET @Conversion = ', 121';

    ELSE

    SET @Conversion = '';

    EXEC (N'INSERT INTO [Audit_Trail_Transaction] ([Table_Name], [Record_ID], [Field_Name], [Operation], [Performed_By], [Performed_In], [Performed_At], [Old_Value], [New_Value])

    SELECT ''' + @Table_Name + ''', i.[ID], ''' + @Field_Name + ''',

    ''I'',i.[Performed_By],i.[Performed_In],i.[Performed_At],

    NULL,CONVERT(VARCHAR(5000), i.[' + @Field_Name + ']' + @Conversion + ')

    FROM

    #ins i;');

    --WAITFOR DELAY '00:01'

    END

    END

    Thanks

    Nader

  • Two things you can try here

    1) Don't create # table on fly use create approach.

    2) SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;

    SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position; use WITH (NOLOCK) option.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • thanks for ur help

    thats what i did pls tell me if am wrong

    for trigger

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [TR_Audit_Trail_Insert_Accounting_Period] ON [Accounting_Period] FOR INSERT AS

    SET NOCOUNT ON;

    CREATE TABLE #ins([ID] [int] ,

    [Name] [varchar](100) NOT NULL,[Date_From] [datetime] NOT NULL,[Date_To] [datetime] NOT NULL,

    [Performed_By] [varchar](400) NOT NULL,[Performed_In] [varchar](200) NOT NULL,[Performed_At] [datetime] NOT NULL)

    insert INTO #ins select * FROM INSERTED with (nolock);

    EXEC SP_Audit_Trail_Transaction_Insert 'TableName;

    for SP

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [SP_Audit_Trail_Transaction_Insert] (@Table_Name VARCHAR(128)) AS

    DECLARE @Field_Position AS INT;

    DECLARE @Field_Name AS VARCHAR(128);

    DECLARE @Field_Type AS VARCHAR(128);

    DECLARE @Conversion AS VARCHAR(128);

    SELECT @Field_Position = 0;

    WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )

    BEGIN

    SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;

    SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position;

    IF ( @Field_Name NOT IN (N'ID', N'Performed_By', N'Performed_In', N'Performed_At') )

    BEGIN

    waitfor delay '00:01'

    IF ( @Field_Type IN (N'DATETIME') )

    SET @Conversion = ', 121';

    ELSE

    SET @Conversion = '';

    EXEC (N'INSERT INTO [Audit_Trail_Transaction] ([Table_Name], [Record_ID], [Field_Name], [Operation], [Performed_By], [Performed_In], [Performed_At], [Old_Value], [New_Value])

    SELECT

    ''' + @Table_Name + ''',

    i.[ID],

    ''' + @Field_Name + ''',

    ''I'',

    i.[Performed_By],

    i.[Performed_In],

    i.[Performed_At],

    NULL,

    CONVERT(VARCHAR(5000), i.[' + @Field_Name + ']' + @Conversion + ')

    FROM

    #ins with (nolock) i;');

    --WAITFOR DELAY '00:01'

    END

    END

    i applied those changes but still tempdb is locked till i finish insertion.

    Thanks

  • Although your code is nice and generic and can be used by multiple tables with different columns, I think this is also the cause of your issues.

    If I'm reading it right then

    you insert a row into a table which has say 10 columns

    the trigger fires, and then copies this row into a temp table

    you then call your generic stored procedure

    this stored procedure then loops around the each of the columns in the table.

    for each column it then inserts an entry into your audit table.

    So for a single insert statement you have

    1. the insert into the real table

    2. the insert into the temp table

    3. 10 reads from the system tables to get the column names

    4. 10 inserts into the audit table.

    this seems quite an overhead.

  • Just spotted one possible improvement

    this sql

    SELECT @Field_Position = 0;

    WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )

    could be changed to

    SELECT @Field_Position = 0;

    SELECT @Total_Number_Of_Fields = SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name)

    WHILE ( @Field_Position < @Total_Number_Of_Fields )

    regards

    David

  • Man this is amuch better solution , thank u very much.

    Nader

  • nadersam (10/5/2010)


    Man this is amuch better solution , thank u very much.

    Nader

    Did you see some improvement with it ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 1 through 15 (of 32 total)

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