Monday, September 1, 2008

Computed column is PERSISTED?

Hi everybody.
I'd like to share with you how important to define a computed column to be PESRISTED.
As you know from the BOL
/*
For columns specified as PERSISTED, the SQL Server 2005 Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise.
*/
I visited our client two days ago who has been experienced with performance issue for one of their very important query.One big table containes a computed column which SELECT statement is using for to return to the client. We have seen very high number of logical reads and TWO computer scalar iterators. For an obvious reason we define the computed column as PESRISTED and performance was increased dramatically.Moreover, create an index on computed column and see how perfromance will be increased more..


See demo script to see how it is affected.

CREATE TABLE t(c INT NOT NULL identity(1,1) PRIMARY KEY,
c1 AS '00'+cast(c AS VARCHAR(100)))

SET NOCOUNT ON
INSERT INTO t DEFAULT VALUES
GO 100000


SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT c1 FROM t
--Table 't'. Scan count 1, logical reads 1250, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]='00'+CONVERT(varchar(100),[master].[dbo].[t].[c],0)))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))



SET STATISTICS IO ON
SET STATISTICS PROFILE ON

SELECT c1 FROM t
--Table 't'. Scan count 0, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--select c1 from t
|--Compute Scalar(DEFINE:([master].[dbo].[t].[c1]=[master].[dbo].[t].[c1]))
|--Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__1446FBA6]))

DROP TABLE t

4 comments:

haiyan said...

Delivering a new dimension for hardcore Maple Story players with the addition of a fourth job and some free maple mesos, this is launched today. Maple Story players will have reached a high enough level to achieve the 4th Job or some necessary maplestory mesos, which begins this week. Many people can cost their own cheap mesos to gain themed dessert and costumes among their many posts. The addition of the 4th Job provides further depth to an already intricate and complex maple story mesos in game. At Leafre, players can find new upgrades for some of the games more powerful weapons can receive from mesos and also party with other elite players.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!