Rewriting an update statement without using a cursor
-
I wrote the following SQL, but I know there has to be a better way to do what I'm trying to accomplish. Basically, I've created a new column in the Drivers table that I need to update only if the driver's license state is different from the one corresponding row in the Policy table. I've been told how evil cursors are in terms of efficiency, but I'm really wondering if I have to use one in this case since I haven't had any luck writing an update that also has a join in it.
The relevant data in the tables looks like this:
Drivers
Id char(20)
DriverNumber smallint
State char(2)
LicenseStateDifferent char(1)
Policy
Id char(20)
State char(2)
INSERT INTO Policy (Id, State) VALUES ( '1', 'MI' )
INSERT INTO Policy (Id, State) VALUES ( '2', 'IL' )
INSERT INTO Drivers (Id, DriverNumber, State) VALUES ( '1', '1', 'MI' )
INSERT INTO Drivers (Id, DriverNumber, State) VALUES ( '1', '2', 'OH' )
INSERT INTO Drivers (Id, DriverNumber, State) VALUES ( '1', '3', '' )
INSERT INTO Drivers (Id, DriverNumber, State) VALUES ( '2', '1', 'IL' )
The only row in Drivers that should get updated is Id = 1, DriverNumber = 2. The primary key on Policy is Id, and the primary key on Drivers is Id and DriverNumber. My query currently looks like this:
DECLARE @Id char(20)
DECLARE @DriverNumber smallint
DECLARE temp_cursor CURSOR FOR
SELECT Drivers.Id
, Drivers.DriverNumber
FROM Drivers
LEFT JOIN Policy
ON Policy.Id = Drivers.Id
WHERE RTrim ( IsNull ( Drivers.State , '' ) ) <> ''
AND Policy.State <> Drivers.State
OPEN temp_cursor
FETCH FROM temp_cursor INTO @Id, @DriverNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE Drivers
SET LicenseStateDifferent = 'Y'
WHERE Drivers.Id = @Id
AND Drivers.DriverNumber = @DriverNumber
FETCH FROM temp_cursor INTO @Id, @DriverNumber
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
Any help on eliminating the cursor would be appreciated.
-
UPDATE D
SET LicenseStateDifferent =
CASE WHEN P.State IS NULL THEN 'Y' ELSE 'N' END
FROM Drivers As D
LEFT JOIN Policy As P
ON ( D.Id = P.Id AND
(D.State = P.State OR D.State = '') )
-
It might be better just to write a view for this - especially if drivers might change policy or state. That way you know the denormalised values always reflect the underlying data. You can index the view if performance is an issue.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Shouldn't need to index the view if the underlying tables are properly indexed... indexing a view also requires the view to be schema bound and that all UDF's addressed by the view also be created with the schema binding option. On top of that, I think the restrictions for an indexed view just aren't worth it... the query that PW posted supposedly (I haven't tried it) will not work in an indexed view because it contains an outer join. From BOL...
- The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
- The select list cannot use the * or table_name.* syntax to specify columns. Column names must be explicitly stated.
- A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, this select list is invalid:
SELECT ColumnA, ColumnB, ColumnAThese select lists are valid:
SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColBSELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB - A derived table.
- Rowset functions.
- UNION operator.
- Subqueries.
- Outer or self joins.
- TOP clause.
- ORDER BY clause.
- DISTINCT keyword.
- COUNT(*) (COUNT_BIG(*) is allowed.)
- The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions.
Complex aggregate function Substitute simple aggregate functions AVG(X) SUM(X), COUNT_BIG(X) STDEV(X) SUM(X), COUNT_BIG(X), SUM(X**2) STDEVP(X) SUM(X), COUNT_BIG(X), SUM(X**2) VAR(X) SUM(X), COUNT_BIG(X), SUM(X**2) VARP(X) SUM(X), COUNT_BIG(X), SUM(X**2) For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).
- A SUM function that references a nullable expression.
- The full-text predicates CONTAINS or FREETEXT.
- COMPUTE or COMPUTE BY clause.
- If GROUP BY is not specified, the view select list cannot contain aggregate expressions.
- If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
- A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
-
Considering this is going to be a one-time conversion once we add the column to the table, I don't really see the need for a view. The data itself can change, but we take care of data integrity programmatically in our web application. I have yet to try the query posted here, too, but I'll go test that out right now.
-
Looks like the only thing I need to do differently on the query is only update the rows that will have LicenseStateDifferent = 'Y'. The field is originally defaulted to 'N' since this is the most common value. I don't want to update every row in the table since there are around 300,000 rows or something like that. I figured I would default it to 'N' for everyone and then only update the ones that are 'Y' with this conversion. If the state in Drivers is blank, we're leaving the value to 'N' because it means the driver hasn't selected their state yet, and it's most likely to be 'N' but when they do select their state, the web app will take care of the data integrity part. Thanks for everyone's help so far! I might be able to figure it out from here on my own, but I wouldn't turn down a little extra help.
-
Never mind. I did get it on my own.
The following query is the one I'll be using:UPDATE D
SET LicenseStateDifferent = 'Y'
FROM Drivers As D
LEFT JOIN Policy As P
ON D.Id = P.Id
WHERE D.State <> P.State
AND RTrim ( IsNull ( D.State, '' ) ) <> ''
-
Nearly there, but you don't and never did need the left join (Jeff!). Your initialisation query may be once only, but the minatenance of the data isn't once only, is it. The web app has to do it. I don't like web apps maintaining my data.
Jeff:
>Shouldn't need to index the view if the underlying tables are properly indexed
Well that isn't generally true, is it. An example - indexing highly aggregated data from a huge table. Note I specified 'if performance is an issue'. It probably won't be if the tables are indexed, no. I generally mention this possibility to forestall specious objections to eliminating denormalised data, based on performance/contention.
>the restrictions for an indexed view just aren't worth it
Which of the disallowed features do you wish you could use? Select *? Float data? Perhaps preconcatenating data with a UNION? Surely not derived tables, since you can use a view instead, which promotes code reuse hence consistency. Rowset functions, perhaps? Nah. And I can't imagine you're bothered by being unable to enforce an ephemeral order on a whole view's worth of data (without necessarily indexing the ordering columns), when you can always order in the outermost SELECT. Maybe it's the fact that some aggregates have to be stored in decomposed form, even though the aggregates will still be usable from outside the view? Seems a bit picky. Try averaging some averages one day and see how you get on...
No, it must be subqueries and left joins. Neither are needed here, of course, or in many other expensive queries. In those cases, the restrictions are neither worth it nor not worth it. Worth what?
There are other reasons wieghing against using indexed views, e.g. maintenance overhead in a highly volatile OLTP environment, but in a given case, the restrictions will usually either apply, and rule it out altogether, or not apply, so having no relevance. It's not a question of being 'worth it'.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
>Shouldn't need to index the view if the underlying tables are properly indexed
Well that isn't generally true, is it.
Yes, I've found that it is generally true especially on aggragated data from a huge table if the aggragates
and indexes are well formed. Check it out (single table test, multiple table join works in a similar fashion)...
--===== Create and populate a 4 million row test table to test with.
SELECT TOP 4000000
IDENTITY(INT,1,1) AS RowNum,
CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT) AS UserID,
CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) AS SomeValue,
'A column for kicks' AS Kicks,
'Still another column just for proofing' AS StillAnother,
CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY) AS SomeNumber1,
CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY) AS SomeNumber2,
CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY) AS SomeNumber3,
CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate --(>=01/01/2000 <01/01/2010)
INTO dbo.BigTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
GO
--===== Add a calculated column to strip the time from the date.
ALTER TABLE dbo.BigTest
ADD AMonth AS YEAR(ADate)*100+MONTH(ADate)
GO
--===== Create a test view
CREATE VIEW vBigTest
AS
SELECT SomeValue, AMonth,
CASE
WHEN LEFT(SomeValue,1)=RIGHT(SomeValue,1)
THEN AVG(SomeNumber1+SomeNumber2+SomeNumber3)
WHEN LEFT(SomeValue,1)<RIGHT(SomeValue,1)
THEN AVG(SomeNumber1+SomeNumber2)
WHEN LEFT(SomeValue,1)>RIGHT(SomeValue,1)
THEN AVG(SomeNumber2+SomeNumber3)
END AS AvgSomeNumber
FROM dbo.BigTest
GROUP BY SomeValue,AMonth
--===== Show the execution plan without and with indexes including a primary key
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM vBigTest
GO
SET SHOWPLAN_TEXT OFF
GO
ALTER TABLE dbo.BigTest ADD PRIMARY KEY CLUSTERED (RowNum)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM vBigTest
GO
SET SHOWPLAN_TEXT OFF
GO
CREATE INDEX BigTest2 ON dbo.BigTest(SomeValue, AMonth, SomeNumber1, SomeNumber2, SomeNumber3)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM vBigTest
GO
SET SHOWPLAN_TEXT OFF
GO
... produces... {edit... previously included result of a "cubed" view instead of simple "group by". Has been updated.}
(400000 row(s) affected)
StmtText ------------------------- SELECT * FROM vBigTest
(1 row(s) affected)
StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Compute Scalar(DEFINE: ([Expr1005]=If (substring(Convert([BigTest].[SomeValue]), 1, 1)=right(Convert([BigTest].[SomeValue]), 1)) then [Expr1002] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)<right(Convert([BigTest].[SomeValue]), 1)) then [Expr1003] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)>right(Convert([BigTest].[SomeValue]), 1)) then [Expr1004] else NULL)) |--Compute Scalar(DEFINE: ([Expr1002]=If ([Expr1024]=0) then NULL else ([Expr1025]/Convert([Expr1024])), [Expr1003]=If ([Expr1026]=0) then NULL else ([Expr1027]/Convert([Expr1026])), [Expr1004]=If ([Expr1028]=0) then NULL else ([Expr1029]/Convert([Expr1028])))) |--Hash Match(Aggregate, HASH: ([BigTest].[SomeValue], [BigTest].[AMonth]), RESIDUAL: ([BigTest].[SomeValue]=[BigTest].[SomeValue] AND [BigTest].[AMonth]=[BigTest].[AMonth]) DEFINE: ([Expr1024]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1025]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1026]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1027]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1028]=COUNT_BIG([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1029]=SUM([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]))) |--Compute Scalar(DEFINE: ([BigTest].[AMonth]=datepart(year, dbo.BigTest.[ADate])*100+datepart(month, dbo.BigTest.[ADate]))) |--Table Scan(OBJECT: ([BELUTION].[dbo].[BigTest]))
(5 row(s) affected)
StmtText ------------------------- SELECT * FROM vBigTest
(1 row(s) affected)
StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Compute Scalar(DEFINE: ([Expr1005]=If (substring(Convert([BigTest].[SomeValue]), 1, 1)=right(Convert([BigTest].[SomeValue]), 1)) then [Expr1002] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)<right(Convert([BigTest].[SomeValue]), 1)) then [Expr1003] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)>right(Convert([BigTest].[SomeValue]), 1)) then [Expr1004] else NULL)) |--Compute Scalar(DEFINE: ([Expr1002]=If ([Expr1024]=0) then NULL else ([Expr1025]/Convert([Expr1024])), [Expr1003]=If ([Expr1026]=0) then NULL else ([Expr1027]/Convert([Expr1026])), [Expr1004]=If ([Expr1028]=0) then NULL else ([Expr1029]/Convert([Expr1028])))) |--Hash Match(Aggregate, HASH: ([BigTest].[SomeValue], [BigTest].[AMonth]), RESIDUAL: ([BigTest].[SomeValue]=[BigTest].[SomeValue] AND [BigTest].[AMonth]=[BigTest].[AMonth]) DEFINE: ([Expr1024]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1025]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1026]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1027]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1028]=COUNT_BIG([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1029]=SUM([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]))) |--Compute Scalar(DEFINE: ([BigTest].[AMonth]=datepart(year, dbo.BigTest.[ADate])*100+datepart(month, dbo.BigTest.[ADate]))) |--Clustered Index Scan(OBJECT: ([BELUTION].[dbo].[BigTest].[PK__BigTest__0B9350C2]))
(5 row(s) affected)
StmtText ------------------------- SELECT * FROM vBigTest
(1 row(s) affected)
StmtText ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Compute Scalar(DEFINE: ([Expr1005]=If (substring(Convert([BigTest].[SomeValue]), 1, 1)=right(Convert([BigTest].[SomeValue]), 1)) then [Expr1002] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)<right(Convert([BigTest].[SomeValue]), 1)) then [Expr1003] else If (substring(Convert([BigTest].[SomeValue]), 1, 1)>right(Convert([BigTest].[SomeValue]), 1)) then [Expr1004] else NULL)) |--Compute Scalar(DEFINE: ([Expr1002]=If ([Expr1024]=0) then NULL else ([Expr1025]/Convert([Expr1024])), [Expr1003]=If ([Expr1026]=0) then NULL else ([Expr1027]/Convert([Expr1026])), [Expr1004]=If ([Expr1028]=0) then NULL else ([Expr1029]/Convert([Expr1028])))) |--Stream Aggregate(GROUP BY: ([BigTest].[SomeValue], [BigTest].[AMonth]) DEFINE: ([Expr1024]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1025]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1026]=COUNT_BIG([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1027]=SUM([BigTest].[SomeNumber1]+[BigTest].[SomeNumber2]), [Expr1028]=COUNT_BIG([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]), [Expr1029]=SUM([BigTest].[SomeNumber2]+[BigTest].[SomeNumber3]))) |--Index Scan(OBJECT: ([BELUTION].[dbo].[BigTest].[BigTest2]), ORDERED FORWARD)
(4 row(s) affected)
Try averaging some averages one day and see how you get on Don't need to try it... Proper use of ROLLUP and CUBE usually make that task pretty simple.
Which of the disallowed features do you wish you could use? These, Tim...
- A derived table.
- UNION operator.
- Subqueries.
- Outer or self joins.
- DISTINCT keyword.
- COUNT(*) (COUNT_BIG(*) is allowed.)
- The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions.
For example, an indexed view select list cannot contain the expression
AVG(SomeColumn). If the view select list contains the expressions
SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate
the average for a query that references the view and specifies AVG(SomeColumn).
- A SUM function that references a nullable expression.
- If GROUP BY is specified, the view select list must contain a COUNT_BIG(*)
expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
It's not a question of being 'worth it'. A bit semiotic, but you are correct... Considering that we've just proven that,
if properly formed, the indexes of the underlying tables are, in fact, used and
the miserable list of restrictions for indexed views, the most significant of
which are listed above, it's certainly not worth it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
- Here's an indexed view version.
- Performance stats are shown at the bottom for three tests, comparing use of base table indexes v use of view indexes.
[edit: revised and expanded, nothing essential changed.]
- --/*--session settings-------------
- set
numeric_roundabort off
- set ansi_nulls on
- set ansi_padding on
- set ansi_warnings on
- set arithabort on
- set concat_null_yields_null on
- set quoted_identifier on
- --*/--end session settings---------
- go--
- --
- /*----CLEANUP CODE---------------------------------------------------------
- drop view dbo.vBigTestA_C_FromTables
- go--
- drop view dbo.vBigTestA_C_FromViewIndex
- go--
- drop view dbo.vBigTestA_C_AGG
- go--
- drop table dbo.MoreData
- go--
- drop table dbo.BigTest
- go--
- --
- */----END CLEANUP CODE-----------------------------------------------------
- --
- --/*--DDL AND TEST DATA----------------------------------------------------
- create
table MoreData(RowNum int identity not null primary key clustered, SomeNumber3 money not null)
- go--
- --
- create
table dbo.BigTest
- (
Rownum int identity not null unique clustered, MoreDataRowNum int not null
- , Userid int not null, SomeValue varchar(2) not null
- , Kicks varchar(1000) null
- , EighthOfAPage varchar(1000) null
- , SomeNumber1 money not null, SomeNumber2 money not null
- , ADate datetime not null, AMonth as YEAR(ADate)*100+MONTH(ADate)
- )
- go--
- --
- --in the TOP expression, use the square root of the desired total number of records
- --so using 1415 results in 2,002,225 records in table BigTest
- insert
MoreData(SomeNumber3) select top 2000 rand(cast(newid() as varbinary))*100 SomeNumber3
- from
master.dbo.SysColumns sc1
- cross
join master.dbo.SysColumns sc2
- go--
- --
- declare
@monot_all bigint, @monot_1_26 bigint, @monot_1_1000 bigint
- select @monot_all = count_big(*)
- , @monot_1_26 = count_big(*)/26+1
- ,
@monot_1_1000 = count_big(*)/1000+1 from MoreData
- insert
dbo.BigTest(MoreDataRowNum,Userid,SomeValue,Kicks,EighthOfAPage,SomeNumber1,SomeNumber2,ADate)
- select
m.Rownum MoreDataRowNum
- ,rand(cast(newid() as varbinary))*50000+1 Userid
- ,CHAR(STR(m.Rownum/@monot_1_26+65))
- + CHAR(STR(m2.Rownum/@monot_1_26+65)) SomeValue
- ,'A column for kicks' Kicks
- ,'X' --replicate('X',1000) EighthOfAPage
- ,m.Rownum/@monot_1_1000 + (1.0 - 1.0/m.Rownum) SomeNumber1
- ,m2.Rownum/@monot_1_1000 + 55.0/m.Rownum SomeNumber2
- ,rand(cast(newid() as varbinary))*3653.0+36524.0 ADate --(>=01/01/2000 <01/01/2010)
- from MoreData m
- cross join MoreData m2
- go--
- --
- create
index IX_BigTest_covering on dbo.BigTest(Rownum, MoreDataRowNum, SomeValue, AMonth, SomeNumber1, SomeNumber2)
- go--
- --
- create
view dbo.vBigTestA_C_FromTables
- AS
- SELECT SomeValue, AMonth,
- CASE
- WHEN LEFT(SomeValue,1)=RIGHT(SomeValue,1)
- THEN AVG(SomeNumber1+SomeNumber2+SomeNumber3)
- WHEN LEFT(SomeValue,1)<RIGHT(SomeValue,1)
- THEN AVG(SomeNumber1+SomeNumber2)
- WHEN LEFT(SomeValue,1)>RIGHT(SomeValue,1)
- THEN AVG(SomeNumber2+SomeNumber3)
- END AS AvgSomeNumber,
- sum(SomeNumber1) SomeNumber1 ,sum(SomeNumber2) SomeNumber2, sum(SomeNumber3) SomeNumber3
- from dbo.BigTest b join dbo.MoreData m
- on b.MoreDataRowNum = m.RowNum
- where right(SomeValue,1) not between 'A' and 'C'
- group
by SomeValue,AMonth
- go--
- --
- create
view dbo.vBigTestA_C_AGG
- with
schemabinding
- as
- select AMonth
- ,left(
b.SomeValue,1) SomeValue_LEFT1
- ,right(
b.SomeValue,1) SomeValue_RIGHT1
- ,
sum(b.SomeNumber1) SomeNumber1_SUM
- ,
sum(b.SomeNumber2) SomeNumber2_SUM
- ,
sum(m.SomeNumber3) SomeNumber3_SUM
- ,
count_big(SomeValue) _COUNT
- from
dbo.BigTest b
- join
dbo.MoreData m
- on b.MoreDataRowNum = m.Rownum
- where
right(SomeValue,1) not between 'A' and 'C'
- group
by AMonth,left(SomeValue,1),right(SomeValue,1)
- go--
- --
- create
unique clustered index IXC_vBigTestA_C_AGG_covering on vBigTestA_C_AGG(SomeValue_LEFT1, SomeValue_RIGHT1, AMonth)
- go--
- --
- create
view vBigTestA_C_FromViewIndex
- as
- SELECT SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue, AMonth,
- case when SomeValue_LEFT1=SomeValue_RIGHT1
- then (SomeNumber1_SUM+SomeNumber2_SUM+SomeNumber3_SUM)/_COUNT
- when SomeValue_LEFT1<SomeValue_RIGHT1
- then (SomeNumber1_SUM+SomeNumber2_SUM)/_COUNT
- when SomeValue_LEFT1>SomeValue_RIGHT1
- then (SomeNumber2_SUM+SomeNumber3_SUM)/_COUNT
- end as AvgSomeNumber
- from dbo.vBigTestA_C_AGG with(noexpand)
- go--
- --/*--END DDL AND TEST DATA------------------------------------------------
- --
- --
- --/*--performance test code------------------------------------------------
- --/*--session settings-------------
- set
numeric_roundabort off
- set ansi_nulls on
- set ansi_padding on
- set ansi_warnings on
- set arithabort on
- set concat_null_yields_null on
- set quoted_identifier on
- --*/--end session settings---------
- set
statistics profile on
- set statistics time on
- go-- TEST 1: full data set based on original spec, ordered
- select
SomeValue, AMonth, AvgSomeNumber from vBigTestA_C_FromTables order by SomeValue
- go--
- select
SomeValue, AMonth, AvgSomeNumber from vBigTestA_C_FromViewIndex
- --
- go-- TEST 2: moderately selective query
- select
SomeValue
- ,
AMonth
- ,
right(SomeValue,1) SomeValueRight1
- ,
abs(SomeNumber1-SomeNumber3) SomeNumber1_3diff
- ,
count_big(*) Cnt
- from
vBigTestA_C_FromTables
- where
right(SomeValue,1) in ('F','X','J','Z','R') or abs(SomeNumber1-SomeNumber3) <=100
- group
by SomeValue, AMonth, right(SomeValue,1), abs(SomeNumber1-SomeNumber3)
- order
by SomeValue
- go--
- select
SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue
- ,
AMonth
- ,
SomeValue_RIGHT1 SomeValueRight1
- ,
abs(SomeNumber1_SUM - SomeNumber3_SUM) SomeNumber1_3diff
- ,
count_big(*) Cnt
- from
dbo.vBigTestA_C_AGG with(noexpand)
- where
SomeValue_RIGHT1 in ('F','X','J','Z','R') or abs(SomeNumber1_SUM - SomeNumber3_SUM) <=100
- group
by SomeValue_LEFT1,SomeValue_RIGHT1, AMonth, abs(SomeNumber1_SUM - SomeNumber3_SUM)
- --
- go-- TEST 3: more selective query
- select
SomeValue
- ,
AMonth
- ,
right(SomeValue,1) SomeValueRight1
- ,
count_big(*) Cnt
- from
vBigTestA_C_FromTables
- where
SomeValue in ('FR','XR','DR', 'RR')
- or
SomeValue between 'JF' and 'JZ'
- group
by SomeValue, AMonth, right(SomeValue,1)
- order
by SomeValue
- go--
- select
SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue
- ,
AMonth
- ,
SomeValue_RIGHT1 SomeValueRight1
- ,
count_big(*) Cnt
- from
dbo.vBigTestA_C_AGG with(noexpand)
- where
(SomeValue_RIGHT1 = 'R' and SomeValue_LEFT1 in ('F','X','D','R'))
- or (SomeValue_LEFT1 = 'J' and SomeValue_RIGHT1 between 'F' and 'Z')
- group
by SomeValue_LEFT1,SomeValue_RIGHT1, AMonth
- go--
- set
statistics time off
- set statistics profile off
- go--
- --*/--end performance test code--------------------------------------------
Here's the output:
TEST 1: Complete output from views.
Query 1 (table indexes) v Query 2 (view indexes).
result: view indexes found to be 10 times faster than table indexes. table indexes estimated to be 128 times more costly than view indexes.
TEST 2: Moderately selective complex filter applied to views.
Query 3 (table indexes) v Query 4 (view indexes).
result: view indexes found to be 20 times faster than table indexes. table indexes estimated to be 89 times more costly than view indexes.
TEST 3: Highly selective filter, grouping applied to views.
Query 5 (table indexes) v Query 6 (view indexes).
result: view indexes found to be 70 times faster than table indexes. table indexes estimated to be 1,283 times more costly than view indexes. COMMENTS: The optimiser's cost estimates may be inaccurate. It is possible to check the optimiser's accuracy in predicting rowcounts, since the actual row count is stored during execution.
For queries 1,3 and 5 (table indexes) the rowcounts have been significantly underestimated for the two main operations (index scan and hash join).
In queries 2 and 6 the rowcount estimates where correct. In query 4 the rowcount was doubled.
This means that the cost ratios given above are likely to be overgenerous(!) to the table index method.
------------------------------------------------------------------------------------------------------------ Query 1: query cost (relative to the batch): 40% ------------------------------------------------------------------------------------------------------------
Statement text:
select SomeValue, AMonth, AvgSomeNumber from vBigTestA_C_FromTables order by SomeValue
Execution Plan:
StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select SomeValue, AMonth, AvgSomeNumber from vBigTestA_C_FromTables order by SomeValue |--Sort(ORDER BY: (.[SomeValue] ASC)) |--Compute Scalar(DEFINE: ([Expr1010]=CASE WHEN substring([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1),(1))=right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1)) THEN [Expr1004] ELSE CASE |--Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN [Expr1028]=(0) THEN NULL ELSE [Expr1029]/CONVERT_IMPLICIT(money,[Expr1028],0) END, [Expr1005]=CASE WHEN [Expr1030]=(0) THEN NULL ELSE [Expr1031]/CONVERT_IMPLICIT(money,[ |--Hash Match(Aggregate, HASH: (.[SomeValue], .[AMonth]), RESIDUAL: ([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] = [Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] AND [Scheduler].[ |--Hash Match(Inner Join, HASH: ([m].[RowNum])=(.[MoreDataRowNum])) |--Clustered Index Scan(OBJECT: ([Scheduler].[dbo].[MoreData].[PK__MoreData__24DD5622] AS [m])) |--Compute Scalar(DEFINE: (.[AMonth]=[Scheduler].[dbo].[BigTest].[AMonth] as .[AMonth], [Expr1011]=[Scheduler].[dbo].[BigTest].[SomeNumber1] as .[SomeNumber1]+[Scheduler].[dbo].[BigTest].[SomeN |--Index Scan(OBJECT: ([Scheduler].[dbo].[BigTest].[IX_BigTest_covering] AS ), WHERE: (right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1))<'A' OR right([Scheduler].[dbo].
=[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]+[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_RIGHT1], [Expr1004]=CASE WHEN [Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]=[Sch
|--Clustered Index Scan(OBJECT: ([Scheduler].[dbo].[vBigTestA_C_AGG].[IXC_vBigTestA_C_AGG_covering]))
Rows PhysicalOp (LogicalOp) EstimateRows EstimateIO EstimateCPU AvgRowSize SubtreeCost
--------- ---------------------- ------------ ---------- ----------- ---------- -----------
71760 71760 0.4390236
0 Compute Scalar 71760 0 0.007176 24 0.4390236
71760 Clustered Index Scan 71760 0.3527546 0.079093 49 0.4318476
Time statistics:
SQL Server Execution Times: CPU time = 201 ms, elapsed time = 2084 ms. SQL Server parse and compile time: CPU time = 40 ms, elapsed time = 140 ms.
------------------------------------------------------------------------------------------------------------ Query 3: query cost (relative to the batch): 37% ------------------------------------------------------------------------------------------------------------
Statement text:
select SomeValue, AMonth, right(SomeValue,1) SomeValueRight1, abs(SomeNumber1-SomeNumber3) SomeNumber1_3diff, count_big(*) Cnt from vBigTestA_C_FromTables where right(SomeValue,1) in ('F','X','J','Z','R') or abs(SomeNumber1-SomeNExecution Plan:
StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select SomeValue, AMonth, right(SomeValue,1) SomeValueRight1, abs(SomeNumber1-SomeNumber3) SomeNumber1_3diff, count_big(*) Cnt from vBigTestA_C_FromTables where right(SomeValue,1) in ('F','X','J','Z','R') or abs(SomeNumber1-SomeN |--Sort(ORDER BY: (.[SomeValue] ASC)) |--Compute Scalar(DEFINE: ([Expr1012]=abs([Expr1007]-[Expr1009]), [Expr1013]=CASE WHEN (0) IS NULL THEN (0) ELSE (1) END)) |--Filter(WHERE: ([Expr1014]='R' OR [Expr1014]='Z' OR [Expr1014]='J' OR [Expr1014]='X' OR [Expr1014]='F' OR abs([Expr1007]-[Expr1009])<=($100.0000))) |--Compute Scalar(DEFINE: ([Expr1014]=right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1)))) |--Hash Match(Aggregate, HASH: (.[SomeValue], .[AMonth]), RESIDUAL: ([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] = [Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] AND [Schedul |--Hash Match(Inner Join, HASH: ([m].[RowNum])=(.[MoreDataRowNum])) |--Clustered Index Scan(OBJECT: ([Scheduler].[dbo].[MoreData].[PK__MoreData__24DD5622] AS [m])) |--Compute Scalar(DEFINE: (.[AMonth]=[Scheduler].[dbo].[BigTest].[AMonth] as .[AMonth])) |--Index Scan(OBJECT: ([Scheduler].[dbo].[BigTest].[IX_BigTest_covering] AS ), WHERE: (right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1))<'A' OR right([Scheduler]. Rows PhysicalOp (LogicalOp) EstimateRows EstimateIO EstimateCPU AvgRowSize SubtreeCost -------- ---------------------- ---------- ---------- ----------- ---------- ----------- 15716 38759.57 52.92312 15716 Sort 38759.57 0.01126126 2.711797 35 52.92312 0 Compute Scalar 38759.57 0 0.003875957 35 50.20007 15716 Filter 38759.57 0 0.190273 35 50.19619 0 Compute Scalar 79946.64 0 0.007994664 35 50.00592 71760 Hash Match (Aggregate) 79946.64 0 16.06204 33 49.99792 3540000 Hash Match (Inner Join) 2034241 0 10.936 33 33.93587 2000 Clustered Index Scan 2000 0.00682870 0.002357 19 0.009185703 0 Compute Scalar 2040000 0 0.4 31 19.47069 3540000 Index Scan 2040000 14.67053 4.400157 29 19.07069Time statistics:
SQL Server Execution Times: CPU time = 12948 ms, elapsed time = 17230 ms. SQL Server parse and compile time: CPU time = 801 ms, elapsed time = 1538 ms.
------------------------------------------------------------------------------------------------------------ Query 4: query cost (relative to the batch): 0% ------------------------------------------------------------------------------------------------------------
Statement text:
select SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue, AMonth, SomeValue_RIGHT1 SomeValueRight1, abs(SomeNumber1_SUM - SomeNumber3_SUM) SomeNumber1_3diff, count_big(*) Cnt from dbo.vBigTestA_C_AGG with(noexpand) where SomeValue_RIGHT
Execution Plan: StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue, AMonth, SomeValue_RIGHT1 SomeValueRight1, abs(SomeNumber1_SUM - SomeNumber3_SUM) SomeNumber1_3diff, count_big(*) Cnt from dbo.vBigTestA_C_AGG with(noexpand) where SomeValue_RIGHT |--Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN (0) IS NULL THEN (0) ELSE (1) END)) |--Compute Scalar(DEFINE: ([Expr1003]=abs([Scheduler].[dbo].[vBigTestA_C_AGG].[SomeNumber1_SUM]-[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeNumber3_SUM]), [Expr1005]=[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]+[Sche |--Clustered Index Scan(OBJECT: ([Scheduler].[dbo].[vBigTestA_C_AGG].[IXC_vBigTestA_C_AGG_covering]), WHERE: ([Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_RIGHT1]='R' OR [Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValu Rows PhysicalOp (LogicalOp) EstimateRows EstimateIO EstimateCPU AvgRowSize SubtreeCost -------- --------------------- ------------ ---------- ----------- ---------- ----------- 15716 32448 0.5915293 0 Compute Scalar 32448 0 0.0032448 35 0.5915293 0 Compute Scalar 32448 0 0.007176 27 0.4390236 15716 Clustered Index Scan 32448 0.3527546 0.079093 34 0.4318476
Time statistics:
SQL Server Execution Times: CPU time = 100 ms, elapsed time = 786 ms. SQL Server parse and compile time: CPU time = 40 ms, elapsed time = 105 ms.
------------------------------------------------------------------------------------------------------------ Query 5: query cost (relative to the batch): 23% ------------------------------------------------------------------------------------------------------------
Statement text:
select SomeValue, AMonth, right(SomeValue,1) SomeValueRight1, count_big(*) Cnt from vBigTestA_C_FromTables where SomeValue in ('FR','XR','DR', 'RR') or SomeValue between 'JF' and 'JZ' group by SomeValue, AMonth, right(SomeValue,1Execution Plan: StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select SomeValue, AMonth, right(SomeValue,1) SomeValueRight1, count_big(*) Cnt from vBigTestA_C_FromTables where SomeValue in ('FR','XR','DR', 'RR') or SomeValue between 'JF' and 'JZ' group by SomeValue, AMonth, right(SomeValue,1 |--Compute Scalar(DEFINE: ([Expr1012]=CASE WHEN (0) IS NULL THEN (0) ELSE (1) END, [Expr1013]=right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1)))) |--Sort(ORDER BY: (.[SomeValue] ASC)) |--Hash Match(Aggregate, HASH: (.[SomeValue], .[AMonth]), RESIDUAL: ([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] = [Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue] AND [Scheduler].[dbo]. |--Hash Match(Inner Join, HASH: ([m].[RowNum])=(.[MoreDataRowNum])) |--Clustered Index Scan(OBJECT: ([Scheduler].[dbo].[MoreData].[PK__MoreData__24DD5622] AS [m])) |--Compute Scalar(DEFINE: (.[AMonth]=[Scheduler].[dbo].[BigTest].[AMonth] as .[AMonth])) |--Index Scan(OBJECT: ([Scheduler].[dbo].[BigTest].[IX_BigTest_covering] AS ), WHERE: ((right([Scheduler].[dbo].[BigTest].[SomeValue] as .[SomeValue],(1))<'A' OR right([Scheduler].[dbo].[BigTest]Rows PhysicalOp (LogicalOp) EstimateRows EstimateIO EstimateCPU AvgRowSize SubtreeCost -------- ----------------------- ------------ ---------- ----------- ---------- ----------- 3000 1276.67 32.26978 0 Compute Scalar 1276.67 0 0.000127666 27 32.26978 3000 Sort 1276.67 0.01126126 0.02064973 17 32.26965 3000 Hash Match (Aggregate) 1276.67 0 0.4595915 17 32.23774 148148 Hash Match (Inner Join) 63695.74 0 0.378274 21 31.77815 2000 Clustered Index Scan 2000 0.00682870 0.002357 11 0.009185703 0 Compute Scalar 63876.08 0 0.4 27 19.47069 148148 Index Scan 63876.08 14.67053 4.400157 21 19.07069
Time statistics:
SQL Server Execution Times: CPU time = 10204 ms, elapsed time = 13017 ms. SQL Server parse and compile time: CPU time = 20 ms, elapsed time = 140 ms.
------------------------------------------------------------------------------------------------------------ Query 6: query cost (relative to the batch): 0% ------------------------------------------------------------------------------------------------------------
Statement text:
select SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue, AMonth, SomeValue_RIGHT1 SomeValueRight1, count_big(*) Cnt from dbo.vBigTestA_C_AGG with(noexpand) where (SomeValue_RIGHT1 = 'R' and SomeValue_LEFT1 in ('F','X','D','R')) or (SomExecution Plan: StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select SomeValue_LEFT1+SomeValue_RIGHT1 SomeValue, AMonth, SomeValue_RIGHT1 SomeValueRight1, count_big(*) Cnt from dbo.vBigTestA_C_AGG with(noexpand) where (SomeValue_RIGHT1 = 'R' and SomeValue_LEFT1 in ('F','X','D','R')) or (Som |--Compute Scalar(DEFINE: ([Expr1003]=CASE WHEN (0) IS NULL THEN (0) ELSE (1) END)) |--Compute Scalar(DEFINE: ([Expr1004]=[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]+[Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_RIGHT1])) |--Concatenation |--Clustered Index Seek(OBJECT: ([Scheduler].[dbo].[vBigTestA_C_AGG].[IXC_vBigTestA_C_AGG_covering]), SEEK: ([Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]='D' AND [Scheduler].[dbo].[vBigTestA_C_AGG].[Some |--Clustered Index Seek(OBJECT: ([Scheduler].[dbo].[vBigTestA_C_AGG].[IXC_vBigTestA_C_AGG_covering]), SEEK: ([Scheduler].[dbo].[vBigTestA_C_AGG].[SomeValue_LEFT1]='J' AND [Scheduler].[dbo].[vBigTestA_C_AGG].[SomeRows PhysicalOp (LogicalOp) EstimateRows EstimateIO EstimateCPU AvgRowSize SubtreeCost -------- ----------------------- ------------ ---------- ----------- ---------- ----------- 3000 3000 0.02513807 0 Compute Scalar 3000 0 0.0003 27 0.02513807 0 Compute Scalar 3000 0 0.0003 19 0.02483807 3000 Concatenation 3000 0 0.0003 19 0.02423807 480 Clustered Index Seek 480 0.00534722 0.000685 19 0.006032222 2520 Clustered Index Seek 2520 0.01497685 0.002929 19 0.01790585
Time statistics:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 179 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Must be a nasty copy'n'paste error... The code doesn't work as posted...
GO must be the only thing on a line...
Every /* must be match with an */...
...
However, I agree that the indexed view is faster... much faster... in fact, 31.7578740 times faster (actual run
time on a single user server from your first example using a query specific tuned index instead of just a covering index)...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(71760 row(s) affected)
32266 Table view duration (milliseconds)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(71760 row(s) affected)
1016 Indexed view duration (milliseconds)
The DBCC messages are because I clear cache and buffers before each run.
From a CPU perspective, I got the following (top numbers are the table run) on a separate run...
SQL Server Execution Times:
CPU time = 24421 ms, elapsed time = 31702 ms.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 1047 ms.
What a pain in the hiney to get there, though
Like the book says,you absolutely cannot have nulls in the aggragated view columns (a good table won't but it does happen),
you can't combine aggragates with columns so you need to make two views to get 1 indexed view,
you must build your own AVG as SUM(x)/COUNT_BIG(*),
forget about MIN or MAX (did test THAT, I'll take their word on that one based on AVG),
and if you absolutely need to do an outer join, just forget it.
I've not checked what the overhead cause by the index on the for doing an INSERT into the base tables
but I can imagine it would be any more than any other clustered index (which, by the way, can be
a very high cost depending on the index and data being inserted)
Still, with that much speed and low resource usage, you can do some awesome stuff.
So Tim, you were right and I understood what you were saying... yes, base table indexes are used on
normal views but indexed views are much faster and can be "worth it".
Thanks for taking the time to put the code example together.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply