October 21, 2010 at 12:19 pm
Hoping someone can help me out with a confuzzlement. I've got a call going to a massively joined view that flattens out an OLTP system for reporting. The call itself is simple, as did, I thought, the index matrix.
However, I'm getting an oddity. My filter, which reduces a 6 million rowset down to 21 rows, is occuring as the *final* activity in the execution plan, instead of at the beginning to reduce the rowset count. It is not an indexed view, so it should flow through directly to the table ddl and indexing, but for some reason is ignoring that idea.
The call to the view is simple:
SELECT *
FROM dbo.vw_inv
WHERE ACCT_ID IN ('a3880')
AND POS_LOT_EFF_DT = '2010-09-28'
Not a lot going on there except the filtration. Yeah, I know, I know, Select splat, varchar identifier, etc... I can't help that, I'm working against a final vendor system that makes that call. I can only optimize up to the view level.
The view itself is a monster.
ALTER VIEW [dbo].[vw_INV] AS
SELECT
CONVERT(CHAR(40),b.ReportHeading1) AS ACCT_ALPH_SORT_CD
,CONVERT(CHAR(12),r.PortfolioCode) AS ACCT_ID
,CONVERT(CHAR(40),ls.t_ptype) AS ACCT_INVS_STRGY_CD
,CONVERT(CHAR(40),b.ReportHeading1) AS ACCT_LN1_LNG_DS
,CONVERT(CHAR(40),b.ReportHeading2) AS ACCT_LN2_LNG_DS
,CONVERT(CHAR(40),b.ReportHeading3) AS ACCT_LN3_LNG_DS
,CONVERT(CHAR(2),'3') AS ACCT_LNG_DS_LN_QY
,CONVERT(CHAR(40),b.ReportHeading1) AS ACCT_SHRT_NM
,CONVERT(CHAR(20),p.PortfolioStatus) AS ACCT_STAT_CD
,CONVERT(DATETIME,b.CloseDate) AS ACCT_TO_BE_CLS_DT
,CONVERT(DATETIME,s.FirstcouponDate) AS ACRL_START_DT
,CONVERT(CHAR(45),cc.Custom01) AS ADMIN_EMP_ID_NO
,CONVERT(CHAR(12),b.ReportHeading2) AS ALT_ACCT_ID
,CONVERT(DECIMAL(9,4),s.AverageLife) AS AVG_LIFE
,CONVERT(DECIMAL(15,2),99.99) AS AVG_UNT_CST
,CONVERT(DECIMAL(15,2),99.99) AS BASE_ACRINC_GRS_AT
,CONVERT(DECIMAL(17,2),r.MarketValue) AS BASE_BOOK_VAL_AT
,CONVERT(CHAR(6),ls.t_ity) AS BASE_CURR_CD
,CONVERT(DECIMAL(17,2),r.TotalCost) AS BASE_EBT_CST_AT
,CONVERT(DECIMAL(17,2),99.99) AS BASE_EBT_CST_UT_AT
,CONVERT(DECIMAL(17,2),r.TotalCost) AS BASE_FED_CST_AT
,CONVERT(DECIMAL(17,2),99.99) AS BASE_FED_CST_UT_AT
,CONVERT(DECIMAL(17,2),99.99) AS BASE_MV_AT
,CONVERT(DECIMAL(1, 0),0) AS BASE_MV_UKN_CD
,CONVERT(DECIMAL(19,2),sp.ClosePrice) AS BASE_PRC_AT
,CONVERT(DATETIME,r.AsOfDate) AS BND_CALC_DT
,CONVERT(CHAR(40),bf.BrokerFirmName) AS BNK_CD
,CONVERT(DECIMAL(15,2),r.MarketValue) AS BOOK_VAL_AT
,CONVERT(CHAR(40),cc.Custom05) AS BR_CD
,CONVERT(DECIMAL(15,2),1) AS BS_FED_CUR_UGL_AT
,CONVERT(DECIMAL(1, 0),0) AS BS_FED_CST_UKN_CD
,CONVERT(DECIMAL(17,2),99.99) AS BS_FED_TOT_UGL_AT
,CONVERT(DECIMAL(1, 0),0) AS BS_FED_UGL_UKN_CD
,CONVERT(CHAR(20),p.ProcessingGroupID) AS BUS_PRCS_CL_CD
,CONVERT(CHAR(1),s.CMOPaymentTypeCode) AS CMO_IN
,CONVERT(DECIMAL(19,4),s.ConversionFactor) AS CNVT_IN --datatype change from char(1)
,CONVERT(CHAR(1),s.SymbolTypeCode) AS COMN_COLL_FND_IN
,CONVERT(CHAR(40),s.IssuecountryCode) AS CTRY_ISS_CD
,CONVERT(CHAR(40),ct.DefaultAddressCountry) AS CTRY_TAX_SITUS_CD
,CONVERT(CHAR(60),'us') AS CURR_BS_CD
,CONVERT(CHAR(40),1) AS DIVS_CD
,CONVERT(DECIMAL(15,2),r.TotalCost) AS EBT_CST_AT
,CONVERT(DATETIME,r.AsOfDate) AS EBT_CST_DT
,CONVERT(DECIMAL(15,2),99.99) AS EBT_CST_UT_AT
,CONVERT(DECIMAL(15,2),99.99) AS FED_UGL_AT
,CONVERT(CHAR(1),s.IssueCountryCode) AS FGN_ISS_IN
,CONVERT(CHAR(40),'N') AS FIX_INC_SCTR_CD --52/33 Waiting on mapping clarification
,CONVERT(CHAR(11),p.PortfolioID) AS GLP_INTRNL_ACCT_ID
,CONVERT(CHAR(11),r.PortfolioID) AS GLP_INTRNL_PORT_ID
,CONVERT(CHAR(11),s.SecurityID) AS GLP_INTRNL_POS_ID
,CONVERT(CHAR(11),s.SecurityID) AS GLP_INTRNL_SEC_ID
,CONVERT(CHAR(14),s.ShortAssetClasscode) AS GLP_POS_TP_CD
,CONVERT(CHAR(40),CASE WHEN S.SecTypeBaseCode='gp' THEN 'Y' ELSE 'N' END) AS GVT_AGCY_PP_CD
,CONVERT(CHAR(20),CASE WHEN s.PaymentFrequencyID='1' THEN 'Yearly'
WHEN s.PaymentFrequencyID='2' THEN 'Semiannual'
WHEN s.PaymentFrequencyID='4' THEN 'Quarterly'
WHEN s.PaymentFrequencyID='12' THEN 'Monthly'
ELSE 'No Accrue' END) AS INC_ACCR_RULE_CD
,CONVERT(DECIMAL(7,2),s.InterestOrDividendRate) AS INT_DIV_RATE
,CONVERT(DECIMAL(17,6),s.InterestOrDividendRate) AS INT_RT
,CONVERT(CHAR(35),s.IndustryGroupID) AS IR_CL_1_SRTKEY1_CD
,CONVERT(CHAR(40),s.SectorID) AS IR_CL_1_SRTKEY2_CD
,CONVERT(CHAR(12),s.ISIN) AS ISIN_ID
,CONVERT(CHAR(3),cur.ISOCode) AS ISO_CURR_CD
,CONVERT(CHAR(40),s.FullName) AS ISS_ALPHA_SRTKY_DS
,CONVERT(DECIMAL(17,2),sp.ClosePrice) AS ISS_CLS_PRC
,CONVERT(CHAR(10),s.CUSIP) AS ISS_CUSIP_ID
,CONVERT(CHAR(12),CASE WHEN s.CUSIP IS NOT NULL THEN S.CUSIP
WHEN s.SEDOL IS NOT NULL THEN s.SEDOL
WHEN s.ISIN IS NOT NULL THEN s.ISIN ELSE s.TICKER END) AS ISS_ID
,CONVERT(CHAR(6),COALESCE(s.Cusip,s.Sedol,s.Isin,s.Ticker,'N/A')) AS ISS_ID_CD
,CONVERT(CHAR(12),s.SEDOL) AS ISS_SEDOL_ID
,CONVERT(CHAR(40),'1') AS ISSU_LN_LNG_DS_QY
,CONVERT(CHAR(72),s.FullName) AS ISSU_LN1_LNG_DS
,CONVERT(CHAR(72),s.FullName) AS ISSU_LN1_SHRT_DS
,CONVERT(CHAR(1),'Y') AS MAINT_FED_CST_IN
,CONVERT(CHAR(1),'N') AS MAINT_LOTS_IN
,CONVERT(CHAR(4),s.DurationToMaturity) AS MAT_CAT_CD
,CONVERT(DATETIME,s.MaturityDate) AS MAT_DT
,CONVERT(CHAR(6),s.MoodyRating) AS MDYS_BND_RAT_CD
,CONVERT(CHAR(40),COALESCE(ct.isIRA,ct.isLifeInsurance,ct.isTrustFund,ct.isRetired)) AS MII_1_CD
,CONVERT(CHAR(40),c.Custom22) AS MJA_CD
,CONVERT(CHAR(40),s.IndustryGroupID) AS MJI_1_CD
,CONVERT(CHAR(40),s.SecTypeBaseCode) AS MJS_CD
,CONVERT(DECIMAL(15,2),r.MarketValue) AS MKTVAL_AT
,CONVERT(CHAR(1),'N') AS MULT_CURR_TRDG_IN
,CONVERT(DATETIME,s.MaturityDate) AS NXT_EFF_MAT_DT
,CONVERT(DATETIME,CASE WHEN ISDATE(c.Custom19) = 1 THEN c.Custom19 ELSE CONVERT(DATETIME,NULL) END) AS NXT_FISC_DT
,CONVERT(DATETIME,NextPaymentDate) AS NXT_INC_PAY_DT
,CONVERT(DECIMAL(15,2),99.99) AS OID_AT
,CONVERT(CHAR(10),s.SecTypeBaseCode) AS OPT_TP_CD
,CONVERT(CHAR(16),s.PaymentFrequencyID) AS PAY_FREQ_CD
,CONVERT(CHAR(12),s.PoolNumber) AS POOL_NO
,CONVERT(CHAR(6),t_ity) AS PORT_CURR_CD
,CONVERT(CHAR(40),b.ReportHeading1) AS PORT_NAME_DS
,CONVERT(CHAR(6),0) AS PORT_SEQ_NO
,CONVERT(CHAR(40),p.PortfolioStatus) AS PORT_STAT_CD
,CONVERT(CHAR(6),InvestmentGoal) AS PORT_TP_CD
,CONVERT(DATETIME,r.ASOfDate) AS POS_LOT_EFF_DT
,CONVERT(DECIMAL(19,4),sp.ValuationFactor) AS PRC_AS_PT_IN --datatype change from char(1)
,CONVERT(DECIMAL(19,6),sp.ClosePrice) AS PRC_AT
,CONVERT(DATETIME,sp.PriceDate) AS PRC_CONF_DT
,CONVERT(DATETIME,r.AsOfDate) AS PRC_DT
,ROW_NUMBER() OVER(ORDER BY p.PortfolioCode) AS POSN_KY_POS_ID
,CONVERT(DECIMAL(17,4),99.99) AS PST_QY
,CONVERT(DECIMAL(9,2),99.99) AS PV_PER_SHR_AT
,CONVERT(CHAR(18),s.MoodyRating) AS RAT_CAT1_CD
,CONVERT(CHAR(23),s.SPRating) AS RAT_CAT2_CD
,CONVERT(CHAR(40),s.SecTypeBaseCode) AS REGIS_CD
,CONVERT(CHAR(40),'99') AS RGN_CD
,CONVERT(DECIMAL(17,6),1) AS SCL_FCT_PR_AT
,CONVERT(CHAR(30),s.SecTypeBaseCode) AS SEC_PROC_CL_CD
,CONVERT(CHAR(1),' ') AS STALE_PRC_IN
,CONVERT(CHAR(24),s.StateCode) AS STATE_ISS_CD
,CONVERT(CHAR(24),ct.DefaultAddressStateCode) AS STATE_TAX_SITUS_CD
,CONVERT(CHAR(4),s.SPRating) AS STP_BND_RAT_CD
,CONVERT(CHAR(45),CASE WHEN i.InterestedPartyType='Invest Adv Asst' THEN i.ContactID END) AS SUBADVR_EMP_ID_NO
,CONVERT(CHAR(12),s.Ticker) AS TKR_CD
,CONVERT(DECIMAL(17,2),sp.ClosePrice) AS UNT_MKT_VAL
,CONVERT(CHAR(1),CASE WHEN R.MarketValue = 0 THEN 'Y' ELSE 'N' END) AS WRTHL_ISS_IN
,CONVERT(CHAR(12),s.Symbol) AS WTC_ISS_ID
,CONVERT(DECIMAL(17,6),YTMOnMarket) AS YLD_TO_MAT_AT
,CONVERT(DECIMAL(7,2),s.DurationToMaturity) AS YRS_TO_MAT_QY
,CONVERT(DECIMAL(17,6),s.YTMOnCost) AS YTM_AT_COST_AT
FROM dbo.PortSecAgg r WITH (NOLOCK)
LEFT JOIN vSecurity s WITH (NOLOCK) ON r.SecurityID = s.SecurityID--
LEFT JOIN vSecurityPrice sp WITH (NOLOCK) on sp.SecurityID = s.SecurityID and sp.PriceDate = r.AsOfDate
LEFT JOIN dbo.vPortfolioBase b WITH (NOLOCK) on b.PortfolioBaseID = r.PortfolioID
LEFT JOIN dbo.vPortfolio p WITH (NOLOCK) on p.PortfolioID = b.PortfolioBaseID
LEFT JOIN dbo.vPortfolioLabels ls WITH (NOLOCK) on r.PortfolioID = ls.PortfolioID
LEFT JOIN dbo.vContact ct WITH (NOLOCK) ON p.OwnerContactID = ct.ContactID
LEFT JOIN dbo.vContactCustom cc WITH (NOLOCK) ON p.PrimaryContactID = cc.ContactID
LEFT JOIN dbo.vBrokerFirm f ON b.PortfolioBaseID = f.BrokerFirmID
lEFT JOIN (SELECT BrokerFirmID
,BrokerFirmName
FROM dbo.vPortfolioLabels l WITH (NOLOCK)
JOIN dbo.vBrokerFirm b on t_cust = BrokerFirmName
WHERE t_cust is not null)bf on f.BrokerFirmID = bf.BrokerFirmID
LEFT JOIN dbo.vCurrency cur WITH (NOLOCK) ON s.PrincipalCurrencyCode = cur.CurrencyCode
LEFT JOIN dbo.vPortfolioBaseCustom c WITH (NOLOCK) ON b.PortfolioBaseID = c.PortfolioBaseID
LEFT JOIN dbo.vPortfolioInterestedParty i WITH (NOLOCK) ON i.portfolioID = p.portfolioID AND i.ContactID = ct.ContactID
The specific table in question is the core table that everything hangs from, the PortSecAgg table.
The DDL for this table in particular:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PortSecAgg](
[PortfolioID] [int] NOT NULL,
[SecurityID] [int] NOT NULL,
[AsOfDate] [datetime] NOT NULL,
[IsShortPosition] [bit] NOT NULL,
[PortfolioCode] [varchar](10) NULL,
[Symbol] [varchar](20) NULL,
[Quantity] [numeric](18, 4) NULL,
[SecTypeCode] [varchar](5) NULL,
[Cusip] [varchar](15) NULL,
[UnitCost] [money] NULL,
[TotalCost] [money] NULL,
[Price] [money] NULL,
[MarketValue] [money] NULL,
[AccruedInterest] [money] NULL,
[UnrealizedGL] [money] NULL,
[IsSubAccount] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Index [idx_PortSecAgg_portfolioCode_AsOfDate] Script Date: 10/21/2010 11:12:17 ******/
CREATE CLUSTERED INDEX [idx_PortSecAgg_portfolioCode_AsOfDate] ON [dbo].[PortSecAgg]
(
[AsOfDate] ASC,
[PortfolioCode] ASC,
[PortfolioID] ASC,
[SecurityID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
The rest of the indexing makes sense and it hangs off all the ID fields that traverse the system, including the occassional date range (all dates are continuous dates with no backfill, so they're reasonable as a key, they always go in at the tail of the index).
Attached, find the execution plan. Of specific note is the filter occuring in the final component. I need to understand why it's skipping the clustered index specifically built to allow it to seek on that before processing the rest of the view, and how to force it to behave itself so that the rest of my indexes seek instead of scan. It's boggling me as to the choice the optimizer is making here.
I know the 'price' is in the 73% sort that's its doing randomly near the tail of the plan, but I believe that almost all of the pain in this query can be dealt with by getting it to seek in the first place properly.
Thanks in advance.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 12:53 pm
A bit of additional information I neglected to include in the above discussion:
If I take the select statement directly out of the view and append the where clause directly to it, I get sub-second runtimes instead of the 10+ minutes I get from the select * from View WHERE... structure. The view object itself is what's confusing the optimizer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 1:35 pm
Instead of Select *
try listing the columns.
Run both in profiler and see if they are different plans.
Greg E
October 21, 2010 at 1:41 pm
Both excellent ideas, and unfortunately went there already.
In regards to the first one I tried using count(*) instead of any column list to make sure I wasn't dealing with a data traffic issue. Same issue. Well, I get an extra aggregation stream at the end.
In regards to profiler, I definately have different plans, and can post the sqlplan of the subsecond query that doesn't use the view for those interested. It seeks properly against the where clause and then the resultant scans on the rest of the tables are proper seeks.
Now, one of the things I thought might be causing a problem is all the columns are converted in some way outbound from this view. Removing the conversions though on those two columns (the two in the where clause) so it would simply pass through instead of possibly waiting until after because of the conversion effects did not correct the problem.
The two lines in question:
--,CONVERT(CHAR(12),r.PortfolioCode) AS ACCT_ID
--,CONVERT(DATETIME,r.ASOfDate) AS POS_LOT_EFF_DT
I modified to:
,r.PortfolioCode AS ACCT_ID
,r.ASOfDate AS POS_LOT_EFF_DT
To make sure that the convert statements weren't the interference point. While I'm sure they weren't helping, I get the same plan still.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 2:13 pm
Found the problem.
This specific line:
,ROW_NUMBER() OVER(ORDER BY p.PortfolioCode) AS POSN_KY_POS_ID
is causing the entire problem. Remove that line (and only that line, the converts were not causing an issue), and the execution plan works as expected, seeks down the line.
I can only assume that this is because the view wants to figure out the row_number() for EVERY portfolioCode... first... and then use the where from the calling query. Ugh.
Well, thanks for anyone who wasted some brainpower on this one. Appreciate the attempts.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 2:14 pm
<Guess>
Looking at the exec plan - the Sort is taking 73% and this seems related to the following:
,ROW_NUMBER() OVER(ORDER BY p.PortfolioCode) AS POSN_KY_POS_ID
Is there any difference if this is removed from the view?
</Guess>
October 21, 2010 at 2:15 pm
Well - happy to know I guessed right... 😀
October 21, 2010 at 2:22 pm
Sorry I missed your comment of * part way down.
When I copied your select statement to just have a look,
this L is actually upside down, although you don't really see it here.
lEFT JOIN ( SELECT BrokerFirmID
Very odd.
I wonder if changing your clustered index to have the Account ID first, then the date, and also use = not IN.
Leaving for the weekend - or rather making a long weekend :-D, hope you get some better help before I get back.
Greg E
SELECT *
FROM dbo.vw_inv
WHERE ACCT_ID IN ('a3880')
AND POS_LOT_EFF_DT = '2010-09-28'
[AsOfDate] ASC,
[PortfolioCode] ASC,
[PortfolioID] ASC,
[SecurityID] ASC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply