Performance question

sql functions vs sub selects vs inner joins instead of sub selects. Which
is faster."isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:eRs9$YBLGHA.1132@.TK2MSFTNGP10.phx.gbl...
> sql functions vs sub selects vs inner joins instead of sub selects. Which
> is faster.
>
>
Depends on many things. Indexes, parallelism and so forth.
Start doing some reading at www.sql-server-performance.com
Check the execution plans and so forth.
Rick Sawtell
MCT, MCSD, MCDBA

performance question

I've got 3 functions. one that calculates a value, and two other ones that
return a value based on the value returned by the first.
I use these in a view
example:
select
dbo.calcvalue(t.[id]) as value,
dbo.calc1(calcvalue(t.[id]) )) as val1,
dbo.calc2(calcvalue(t.[id]) )) as val2
from
tabletest t
will calcvalue() get called 3 times? or would it be more efficient to use
two views.
example:
view 1:
select
dbo.calcvalue(t.[id]) as value
from
tabletest t
view 2:
select
dbo.calc1(value) as val1,
dbo.calc2(value) as val2
from
view1user defined functions must be deterministic - give the same output for the
same input. I think one reason this restriction is there is so that sql can
eliminate performing second and third calls to your function. That said, I
don't know if it will call it more than once. You can insure it won't by
using a derived table.
select t.id, value, dbo.calc1(value) as val1, dbo.calc2(value) as val2
from
(
select id, calcvalue(id) as value
from tabletest
) as t
Jeremy wrote:
>I've got 3 functions. one that calculates a value, and two other ones that
>return a value based on the value returned by the first.
>I use these in a view
>example:
>select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
>from
> tabletest t
>will calcvalue() get called 3 times? or would it be more efficient to use
>two views.
>example:
>view 1:
>select
> dbo.calcvalue(t.[id]) as value
>from
> tabletest t
>view 2:
>select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
>from
> view1
--
Message posted via http://www.sqlmonster.com|||Jeremy,
The rules for scalar UDFs allow for many types of optimization,
including substitution. However, a simple test will show that both SQL
Server 2000 and SQL Server 2005 will not reuse the result of the UDF for
the same row.
So if you have an expensive dbo.calcvalue, and a very cheap dbo.calc1,
then the query
select dbo.calcvalue(id), dbo.calc1(dbo.calcvalue(id)) from t
will need almost twice as long to finish when compared to
select dbo.calcvalue(id), dbo.calc1(id) from t
--
Gert-Jan
Jeremy wrote:
> I've got 3 functions. one that calculates a value, and two other ones that
> return a value based on the value returned by the first.
> I use these in a view
> example:
> select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
> from
> tabletest t
> will calcvalue() get called 3 times? or would it be more efficient to use
> two views.
> example:
> view 1:
> select
> dbo.calcvalue(t.[id]) as value
> from
> tabletest t
> view 2:
> select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
> from
> view1

Performance of Nested Views

