Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: 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, Dave

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'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:
    Code:
    WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
    Use this syntax instead:
    Code:
    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.
    Last edited by blindman; 09-28-06 at 09:37.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nested views are not derived tables...there's a major differences...and yes avoid netsed views...BUT

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think Blindman may have meant to use
    Code:
    WHERE	Process_Date > DATEadd(mm, -15, GETDATE())
    instead of
    Code:
    WHERE	(DATEDIFF(mm, Process_Date, GETDATE()) <= 15)

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, I see it now

    Code:
    	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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Posts
    95
    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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MCrowley
    I think Blindman may have meant to use
    Code:
    WHERE	Process_Date > DATEadd(mm, -15, GETDATE())
    instead of
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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
    I 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

  10. #10
    Join Date
    Jan 2003
    Posts
    95
    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)
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •