Poor Performance - Nested Views & Complex Joins

The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, DaveYou've been told wrong.

There is nothing bad about nested subquery. They, like any coding technique, should not be used unnecesarilly, but the optimizer will incorporate them in its query plan. You can try eliminating the nested UNION subquery, but you will need to link the account and TransactionalData tables into both YTD tables. This may allow you to better benefit from indexing, but will incur twice as many table scans. So whether this ends up making your code more efficient depends heavily upon your data. You just have to try it and test for yourself.

In your code, GETDATE() will not be executed for every record. It will only be executed once at the start of the query. You WILL, however, lose any benefit of an index on Process_Date by using this syntax: WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)Use this syntax instead:WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)

Other problems? Dump the "SELECT *" and enumerator your columns. "SELECT *" is sloppy programming and has not business in production code.|||Nested views are not derived tables...there's a major differences...and yes avoid netsed views...BUT

FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans

There is no good reason to do this|||I think Blindman may have meant to use

WHERE Process_Date > DATEadd(mm, -15, GETDATE())

instead of

WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)|||OK, I see it now

FROM ( SELECT *
FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

First I would normalize the tables..YTD0xx tables should all be 1 table with a type of...whatever that stuff means..I'm guessing month?

Also, and I'm not sure, bu this might cause a scan: RIGHT(trans.Acnt, 5)

Why are the element size different? Did you overload 1 column with additional data instead of normalizing it into 2 columns?|||Thanks all. The design is definitely something that needs to be looked at.

I'm still not in favor of nested views. Most articles I've read about the pros and cons of nested views indicate a major con is how deeply nested they can become. While SQL Server does expand the views to their base table representation, this does create a certain amount of extra work behind the scenes that is not reported as part of the execution plan. I've also seen many times where developers find a view that comes close to meeting their needs and creates another view off of this view without taking the time to research if the first view is the base view. Several times this summer I've been asked to troubleshoot poor server performance where the CPUs are over 95% utilized. I ended up finding the source of the problem was a nested view. The execution plan revealed over 100-150 icons containing multiple table scans and involving 6-10 other views and multiple base tables all the result of one select statement off of a nested view. I know the argument can be made the real problem was a poorly designed nested view and not the use of nested views itself, however, at least in my experience, most people don't take the time needed to carefully analyze existing views before using them. I'm not even going to touch on the difficulty in troubleshooting performance problems caused by poorly designed nested views since I've typed too much already. Sorry about that.

Dave|||Dave why don't you post the ddl of the tables, sample data and expecxted results.

Read the hint sticky at the top of the board|||I think Blindman may have meant to use

WHERE Process_Date > DATEadd(mm, -15, GETDATE())

instead of

WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)

Yes. Copy/paste error. Thanks for catching it.

And yes, nested VIEWS are bad, if only because they serve more to obfuscate code than to clarify it.|||Unless I'm just WAY off base, the sample you posted isn't syntactically correct, so I'm not about to try to fix it until we get thing clarified. Simply reformatting what you posted, I get:SELECT
trans.Entry_Code, trans.D_C, trans.ADP_Security_#
, trans.TRID, trans.Batch_Code, trans.Last_Money
, null as Shares, Settle_date as Process_Date, null as Closing_Price
, trans.Dwnld_Date, trans.Acnt, null as Mktval
, cast(Null as varchar(20)) as Cusip_#
, ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.AcntI guarantee you that won't work. Can you re-examine what you're doing, and cut-and-paste the actual code for us to help you fix?

-PatP|||Here's the actual code. Thanks

Insert Into actimize_Data.dbo.transactions (Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date)
SELECT Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date
FROM view_Transactions_1YR

CREATE VIEW dbo.view_Transactions_1YR
AS
SELECT Process_Date AS Transaction_Date_Time,
Transaction_Type AS Trans_Type_Cd,
RIGHT(Acnt, 5) AS Account_Key,
case WHEN last_money = 0.0
THEN Mktval * (CASE WHEN D_C = 'd' THEN - 1
ELSE 1 END)
ELSE Last_Money end as Local_Currency_Amount,
'USD' AS Local_Currency_Cd,
Dwnld_Date AS Row_Insert_Date
FROM dbo.view_TransactionsData_1YR trans
LEFT OUTER JOIN dbo.tbl_Transaction_Table
ON trans.Transaction_Type = dbo.tbl_Transaction_Table.Trans_Type_Cd
WHERE (dbo.tbl_Transaction_Table.Trans_Table = 'transactions')