In Our application there is a method which contain a series ( around 7 ) of
functions ( we can think it as sql queries).
Earlier we use to create one table for each fuction out put and use it as
the input to next function. ( we use simple recovery model)
Things were working at that time , but to improve performance ( reduce I/O )
we decided to create views as the ouput of each fuction and use it as the
input of next funtion and so on . At last we create a table from the last
view created.Actually we only need this table( out put of whole method ) for
future use.
But when we try to create the table from the last view, it just take hours
and hours to complete(?) .( never completed even after 60 hours).
Earlier in the table creation approach entire method use to complete in 16
hours.
I'm not sure how sql sever resolve the query when we use the last view .Is
there any way we can see how optimizer convert whole thing into a query.
What could be the reason for this method not working.?Is there any reason
nested views performance degrade when level increases?
Any help really appreciated .
Thanks
Binu
Below the queries I use.
table T123456_2004_JAN has got 5,000,000 records.
CREATE VIEW [dbo].[S1233769547736706426] AS SELECT [S3895777261424671822].*
FROM T123456_2004_JAN
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S1233769547736706426].*
,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
[A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
[B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
[B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END [PM00462]
FROM DBO.[S1233769547767957187] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) }
= { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
[A].[F42] ) + [A].[F39] )<= dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
[B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547912175445] AS SELECT [S1233769547879852187].*
,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE WHEN
{ fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END ) END )
[PM00464] FROM DBO.[S1233769547879852187]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
[S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
FROM DBO.[S1233769547912175445]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
[S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
[PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
DBO.[S1233769547N1268698629]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
[S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT ( NUMERIC
( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END [PM00467]
FROM DBO.[S1233769547N1213049039]
Final Statement to Create the Table: -- ( this never complete after 60
hours)
SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
[D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
[FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4] ,
[FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017] [D188_17]
, [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6] ,
[FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8] ,
[FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23] ,
[FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15] ,
[FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18] ,
[FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016] [D188_16]
, [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187] ,
[PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
DBO.[S1233769547N1177270046]
Below the estimated execution Plan:
|--Table Insert(OBJECT:([S1387535081666676858]),
SET:([S1387535081666676858].[D167]=[Expr1022],
[S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
[S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
[S1387535081666676858].[D1]=[Expr1018],
[S1387535081666676858].[D166]=[Expr1021],
[S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
[S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
[S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
[S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
[S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
[S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
[S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
[S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
[S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
[S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
[S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
[S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
[S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
[S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
[S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
[S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
[S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
[S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
[S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
[S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
[S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
[S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
[S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
[S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
[S1387535081666676858].[D198]=[Expr1020],
[S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
[S1387535081666676858].[D192]=[Expr1019],
[S1387535081666676858].[F41]=[S138753508748329346].[F41],
[S1387535081666676858].[F1]=[S138753508748329346].[F1],
[S1387535081666676858].[F39]=[S138753508748329346].[F39],
[S1387535081666676858].[F40]=[S138753508748329346].[F40],
[S1387535081666676858].[F42]=[S138753508748329346].[F42],
[S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
[S1387535081666676858].[D193]=[Expr1002]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1019]=If ('May 1 2004
12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
round([Expr1018]/Convert(If ('May 1 2004
12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
[Expr1021]=round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')=0) then 0 else If (datepart(day,
[S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
(datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
[Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')=0) then 0 else If (datepart(day,
[S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
(datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
[Expr1018], 10, NULL)))
|--Table Spool
|--Compute
Scalar(DEFINE:([Expr1018]=isnull([Expr1016], 0.000)))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([S138753508748329346].[F42], [S138753508748329346].[F41],
[S138753508748329346].[F40], [S138753508748329346].[F1],
[S138753508748329346].[F39]))
|--Compute
Scalar(DEFINE:([Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
| |--Nested Loops(Inner Join,
WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
[S138753508748329346].[PM00458]))
| |--Table
Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
| |--Row Count Spool
| |--Constant Scan
|--Hash Match(Cache,
HASH:([S138753508748329346].[F42], [S138753508748329346].[F41],
[S138753508748329346].[F40], [S138753508748329346].[F1],
[S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
RESIDUAL:(((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
[S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
[S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
[S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
[S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
[ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
|--Compute Scalar(DEFINE:([Expr1016]=If
([Expr1151]=0) then NULL else [Expr1152]))
|--Stream
Aggregate(DEFINE:([Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
[Expr1152]=SUM([S138753508748329346].[PM00458])))
|--Nested Loops(Inner Join,
WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
[S138753508748329346].[PM00458]))
|--Filter(WHERE:(isnull([S138753508748329346].[F40],
Convert(char(0)))>[S138753508748329346].[F40] OR
(isnull([S138753508748329346].[F40],
Convert(char(0)))=[S138753508748329346].[F40] AND
[dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
)+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S1387535087483
29346].[F42]))+[S138753508748329346].[F39])))))
| |--Index
Spool(SEEK:([S138753508748329346].[F39]=[S138753508748329346].[F39]))
| |--Table
Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
|--Row Count Spool
|--Constant Scan"Abraham" <binu_ca@.yahoo.com> wrote in message
news:%23A4n9FraEHA.4048@.TK2MSFTNGP10.phx.gbl...
> In Our application there is a method which contain a series ( around 7 )
of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce
I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method )
for
> future use.
> But when we try to create the table from the last view, it just take
hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
Yes that can happen. Taking a process that used temp tables and rewriting
it to use views is not guaranteed to run faster. In fact it is almost
guaranteed to run differently, but not necessarilly faster.
Unfortuately that's about all anyone can tell you since the code you posted
almost 100% not human-readable. If you had a real schema with entities with
english names and columns and keys and whatnot, someone might be able to
help. But as it is, you're on your own.
David|||User Defined Functions can be very bad for performance. Have you tried to
replace the functions with straight SQL? From the names of the functions I
assume that they are rather straightforward.
--
Jacco Schalkwijk
SQL Server MVP
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:%23A4n9FraEHA.4048@.TK2MSFTNGP10.phx.gbl...
> In Our application there is a method which contain a series ( around 7 )
of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce
I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method )
for
> future use.
> But when we try to create the table from the last view, it just take
hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
> Any help really appreciated .
> Thanks
> Binu
> Below the queries I use.
> table T123456_2004_JAN has got 5,000,000 records.
> CREATE VIEW [dbo].[S1233769547736706426] AS SELECT
[S3895777261424671822].*
> FROM T123456_2004_JAN
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT
[S1233769547736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
> [B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
> [B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END
[PM00462]
> FROM DBO.[S1233769547767957187] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40]
,CHAR(0) ) }
> = { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
> [B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597]
[A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547912175445] AS SELECT
[S1233769547879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE
WHEN
> { fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
> TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END )
END )
> [PM00464] FROM DBO.[S1233769547879852187]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
> [S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
> FROM DBO.[S1233769547912175445]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
> [S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
> [PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
> DBO.[S1233769547N1268698629]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
> [S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT (
NUMERIC
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END
[PM00467]
> FROM DBO.[S1233769547N1213049039]
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4]
,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017]
[D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6]
,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8]
,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23]
,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15]
,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18]
,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016]
[D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187]
,
> [PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
> DBO.[S1233769547N1177270046]
>
>
> Below the estimated execution Plan:
> |--Table Insert(OBJECT:([S1387535081666676858]),
> SET:([S1387535081666676858].[D167]=[Expr1022],
> [S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
> [S1387535081666676858].[D192]=[Expr1019],
> [S1387535081666676858].[F41]=[S138753508748329346].[F41],
> [S1387535081666676858].[F1]=[S138753508748329346].[F1],
> [S1387535081666676858].[F39]=[S138753508748329346].[F39],
> [S1387535081666676858].[F40]=[S138753508748329346].[F40],
> [S1387535081666676858].[F42]=[S138753508748329346].[F42],
> [S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
> [S1387535081666676858].[D193]=[Expr1002]))
> |--Top(ROWCOUNT est 0)
> |--Compute Scalar(DEFINE:([Expr1019]=If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
> ('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If
(datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
> round([Expr1018]/Convert(If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
> [Expr1021]=round(110*If ('May 1 2004
12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
> [Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
> Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
> [Expr1018], 10, NULL)))
> |--Table Spool
> |--Compute
> Scalar(DEFINE:([Expr1018]=isnull([Expr1016], 0.000)))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39]))
> |--Compute
>
Scalar(DEFINE:([Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
> | |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> | |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
> | |--Row Count Spool
> | |--Constant Scan
> |--Hash Match(Cache,
> HASH:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
> RESIDUAL:(((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
> [S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
> [ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
> |--Compute
Scalar(DEFINE:([Expr1016]=If
> ([Expr1151]=0) then NULL else [Expr1152]))
> |--Stream
> Aggregate(DEFINE:([Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
> [Expr1152]=SUM([S138753508748329346].[PM00458])))
> |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> |--Filter(WHERE:(isnull([S138753508748329346].[F40],
> Convert(char(0)))>[S138753508748329346].[F40] OR
> (isnull([S138753508748329346].[F40],
> Convert(char(0)))=[S138753508748329346].[F40] AND
>
[dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
>
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
>
48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
>
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
>
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
>
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
> )+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S13875350874
83
> 29346].[F42]))+[S138753508748329346].[F39])))))
> | |--Index
> Spool(SEEK:([S138753508748329346].[F39]=[S138753508748329346].[F39]))
> | |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
> |--Row Count Spool
> |--Constant Scan
>|||The latest copy of the SQL Server Newsletter from http://www.sqlmag.com contains a good discussion on enstec views and what to be wary of when using them. I received my newsletter (via email) about 12 hours ago. I don't have it handy though unfortunately but you should be able to find it by going to the website.
Regards
Jamie
"Abraham" wrote:
> In Our application there is a method which contain a series ( around 7 ) of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method ) for
> future use.
> But when we try to create the table from the last view, it just take hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
> Any help really appreciated .
> Thanks
> Binu
> Below the queries I use.
> table T123456_2004_JAN has got 5,000,000 records.
> CREATE VIEW [dbo].[S1233769547736706426] AS SELECT [S3895777261424671822].*
> FROM T123456_2004_JAN
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S1233769547736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
> [B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
> [B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END [PM00462]
> FROM DBO.[S1233769547767957187] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) }
> = { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
> [B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547912175445] AS SELECT [S1233769547879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE WHEN
> { fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
> TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END ) END )
> [PM00464] FROM DBO.[S1233769547879852187]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
> [S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
> FROM DBO.[S1233769547912175445]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
> [S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
> [PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
> DBO.[S1233769547N1268698629]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
> [S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT ( NUMERIC
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END [PM00467]
> FROM DBO.[S1233769547N1213049039]
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4] ,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017] [D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6] ,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8] ,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23] ,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15] ,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18] ,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016] [D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187] ,
> [PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
> DBO.[S1233769547N1177270046]
>
>
> Below the estimated execution Plan:
> |--Table Insert(OBJECT:([S1387535081666676858]),
> SET:([S1387535081666676858].[D167]=[Expr1022],
> [S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
> [S1387535081666676858].[D192]=[Expr1019],
> [S1387535081666676858].[F41]=[S138753508748329346].[F41],
> [S1387535081666676858].[F1]=[S138753508748329346].[F1],
> [S1387535081666676858].[F39]=[S138753508748329346].[F39],
> [S1387535081666676858].[F40]=[S138753508748329346].[F40],
> [S1387535081666676858].[F42]=[S138753508748329346].[F42],
> [S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
> [S1387535081666676858].[D193]=[Expr1002]))
> |--Top(ROWCOUNT est 0)
> |--Compute Scalar(DEFINE:([Expr1019]=If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
> ('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
> round([Expr1018]/Convert(If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
> [Expr1021]=round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
> [Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
> Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
> [Expr1018], 10, NULL)))
> |--Table Spool
> |--Compute
> Scalar(DEFINE:([Expr1018]=isnull([Expr1016], 0.000)))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39]))
> |--Compute
> Scalar(DEFINE:([Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
> | |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> | |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
> | |--Row Count Spool
> | |--Constant Scan
> |--Hash Match(Cache,
> HASH:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
> RESIDUAL:(((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
> [S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
> [ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
> |--Compute Scalar(DEFINE:([Expr1016]=If
> ([Expr1151]=0) then NULL else [Expr1152]))
> |--Stream
> Aggregate(DEFINE:([Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
> [Expr1152]=SUM([S138753508748329346].[PM00458])))
> |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> |--Filter(WHERE:(isnull([S138753508748329346].[F40],
> Convert(char(0)))>[S138753508748329346].[F40] OR
> (isnull([S138753508748329346].[F40],
> Convert(char(0)))=[S138753508748329346].[F40] AND
> [dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
> TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
> 48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
> [S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
> UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
> Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
> )+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S1387535087483
> 29346].[F42]))+[S138753508748329346].[F39])))))
> | |--Index
> Spool(SEEK:([S138753508748329346].[F39]=[S138753508748329346].[F39]))
> | |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
> |--Row Count Spool
> |--Constant Scan
>
>

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.