September 27, 2006 at 8:33 pm
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
September 27, 2006 at 11:05 pm
Yeah, the GETDATE() comparison is problemmatic, but not because it repeatedly executes GETDATE(). The problem is it does a DATEDIFF against Process_Date... for each value. The better approach would be to use dateadd() against GETDATE()... i.e.: WHERE DATEADD(mm, -15, GETDATE()) >= Process_Date. This also has the added advantage that if there is an index on Procecss_Date the query processing can be used.
But yes, the unions, joins, etc... they get nasty pretty quickly.
September 27, 2006 at 11:05 pm
Yeah, the GETDATE() comparison is problemmatic, but not because it repeatedly executes GETDATE(). The problem is it does a DATEDIFF against Process_Date... for each value. The better approach would be to use dateadd() against GETDATE()... i.e.: WHERE DATEADD(mm, -15, GETDATE()) >= Process_Date. This also has the added advantage that if there is an index on Procecss_Date the query processing can be used.
But yes, the unions, joins, etc... they get nasty pretty quickly.
September 28, 2006 at 8:19 am
Do you see much benefit in assigning GetDate() to a variable and having the query reference the variable instead of invoking GetDate() per record?
September 28, 2006 at 9:50 am
They query processor won't invoke GETDATE() for each record. It will do it once. The bigger problem is the DATEDIFF function which forces math to be done between the saved GETDATE() value and the datetime column. If you use DATEADD() against GETDATE() it will do that calculation once, store the result, and then can compare it against the datetime value. An index on that column would help that part of the query perform better with this approach.
September 28, 2006 at 9:56 am
Thanks. As for Getdate() I think I'm confusing its query performance with that of user-defined functions.
September 28, 2006 at 10:47 am
Yeah, SQL Server is pretty good about identifying functions which need to evaluate and produce a single result per line (NEWID, ISNULL) and a function which will produce a static result (GETDATE, SUSER_SNAME). For the latter, it will almost always try to grab the value once and re-use it rather than re-evaluating the function.
September 29, 2006 at 2:27 am
cast(Null as varchar(20) ?
Seems odd. Do you need to use nulls at all? Better avoided. Empty strings or zeros should work.
I'd prefer to assign DATEADD(... GETDATE()) to a variable so as to keep the code clearer, just as a matter of maintainability. Easier to comment it as well.
September 29, 2006 at 7:40 am
The subselect is not necessary, get rid of it, as is the UNION statement get rid of it and add your INNER JOINS directly to your initial SELECT statement. This would allow you to get rid of the NULL columns that I believe are there for the UNION clause. The extra ) should go to.
September 29, 2006 at 7:49 am
Here's the actual code. One look and you'll know why the developer is experiencing performance problems.
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
-- Below are all of the views and tables reference directly or indirectly by the insert statement above
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)
)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply