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 [S38957772614
24671822].*
FROM T123456_2004_JAN
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S12337695477
36706426].*
,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAUL
T ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
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.[S12337695477367064
26]
--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 ( S
ELECT
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] = &#
91;A].[F39] ) END [PM00462]
FROM DBO.[S1233769547767957187] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SEL
ECT { fn
IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S123376954782420
2597] [B]
WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A]
.[F40] ,CHAR(0) ) } > {
fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].&
#91;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.[S
1233769547824202597] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547912175445] AS SELECT [S12337695478
79852187].*
,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDI
FF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPA
RT
( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPD
IFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE W
HEN
{ 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 ) EN
D )
[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 ) } E
ND [PM00467]
FROM DBO.[S1233769547N1213049039]
Final Statement to Create the Table: -- ( this never complete after 60
hours)
SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM004
64] [D192] , [PM00459]
[D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00
461] [D193] ,
[FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG0
0456_004] [D188_4] ,
[FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG0
0456_017] [D188_17]
, [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG0
0456_006] [D188_6] ,
[FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG0
0456_008] [D188_8] ,
[FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG004
56_023] [D188_23] ,
[FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00
456_015] [D188_15] ,
[FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00
456_018] [D188_18] ,
[FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG0
0456_016] [D188_16]
, [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D18
4] , [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].[FG00
456_016],
[S1387535081666676858].[D188_12]=[S138753508748329346].[FG00
456_012],
[S1387535081666676858].[D188_22]=[S138753508748329346].[FG00
456_022],
[S1387535081666676858].[D188_18]=[S138753508748329346].[FG00
456_018],
[S1387535081666676858].[D188_20]=[S138753508748329346].[FG00
456_020],
[S1387535081666676858].[D188_1]=[S138753508748329346].[FG004
56_001],
[S1387535081666676858].[D188_15]=[S138753508748329346].[FG00
456_015],
[S1387535081666676858].[D188_11]=[S138753508748329346].[FG00
456_011],
[S1387535081666676858].[D188_7]=[S138753508748329346].[FG004
56_007],
[S1387535081666676858].[D188_23]=[S138753508748329346].[FG00
456_023],
[S1387535081666676858].[D188_5]=[S138753508748329346].[FG004
56_005],
[S1387535081666676858].[D188_9]=[S138753508748329346].[FG004
56_009],
[S1387535081666676858].[D188_8]=[S138753508748329346].[FG004
56_008],
[S1387535081666676858].[D188_24]=[S138753508748329346].[FG00
456_024],
[S1387535081666676858].[D188_14]=[S138753508748329346].[FG00
456_014],
[S1387535081666676858].[D188_6]=[S138753508748329346].[FG004
56_006],
[S1387535081666676858].[D188_2]=[S138753508748329346].[FG004
56_002],
[S1387535081666676858].[D188_3]=[S138753508748329346].[FG004
56_003],
[S1387535081666676858].[D188_17]=[S138753508748329346].[FG00
456_017],
[S1387535081666676858].[D188_21]=[S138753508748329346].[FG00
456_021],
[S1387535081666676858].[D188_10]=[S138753508748329346].[FG00
456_010],
[S1387535081666676858].[D188_4]=[S138753508748329346].[FG004
56_004],
[S1387535081666676858].[D188_13]=[S138753508748329346].[FG00
456_013],
[S1387535081666676858].[D188_19]=[S138753508748329346].[FG00
456_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 200412:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else I
f
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2
004
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(m
onth,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr10
20]=If (If
('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (datedif
f(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else I
f
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2
004
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(m
onth,
'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 I
f
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2
004
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(m
onth,
'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(m
onth,
'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].[F
41])
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(m
onth,
'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, DEFA
ULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 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)OUTERREFERENCES
[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)OUTERREFERENCES
[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](Co
nvert([dbo].[EMP
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([
;S1387535087
48329346].[F41])+[S138753508748329346].[F40]))+[S13875350874
8329346].[F1]))+
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<
=[dbo].[EMPTYVAL
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EM
PTYVALUEHANDLE](
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087483
29346].[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 , DEFA
ULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> 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.[S123376954773670
6426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CAS
E WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE (
SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHE
RE ( (
> [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].* , ( S
ELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824
202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [
A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F4
0]
,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.[S123376
9547824202597]
[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 TIMESTAMP
DIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART[/vb
col]
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAM
PDIFF (
> 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 TH
EN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) TH
EN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { f
n
> 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 [PM0045
7] = 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 ([/vbcol
]
NUMERIC[vbcol=seagreen]
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END[/vb
col]
[PM00467][vbcol=seagreen]
> FROM DBO.[S1233769547N1213049039]
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM0
0464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM
00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_0
04] [D188_4]
,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_0
17]
[D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_0
06] [D188_6]
,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_0
08] [D188_8]
,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023
] [D188_23]
,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_01
5] [D188_15]
,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_01
8] [D188_18]
,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_0
16]
[D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , &
#91;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].[PM004
60],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM004
58],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG
00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG
00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG
00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG
00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG
00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG0
0456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG
00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG
00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG0
0456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG
00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG0
0456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG0
0456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG0
0456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG
00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG
00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG0
0456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG0
0456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG0
0456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG
00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG
00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG
00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG0
0456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG
00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG
00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM004
57],
> [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].[PM004
59],
> [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)), [Expr
1020]=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 NU
LL 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)[/v
bcol]
else[vbcol=seagreen]
> ( -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)[/v
bcol]
else[vbcol=seagreen]
> ( -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, DE
FAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 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]) A
ND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) A
ND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) A
ND
> [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[vbc
ol=seagreen]
>
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 cont
ains a good discussion on enstec views and what to be wary of when using the
m. 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 ) o
f
> 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 ) f
or
> future use.
> But when we try to create the table from the last view, it just take hour
s
> 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 [S389577726
1424671822].*
> FROM T123456_2004_JAN
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S123376954
7736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFA
ULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> 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.[S123376954773670
6426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CAS
E WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE (
SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHE
RE ( (
> [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].* , ( S
ELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824
202597] [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.EMPTYVA
LU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40]
) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVA
LU
> 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 [S123376954
7879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMP
DIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATE
PART
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAM
PDIFF (
> 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 TH
EN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) TH
EN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { f
n
> 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 [PM0045
7] = 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] , [PM0
0464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM
00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [F
G00456_004] [D188_4] ,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [F
G00456_017] [D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [F
G00456_006] [D188_6] ,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [F
G00456_008] [D188_8] ,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG0
0456_023] [D188_23] ,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG
00456_015] [D188_15] ,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG
00456_018] [D188_18] ,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [F
G00456_016] [D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D
184] , [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].[PM004
60],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM004
58],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG
00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG
00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG
00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG
00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG
00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG0
0456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG
00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG
00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG0
0456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG
00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG0
0456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG0
0456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG0
0456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG
00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG
00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG0
0456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG0
0456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG0
0456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG
00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG
00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG
00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG0
0456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG
00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG
00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM004
57],
> [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].[PM004
59],
> [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 datedi
ff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff
(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr
1020]=If (If
> ('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (dated
iff(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 datedi
ff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff
(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NU
LL 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 datedi
ff(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'>[S13875350874832934
6].[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 datedi
ff(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 datedi
ff(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( 1;S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DE
FAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 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]) A
ND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) A
ND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) A
ND
> [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(&#
91;S1387535087
> 48329346].[F41])+[S138753508748329346].[F40]))+[S138753508
748329346].[F1]))+
> [S138753508748329346].[F42]))+[S138753508748329346].[F39])
)<=[dbo].[EMPTYVAL
> UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[
EMPTYVALUEHANDLE](
> Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S13875350874
8329346].[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
>
>
No comments:
Post a Comment