CREATE VIEW dbo.view_TransactionsData_1YR
AS
SELECT transactions.Entry_Code, transactions.D_C,
transactions.ADP_Security_#,
dbo.getTransType(transactions.TRID,
transactions.Entry_Code, transactions.Batch_code,
transactions.D_C, transactions.ADP_Security_#,
transactions.Last_Money, transactions.Shares) AS Transaction_Type,
transactions.Process_Date, transactions.Shares,
transactions.Closing_Price, transactions.Dwnld_Date,
transactions.Acnt, transactions.Last_Money,
transactions.Mktval, transactions.Cusip_#, transactions.TRID
FROM (Select * from view_WBTransactions_DataIn_1YR
UNION
Select * from view_WBMargin_DataIn_1YR) transactions
WHERE (isnull(transactions.Entry_Code,'') NOT IN ('AIM', 'ALL', 'CEF', 'FED', 'FND', 'MMR', 'PPS', 'REI', 'WBR'))
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'NW3' ANd Batch_code ='3N')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CON' ANd Batch_code ='RG')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'JNL' ANd Batch_code ='MF')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CHK' ANd Batch_code ='MN')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'ADJ' ANd Batch_code ='AS')
And ( Batch_code <> 'RG')
And ( Batch_code <> 'OT')

CREATE TABLE [tbl_Transaction_Table] (
[Trans_Type_Cd] [varchar] (50) NOT NULL ,
[Trans_Table] [varchar] (50) NULL ,
CONSTRAINT [PK_tbl_Transaction_Table] PRIMARY KEY CLUSTERED
([Trans_Type_Cd]
) )

CREATE VIEW dbo.view_WBTransactions_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_#, trans.TRID,
trans.Batch_code, trans.Last_Money, trans.Shares,
trans.Process_Date, trans.Closing_Price, trans.Dwnld_Date,
trans.Acnt, trans.Mktval, trans.Cusip_#, ACTIMIZE_DATA.dbo.account.account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

CREATE VIEW dbo.view_WBMargin_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# , trans.TRID,
trans.Batch_Code, trans.Last_Money, null as Shares,
Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACTIMIZE_DATA.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBMRGN.dbo.YTD05M
WHERE (DATEDIFF(mm, Dwnld_Date, GETDATE()) <= 14)
UNION
SELECT * FROM ADPDBMRGN.dbo.YTD06M) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN dbo.tbl_Accounts_TransactionalData
ON trans.Acnt = dbo.tbl_Accounts_TransactionalData.Acnt

CREATE TABLE [YTD05B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)

CREATE TABLE [YTD06B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)

CREATE TABLE [account] (
[account_key] [varchar] (50) NOT NULL ,
[organization_key] [varchar] (50) NULL ,
[branch_key] [varchar] (50) NULL ,
[primary_representative_key] [varchar] (50) NULL ,
[split_key] [varchar] (50) NULL ,
[acct_num] [varchar] (50) NULL ,
[acct_first_name] [varchar] (50) NULL ,
[acct_middle_name] [varchar] (50) NULL ,
[acct_last_name] [varchar] (50) NULL ,
[acct_maiden_name] [varchar] (50) NULL ,
[acct_other_name] [varchar] (50) NULL ,
[acct_type_cd] [varchar] (50) NULL ,
[acct_classification_cd] [varchar] (50) NULL CONSTRAINT [DF_account_acct_classification_cd] DEFAULT ('N/A'),
[primary_party_key] [varchar] (50) NULL ,
[acct_open_date] [datetime] NULL ,
[acct_close_date] [datetime] NULL ,
[last_review_date] [datetime] NULL ,
[sector_cd] [varchar] (50) NULL ,
[investment_objective_cd] [varchar] (50) NULL ,
[risk_tolerance_cd] [varchar] (50) NULL ,
[credit_limit_local_currrency_value] [float] NULL ,
[stated_liquid_net_worth] [float] NULL ,
[stated_total_net_worth] [float] NULL ,
[stated_account_activity] [float] NULL ,
[stated_account_value] [float] NULL ,
[is_suspect] [bit] NULL ,
[is_dvp_rvp] [bit] NULL ,
[is_active] [bit] NULL ,
[is_online_account] [bit] NULL ,
[avg_balance] [float] NULL ,
[verification_status] [varchar] (50) NULL ,
[verification_date] [datetime] NULL ,
[household_id] [varchar] (50) NULL ,
[is_proprietary] [bit] NULL ,
[is_street_side] [bit] NULL ,
[is_error_account] [bit] NULL ,
[is_institutional] [bit] NULL ,
[is_employee_account] [bit] NULL ,
[is_tax_deferred] [bit] NULL ,
[is_single_joint] [bit] NULL ,
[is_fee_based_acct] [bit] NULL ,
[is_anonymous_acct] [bit] NULL ,
[is_option_approved] [bit] NULL ,
[option_approval_level_cd] [varchar] (50) NULL ,
[is_discretionary] [bit] NULL ,
[managed_acct_type] [varchar] (50) NULL ,
[row_update_date] [datetime] NULL ,
CONSTRAINT [pk_account] PRIMARY KEY CLUSTERED
(
[account_key]
) )

CREATE TABLE [tbl_Accounts_TransactionalData] (
[Acnt] [varchar] (9) NOT NULL ,
CONSTRAINT [PK_tbl_Accounts_TransactionalData] PRIMARY KEY CLUSTERED
(
[Acnt]
))

CREATE TABLE [YTD05M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL
)

CREATE TABLE [YTD06M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6)
)