Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 301
SQL Server 300
Administration 251
DBA 240
T-SQL 231
#TSQL 229
Development 224
Tips 213
Guidance 146
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0159-SQL Server-Row Constructors-Triggers process the entire batch at once

May 10 2012 12:00AM by Nakul Vachhrajani   

Almost every product comes with a set of predefined system default data, which may or may not be editable by the user. When writing about returning result sets from  triggers, I realized that there are very few product teams who use row constructors (introduced in SQL Server 2008) to populate their default data.

What these teams fail to realize is that many a times, the conventional approach may have a negative impact on the time it takes to insert the data and also reduces the overall efficiency of the entire data load operation. Let’s see a demo.

The Test Setup

To demonstrate the point, I will create a small table in the tempdb database with an INSERT trigger on it that would return us the inserted values from the INSERTED view.

USE tempdb
GO
CREATE TABLE dbo.RowConstructorDemo (RowId INT IDENTITY(1,1),
                                     RowValue VARCHAR(30),
                                     RowDescription VARCHAR(100)
                                    )
GO

CREATE TRIGGER dbo.trig_RowConstructorDemo_Insert
ON dbo.RowConstructorDemo
FOR INSERT
AS
BEGIN
  --Some business logic here
  SELECT ROW_NUMBER() OVER (ORDER BY inserted.RowId) AS [RowNumber],
         inserted.RowId,
         inserted.RowValue,
         inserted.RowDescription
  FROM inserted
END
GO

The Conventional Method

Now, let us try to insert a couple of rows using the conventional style of data insertion, i.e. row-by-row approach.

USE tempdb
GO
--Conventional approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'OS1','Microsoft Windows 2003 Operating System'
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'DB1','Microsoft SQL Server 2005'
GO

The INSERT trigger that we have defined on our test table returns us two (2) result sets:

image

Using Row Constructors

If we use row constructors to insert data, we see that the INSERT trigger returns us only one (1) result set. This is because it is one INSERT statement, and therefore all records are inserted in a single batch.

USE tempdb
GO
--Row Constructor approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription)
VALUES ('OS2','Microsoft Windows 2008 R2 Operating System'),
       ('DB2','Microsoft SQL Server 2012')
GO

image

Comparing Conventional Method v/s Row Constructors

We have 3 INSERT statements as part of the test. They are simple INSERT statements and therefore, when both approaches are submitted together as a single batch, each INSERT statement contributes to approximately 33% of the total cost.

The Conventional approach has 2 INSERT statements, and therefore, the conventional approach takes up 33 * 2 = 66% of the total cost.

On the other hand, the row constructor method has only one INSERT statement, and therefore takes only 33 * 1 = 33% of the total cost.

image

Please note that the overall cost & performance analysis is dependent upon multiple factors, but overall, row constructors have always been faster than individual inserts for me. They have also been performant and efficient because the subsequent triggers work on batches of rows as opposed to a single row at a time.

Conclusion

If your application/product uses pre-defined static data, I trust that the above post will at the very least inspire you to revisit the individual INSERTs and replace them with row constructors.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, Administration, Development, DBA, Tips, #TSQL, T-SQL


Nakul Vachhrajani
4 · 36% · 11543
9
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

3  Comments  

  • Hello Nakul,

    thank you for sharing this interesting fact which was new to me.

    But one more question: is there a row limit (the amount of rows to be inserted) when using row constructors?

    Regards,

    Michael

    commented on May 10 2012 2:42AM
    Michael Mikic
    594 · 0% · 61
  • @Michael: It is maximum 1000 row values.

    commented on May 10 2012 5:52AM
    Hardik Doshi
    20 · 9% · 2845
  • @Michael: Good question, thank-you! @Hardik: Thank-you for the answer!

    commented on May 11 2012 3:12AM
    Nakul Vachhrajani
    4 · 36% · 11543

Your Comment


Sign Up or Login to post a comment.

"#0159-SQL Server-Row Constructors-Triggers process the entire batch at once" rated 5 out of 5 by 9 readers
#0159-SQL Server-Row Constructors-Triggers process the entire batch at once , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]