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.EMPT YVALUEHANDLE(dbo.EMPTYVALU
EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
[A].[F42] ) + [A].[F39] )<=
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(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(WHEREisnull([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.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(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(WHEREisnull([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.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(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(WHEREisnull([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
>
>