Showing posts with label views. Show all posts
Showing posts with label views. Show all posts

Wednesday, March 28, 2012

Poor performance querying view from sproc

We are using SQL SERVER 2005.

We have been experiencing horrible performance running select queries from sprocs against views that do outer joins, despite having optimized all of the indexes.

Our solution in these cases (and arguably not the best) has been to first select the views into a temp table and then query the temp table for the desired result set. This has greatly improved the speed of which our sprocs run, but we are looking for a better solution.

Has anybody else experienced this problem?

Thanks in advance.

Hi Eric. Would it be possible for you to post some sample code that includes some of the view definitions, table structures, and sample data scripts? It's quite tough to try and guess what the issue may be.

One thing that comes to mind would be if you selecting only a subset of the columns from the view into a temp table and then joining/selecting from that multiple times, you could be bypassing many bookmark/cluster key lookups, but that is a total shot in the dark. If you can post some scripts, that would help us try and help you. You can get the DDL for the tables/indexes/views by simply scripting the appropriate tables from SSMS/QA...

HTH,

Chad

|||

Are you joining multiple views? Or just a view that joins multiple tables?

What I would do is use a trial replacement for your current implimentation where you create the join etc (as you have in the view) in the query where you actually use the view.

I am not 100% sure how views work in MSSQL, but I hope this can be of some assistance.

|||Can you please paste your stored procedure code. I suspect it is due to the selectivity of the predicates that means you are getting a poor plan. If you can past the output of show plan that would also help.|||We have experienced exactly the same thing. Queries which work fine in 1-5 seconds on SQL 2000 SP4 either run very long or never finish in hours under SQL 2005. I have posted several posts here and never gotten any responses.

We are running SQL 2005 9.0.2153 on SQL 2003 SP1 64bit. The problem seems to be related to views with RIGHT outer joins.

Here is my duplication script, which fails on 2 different SQL 2005 installations.

-- Create Test Data Table CustomerListTom and Views

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO

CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)

INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

-- Create a big enough set of data for testing
DECLARE @.i INT
SET @.i = 1
WHILE (@.i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@.i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate + CASE WHEN @.i > 3 THEN 10 ELSE -25 END + @.i

FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

SET @.i = @.i + 1
END

-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)

ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)

GO

-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))

GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO
--return

USE AdventureWorks

-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Drop PK and run ORIGINAL query, returns in less than 4 seconds

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

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

Wednesday, March 7, 2012

Please help: Views and INSTEAD OF UPDATE trigger

Hi:

Currently I have two tables T1 and T2. A view V1 is defined over T1 with an INSTEAD OF UPDATE trigger and another view V2 is defined over V1 and T2 with another INSTEAD OF UPDATE trigger. Unfortunately, inside V2s trigger following update statement is not working:
Update V1 set V1.name = i.name from inserted i
because SQL server complains:
View 'V1' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.
Is there any way to get around this problem? I.e., how can I make the INSTEAD OF UPDATE trigger in V2 to work if I cant reference inserted?

Your help is appreciated,

JeffI think the problem is not with your "inserted" table, but with the target of the insert (View V1).

Modify your INSTEAD OF triggers so that they reference the underlying tables directly, rather than through secondary views.