SQL server-focused computed column or computed column persisted query performance (22)

Objective

In the previous section we explained in detail the calculation column and the calculation column persistence problem, this section we still as explained above to look at the two query performance problem, short content, in-depth understanding, always to review the basics.

Persisted computed columns perform better than non-persisted computed columns

We started by creating two identical tables and inserting the data for comparison, we re-created computed columns and persisted non-computed columns for computed columns.

CREATE TABLE [dbo]. [Computecolumncompare] (ID int,firstname VARCHAR (+),LastName CHAR (8000)) GO

 -   row_number () over [order by A.name] RowID,'Bob', case when Row_number () Over (ORDER by A.name)%21'Smith ' Brown ' endfrom sys.all_objects aCROSS JOIN sys.all_objects BGO

Create a computed column on a computecolumn table

Use tsql2012goalter TABLE dbo. Computecolumn Addfullname as POWER (LEN (left (FirstName+cast (ID as VARCHAR)),312  ) GO

Create a computed persistence column on a computecolumncompare table

Use tsql2012goalter TABLE dbo. Computecolumncompare addfullname_p as POWER (LEN (left (FirstName+cast (ID as VARCHAR)),3  A ) Persistedgo

Now let's run two tables query for computed and computed column persisted columns

531441531441GO

At this point the cost is the same, but the non-persisted column has a compute scalar operation, mainly because it calculates that the value is at run time, we take a look at the operation Chen Ben.

We see that there is a bit of difference in performance, so we know that it is better to use persistence to calculate performance ahead of time if the computational operations are more complex than non-persisted columns. Is the performance of persisted columns better than the performance of persisted columns in all cases? Keep looking down.

Non-persisted computed columns perform better than persisted computed columns

Let's create a test table and insert 10,000 data to compare.

Use tsql2012gocreate TABLE [dbo]. [Computecolumn] (ID int,firstname VARCHAR (+),LastName CHAR ()) Gocreate TABLE [dbo]. [Computecolumncompare] (ID int,firstname VARCHAR (+),LastName CHAR ()) GO

Use tsql2012goinsert into [dbo]. [Computecolumn] (Id,firstname,lastname) SELECT TOP10000row_number () over (ORDER by A.name) RowID,'Bob', Case if Row_number () over (ORDER by A.name)%2=1Then'Smith'ELSE'Brown'endfrom sys.all_objects ACROSS JOIN sys.all_objects BGO
INSERT into [dbo]. [Computecolumncompare] (Id,firstname,lastname) SELECT TOP10000row_number () over (ORDER by A.name) RowID,'Bob', Case if Row_number () over (ORDER by A.name)%2=1Then'Smith'ELSE'Brown'endfrom sys.all_objects aCROSS JOIN sys.all_objects BGO

Next, create a persisted computed column and a non-persisted computed column on both tables

Use tsql2012goalter TABLE dbo. Computecolumn Addfullname as (FirstName+"+LastName) goalter TABLE dbo. Computecolumncompare addfullname_p as (FirstName+"+LastName) Persistedgo

Finally we look at the query plan results

' Bob Smith '  'Bob Smith'GO

Here we find that the non-persistent computed column performance is better than the persisted computed column performance, and compared to the above, I have made it clear that the size of the tag definition column and how much of the inserted row is different, so for both persisted and non-persisted columns there is no absolute performance for who is good, and when we want to see both who performance better, We may need to consider defining the size of the column, the number of data rows, and so on. Let's look at the final scenario where you create an index on a computed column.

Non-persisted computed columns improve query performance

We continue to create the test table

Use tsql2012gocreate TABLE [dbo]. [Computecolumn] (ID int,firstname varchar (+), LastName varchar) Gocreate TABLE [Computecolumncompare] (ID int,firstname varchar), LastName varchar (100  )) GO

Use tsql2012goinsert into [dbo]. [Computecolumn] (Id,firstname,lastname) SELECT TOP10000row_number () over (ORDER by A.name) RowID,'Bob', Case if Row_number () over (ORDER by A.name)%2=1Then'Smith'ELSE'Brown'endfrom sys.all_objects ACROSS JOIN sys.all_objects bgoinsert into [dbo]. [Computecolumncompare] (Id,firstname,lastname) SELECT TOP10000row_number () over (ORDER by A.name) RowID,'Bob', Case if Row_number () over (ORDER by A.name)%2=1Then'Smith'ELSE'Brown'endfrom sys.all_objects aCROSS JOIN sys.all_objects BGO

Create a computed column on the Computecolumn table and create a nonclustered index

ALTER TABLE dbo. Computecolumn Addfullname as (FirstName+"+LastName) gocreate nonclustered INDEX ix_ Compcol_citytrimon dbo. Computecolumn (FullName) GO

Create a computed column on a computecolumncompare table

ALTER TABLE dbo.computecolumncompare addfullname_p as (FirstName+"+LastName) GO

Last query two tables look at the results of the query plan

' Bob Smith '  'Bob Smith'GO

From the above we know that creating an index on a computed column can be a good way to improve query performance, of course, just to return the computed column, if the return of other columns may lead to key Lookup, but from another point of view can improve the query performance, in order to solve the key Lookup problem to build too many indexes are also problematic, specific analysis of the situation. There is no comparison of the performance of the persisted computed columns and the non-persisted computed columns, the two are actually the same, and there is no comparison, just the use of persistence in data storage is different. Reference: "http://blog.sqlauthority.com/2010/08/03/sql-server-computed-column-persisted-and-performance/"

Summarize

We're done here. For computed columns and for the analysis of the concept and performance of the computed column persistence, let's look at the knowledge of the other queries in the next section, and then go to the table expression learning, short content, in-depth understanding, and we'll see you next.

SQL server-focused computed column or computed column persisted query performance (22)