Performance problems with Nested SCOPE statements

I'm hoping someone has run across this particular issue and/or has identified some acceptable alternatives:

In using some scope statements to alter the behavior for certain measures, I've determined that when scope statements are nested, they will impact performance dramtically. For example

Scope [Measures].[myMeasure];
Scope Leaves( [Periods] ); -- Approx 100 members
Scope Leaves( [Markets] ); -- Approx 1500 members
This = { some simple calculation }
End scope;
End scope;
End scope;

NB. A 4th dimension, Products has about 450,000 members

At connect time, this statement is re-evaluated. The result is a long connect time. If the scope statement is removed for either Periods of Markets, the connection is immeadiate, but of course the results are wrong. Order of the scope statements doesn't affect the outcome (the [Measure] scope must be first).

I understand the difference in scale (100 or 1500 vs 150,000) but so far, the results seem to be out of line with reasonable expectations. (< 1 second vs ~65 seconds).

NB. The techqiue, causes the calculation to be evaluated at the product level, then SUM'ed over the Periods and Markets. All levels of Product aggregation use / evaluate the same calculation.

Thoughts & suggestions?

It probably depends on what is in your {some simple calculation}. There may be an alternative approach which would be faster, but it's impossible to say without more details. I don't think its the nesting of scopes that is the issue, it's more related to the size of the sub cube over which you are performing the assignment.

The same thing could be expressed as a single scope statement, but I would not expect this to change the performance at all.

Scope (Leaves([Periods]) * Leaves([Markets]));

([Measures].[myMeasure]) = "some simple calculation";

End Scope;

|||

Thanks for your feedback.

First, you're right that changing the "form" of the scope statements doesn't change the performance characteritics. (Already tried that). Indeed, the problem seems to the the size / scope of the sub cube being accessed.

The performance characteritics is indeed linked to the complexity of the calculation. For example, if I use this = 1; (the simplest possible expression), the connect returns immeadiately.

In this case, my target expression is:

this = Iif( [myMeasure], 1, null)

Essentually, this "resets" the expression to 1, for every level of the product attributes / hierarchies, then aggregates the result within stores and periods.

My basic problem is how to stop this calculation from occuring "during the connect" and instead have it invoked / calculated when the measure is actually used (when I'm willing to take the performance hit). It's easy to see / test, since in VS, I simply browse the cube and reconnect and watch Task Manager on the server. (It jumps to 100% for 60 seconds or so).

NB. The server in question is a x64, 4 way, dual core Opteron (2.6 Ghz) with 20 Gig of memory, running Enterprise edition (SP2). Everything is running 64 bit code.

|||

Found the answer, so I thought I'd post it for other people who might trip across this issue:

The problem actually exists in the front end "tool", not in Analysis Services. In this case, I was testing / debugging with Visual Studio, making small modifications to the calculation, then quickly browsing.

Turns out VS issues the following statement shortly after connecting:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [myCube]

This statement causes all of the facts to be referenced / resolved, including the measure in question. Therefore, a full bottom up calculation is performed. The query only ends after 60 seconds, because VS uses a 60 second timeout. (No wonder all of my tests came out the same!)

I can only speculate, that the reason VS does this, is to "warm" the cache, to make the subsequent queries faster. Results (and error messages) are discarded.

NB. For the record, this particular expression isn't intended to be used at the "higher levels". But, since I've isolated the key performance issue, at least alternatives can no be developed.

|||Glad you found the issue. I tend to test more complicated stuff with queries in SSMS, rather than using the browser, so I have not hit this issue myself. The OWC browser in VS does not really have a reputation for sending the "best" MDX.

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
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
>
>