Re: Performance of Nested Views

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 07/16/04

Next message: Jacco Schalkwijk: "Re: changing default drives for data and logs"
Previous message: Dan D.: "changing default drives for data and logs"
In reply to: Abraham: "Performance of Nested Views"
Next in thread: Jamie: "RE: Performance of Nested Views"
Messages sorted by: [ date ] [ thread ]

Date: Fri, 16 Jul 2004 13:27:27 +0100

User Defined Functions can be very bad for performance. Have you tried to
replace the functions with straight SQL? From the names of the functions I
assume that they are rather straightforward.

-- 
Jacco Schalkwijk
SQL Server MVP
"Abraham" <binu_ca@yahoo.com> wrote in message
news:%23A4n9FraEHA.4048@TK2MSFTNGP10.phx.gbl...
> In Our application there is a method which contain a series ( around 7 )
of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce
I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method )
for
> future use.
> But when we try to create the table from  the last view, it just take
hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
> Any help really appreciated .
> Thanks
> Binu
>
> Below the queries I use.
> table T123456_2004_JAN has got  5,000,000 records.
>
> CREATE VIEW [dbo].[S1233769547736706426] AS SELECT
[S3895777261424671822].*
> FROM T123456_2004_JAN
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT
[S1233769547736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
> [B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
> [B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END
[PM00462]
> FROM DBO.[S1233769547767957187] [A]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40]
,CHAR(0) ) }
> = { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
> [B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597]
[A]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547912175445] AS SELECT
[S1233769547879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE
WHEN
> { fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
> TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END )
END )
> [PM00464] FROM DBO.[S1233769547879852187]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
> [S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
> FROM DBO.[S1233769547912175445]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
> [S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
> [PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
> DBO.[S1233769547N1268698629]
>
> --Earlier below statement is an --- select into statement .....
>
> CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
> [S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT (
NUMERIC
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END
[PM00467]
> FROM DBO.[S1233769547N1213049039]
>
>
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
>
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4]
,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017]
[D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6]
,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8]
,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23]
,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15]
,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18]
,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016]
[D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187]
,
> [PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
> DBO.[S1233769547N1177270046]
>
>
>
>
>
> Below the estimated execution Plan:
>
>  |--Table Insert(OBJECT:([S1387535081666676858]),
> SET:([S1387535081666676858].[D167]=[Expr1022],
> [S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
> [S1387535081666676858].[D192]=[Expr1019],
> [S1387535081666676858].[F41]=[S138753508748329346].[F41],
> [S1387535081666676858].[F1]=[S138753508748329346].[F1],
> [S1387535081666676858].[F39]=[S138753508748329346].[F39],
> [S1387535081666676858].[F40]=[S138753508748329346].[F40],
> [S1387535081666676858].[F42]=[S138753508748329346].[F42],
> [S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
> [S1387535081666676858].[D193]=[Expr1002]))
>        |--Top(ROWCOUNT est 0)
>             |--Compute Scalar(DEFINE:([Expr1019]=If ('May  1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May  1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May  1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May  1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May  1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
> ('May  1 2004 12:00AM'>[S138753508748329346].[F41]) then If
(datediff(month,
> [S138753508748329346].[F41], 'May  1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May  1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May  1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May  1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
> round([Expr1018]/Convert(If ('May  1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May  1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May  1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May  1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May  1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
> [Expr1021]=round(110*If ('May  1 2004
12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May  1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May  1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May  1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May  1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May  1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
> [Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
> Convert(round(110*If ('May  1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May  1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May  1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May  1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May  1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May  1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May  1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May  1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
> [Expr1018], 10, NULL)))
>                  |--Table Spool
>                       |--Compute
> Scalar(DEFINE:([Expr1018]=isnull([Expr1016], 0.000)))
>                            |--Nested Loops(Inner Join, OUTER
> REFERENCES:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39]))
>                                 |--Compute
>
Scalar(DEFINE:([Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
>                                 |    |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
>                                 |         |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
>                                 |         |--Row Count Spool
>                                 |              |--Constant Scan
>                                 |--Hash Match(Cache,
> HASH:([S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
> RESIDUAL:(((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
> [S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
> [ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
>                                      |--Compute
Scalar(DEFINE:([Expr1016]=If
> ([Expr1151]=0) then NULL else [Expr1152]))
>                                           |--Stream
> Aggregate(DEFINE:([Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
> [Expr1152]=SUM([S138753508748329346].[PM00458])))
>                                                |--Nested Loops(Inner Join,
> WHERE:([S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES:([S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
>
> |--Filter(WHERE:(isnull([S138753508748329346].[F40],
> Convert(char(0)))>[S138753508748329346].[F40] OR
> (isnull([S138753508748329346].[F40],
> Convert(char(0)))=[S138753508748329346].[F40] AND
>
[dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
>
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
>
48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
>
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
>
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
>
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
> )+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S13875350874
83
> 29346].[F42]))+[S138753508748329346].[F39])))))
>                                                     |    |--Index
> Spool(SEEK:([S138753508748329346].[F39]=[S138753508748329346].[F39]))
>                                                     |         |--Table
> Scan(OBJECT:([SynComp_MOP].[dbo].[S138753508748329346]))
>                                                     |--Row Count Spool
>                                                          |--Constant Scan
>
>

Next message: Jacco Schalkwijk: "Re: changing default drives for data and logs"
Previous message: Dan D.: "changing default drives for data and logs"
In reply to: Abraham: "Performance of Nested Views"
Next in thread: Jamie: "RE: Performance of Nested Views"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

RE: Backups have Shadow Copy Problems
... Active backup destination: File ... Reason: The process cannot access the file because it is being used by ... Warning: Unable to open "C:\Documents and Settings\Administrator\Local ... Warning: Unable to open "C:\Program Files\Microsoft SQL ...
(microsoft.public.windows.server.sbs)
RE: Backups have Shadow Copy Problems
... recovery model of the SQL Server database to Simple. ... Warning: Unable to open "C:\Program Files\Microsoft SQL ... Reason: The process cannot access the file because it is being used by ... Backup completed on 2/14/2006 at 3:39 PM. ...
(microsoft.public.windows.server.sbs)
[Full-Disclosure] RE: SQL Slammer doing the rounds again?
... I was assuming that this was the only reason the poor netadmin ... the web designers and their choices; I can't speak to the issues ... Security Business Unit ... SQL Slammer doing the rounds again? ...
(Full-Disclosure)
Re: Backup erros
... You may have a SQL ... Error 800423f4 appears in the backup log file when you back up a volume by ... > Reason: The process cannot access the file because it is being used by> another process. ...
(microsoft.public.windows.server.sbs)
RE: Backups have Shadow Copy Problems
... Warning: Unable to open "C:\Program Files\Microsoft SQL ... Reason: The process cannot access the file because it is being used by ... Backup completed on 2/14/2006 at 3:39 PM. ...
(microsoft.public.windows.server.sbs)