December 4, 2007 at 1:52 pm
Greetings,
I am having a problem with a view that I have been working with (not my initial mess). The problem being that when I execute the SQL of the view it returns its data within 58 seconds, however when I execute a 'select * from badview' it takes 22 minutes.
Any initial pointers for starting to debug this mess? I just cant figure out how if every part of the view works reasonably, why the view as a whole is fubar'd.
thanks,
Chris
December 4, 2007 at 2:04 pm
Well - for your first pointer - why not post the code? Preferrably with the DDL of the tables involved? It sounds like there might be a few moving parts in the query, so generic advice is not likely to get you very far.
Otherwise:
- where are you running the "select * from badview" from? different server? remote machine? the server itself?
- how much data are we talking about? in the raw tables? in the end result view? rows? bytes?
- what does the execution plan look like?
- what indexes to do you have?
- when is the last time the indexes were rebuilt? statistics updated?
- is the server exhibiting any signs of being stressed resource-wise (you know the signs - frequent coffee/smoke breaks, etc...?)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2007 at 7:04 am
You're right of course, I ran out of time before I could do a proper post (should of just deleted it)
SELECT TOP (2147483647)
Portfolio= PortfolioCode
,[NAME]= TraderName
,[SECT]= CompType1
,CUSIP= SecurityIdentifier
,[TRD_DATE]= SystemTradeDate
,AMT= Quantity
,LONG_DESC= SecurityLongDescription
,S_P= SPRating
,MDY= MoodyRating
,FITCH= FitchRating
,COUP= Coupon
,EFF_MAT= EffectiveMaturity
,MAT_DATE= MaturityDate
,WAL= WeightedAverageLifeInYears
,P_DUR= CompDuration
,OAS_DUR= CompOASDuration
,CNVX= CompConvexity
,MKT_YLD= CompMarketYield
,BK_YLD= CompBookYield
,AVE_COST= CumulativeAverageCost
,BK_PRICE= CompBookPrice
,MKT_PRICE= CompMarketPrice
,BK_VALUE= CompBookValue
,MKT_VALUE= CompMarketValue
,UNREALIZED_GL= CompUnrealizedGL
,Position
,Subsector= COALESCE(l.Subsector,p.CompType2)
,Comp_Type_2= CompType2
,p.Ticker
,DuffPhelps
,[State]
,StatePledgedQuantity
,OverrideDate= Price_Date1
,OverridePrice= COALESCE(Price1,0.00)
,OverrideMktYld= COALESCE(Yield_Market1,0.00)
,OverrideDurMod= COALESCE(Duration_Modified1,0.00)
,OverrideDurEff= COALESCE(Duration_Effective1,0.00)
,OverrideCnvx= COALESCE(Convexity_Effective1,0.00)
,OverrideSource= Source1
,BloombergPriceDate= Price_Date2
,BloombergPrice= COALESCE(Price2,0.00)
,BloombergDurMod= COALESCE(Duration_Modified2,0.00)
,BloombergDurEff= COALESCE(Duration_Effective2,0.00)
,BloombergSource= Source2
,RestrictionTypeID
FROM Comp.dbo.vwRestrictedPPP p
LEFT JOIN Comp.dbo.vwLehmanPCAggSectorsByCusip l
ON p.SecurityIdentifier = l.CUSIP
LEFT JOIN (
SELECT *
FROM (
SELECT Security_Identifier1
,Price_Date1
,Price1 = Price
,Yield_Market1 = MarketYield
,Duration_Modified1 = ModifiedDuration
,Duration_Effective1 = EffectiveDuration
,Convexity_Effective1 = EffectiveConvexity
,Source1 = 'Trade Desk Override'
FROM Comp.dbo.Price a2
INNER JOIN Comp.dbo.Security s
ON a2.SecurityID = S.ID
INNER JOIN (
SELECT Security_Identifier1 = Identifier
,Price_Date1 = MAX(AsOfDate)
FROM Comp.dbo.Price p
INNER JOIN Comp.dbo.Security s
ON p.SecurityID = S.ID
WHERE PriceSourceID = 7
GROUP BY Identifier
) a1
ON s.Identifier = a1.Security_Identifier1
AND a2.AsOfDate = a1.Price_Date1
WHERE a2.PriceSourceID = 7
) d
INNER JOIN (
SELECT Security_Identifier2
,Price_Date2
,Price2 = Price
,Yield_Market2 = MarketYield
,Duration_Modified2 = ModifiedDuration
,Duration_Effective2 = EffectiveDuration
,Convexity_Effective2 = EffectiveConvexity
,Source2 = 'Bloomberg'
FROM Comp.dbo.Price a2
INNER JOIN Comp.dbo.Security s
ON a2.SecurityID = S.ID
INNER JOIN (
SELECT Security_Identifier2 = Identifier
,Price_Date2 = MAX(AsOfDate)
FROM Comp.dbo.Price p
INNER JOIN Comp.dbo.Security s
ON p.SecurityID = S.ID
WHERE PriceSourceID = 1
GROUP BY Identifier
) a1
ON s.Identifier = a1.Security_Identifier2
AND a2.AsOfDate = a1.Price_Date2
WHERE a2.PriceSourceID = 1
) b
ON d.Security_Identifier1 = b.Security_Identifier2
) li
ON p.SecurityIdentifier = li.Security_Identifier1
ORDER BY PortfolioCode, p.SecurityIdentifier
Indexes are rebuilt on a weekly basis along with stats.
The 'price' table is roughly 14m rows
the 'security' table is roughly 20k rows
I cant really post the vwLehmanPCAggSectorsByCusip or vwRestrictedPPP but they have been solid fast running views for ages
I did run the stats io on for a run returning 1 record
Table 'Security'. Scan count 25, logical reads 2795, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Price'. Scan count 10, logical reads 141385, physical reads 78, read-ahead reads 50131, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 68, logical reads 1310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RestrictionType'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Portfolio'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RestrictedSecurity'. Scan count 5, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Industry'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BloombergFTPSecurityMaster'. Scan count 15, logical reads 2488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LehmanPCAggDetail'. Scan count 39252, logical reads 261432, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CompPricedPortfolio'. Scan count 313, logical reads 7094, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
the server is running SQL2005, full service packs. 4gb memory, MS Windows 2003 sp2.
Now it could just be the 'price' table. running on a different but identially configured server but with 500k records instead of 14m returns the full result set in 8 seconds.
Approx. 33k rows returned
All involved tables have a primary key setup as a clustered index and non-clusted indexes on other key search/fk columns.
No frequent coffee breaks for the machine... roughly serving up 5-10 people at any given time and even when this view is being called the CPU doesn't break a sweat over 20%
execution plan:
|--Compute Scalar(DEFINE: ([Expr1018]=CASE WHEN [Expr1014] IS NOT NULL THEN [Expr1014] ELSE (0) END, [Expr1087]=CASE WHEN [Expr1053] IS NOT NULL THEN CONVERT_IMPLICIT(nvarchar(255),[Expr1053],0) ELSE [Strategist].[dbo].[compPricedPortfolio].[compType2] as [ppp].[compType2] END, [Expr1088]=CASE WHEN [comp].[dbo].[Price].[Price] as [a2].[Price] IS NOT NULL THEN [comp].[dbo].[Price].[Price] as [a2].[Price] ELSE (0.000000000000000e+000) END, [Expr1089]=CASE WHEN [comp].[dbo].[Price].[MarketYield] as [a2].[MarketYield] IS NOT NULL THEN [comp].[dbo].[Price].[MarketYield] as [a2].[MarketYield] ELSE (0.000000000000000e+000) END, [Expr1090]=CASE WHEN [comp].[dbo].[Price].[ModifiedDuration] as [a2].[ModifiedDuration] IS NOT NULL THEN [comp].[dbo].[Price].[ModifiedDuration] as [a2].[ModifiedDuration] ELSE (0.000000000000000e+000) END, [Expr1091]=CASE WHEN [comp].[dbo].[Price].[EffectiveDuration] as [a2].[EffectiveDuration] IS NOT NULL THEN [comp].[dbo].[Price].[EffectiveDuration] as [a2].[EffectiveDuration] ELSE (0.000000000000000e+000) END, [Expr1092]=CASE WHEN [comp].[dbo].[Price].[EffectiveConvexity] as [a2].[EffectiveConvexity] IS NOT NULL THEN [comp].[dbo].[Price].[EffectiveConvexity] as [a2].[EffectiveConvexity] ELSE (0.000000000000000e+000) END, [Expr1093]=CASE WHEN [Expr1083] IS NOT NULL THEN [Expr1083] ELSE (0.000000000000000e+000) END, [Expr1094]=CASE WHEN [Expr1084] IS NOT NULL THEN [Expr1084] ELSE (0.000000000000000e+000) END, [Expr1095]=CASE WHEN [Expr1085] IS NOT NULL THEN [Expr1085] ELSE (0.000000000000000e+000) END))
|--Top(TOP EXPRESSION: ((2147483647)))
|--Parallelism(Gather Streams, ORDER BY: ([p].[DisplayName] ASC, [ppp].[SecurityIdentifier] ASC))
|--Sort(TOP 2147483647, ORDER BY: ([p].[DisplayName] ASC, [ppp].[SecurityIdentifier] ASC))
|--Merge Join(Right Outer Join, MANY-TO-MANY MERGE: ([Expr1135])=([ppp].[SecurityIdentifier]), RESIDUAL: ([Strategist].[dbo].[compPricedPortfolio].[SecurityIdentifier] as [ppp].[SecurityIdentifier]=[Expr1135]))
|--Compute Scalar(DEFINE: ([Expr1062]=[Expr1062], [Expr1081]='Trade Desk Override', [Expr1072]=[Expr1072], [Expr1086]='Bloomberg'))
| |--Compute Scalar(DEFINE: ([a2].[Price]=[comp].[dbo].[Price].[Price] as [a2].[Price], [a2].[MarketYield]=[comp].[dbo].[Price].[MarketYield] as [a2].[MarketYield], [a2].[ModifiedDuration]=[comp].[dbo].[Price].[ModifiedDuration] as [a2].[ModifiedDuration], [a2].[EffectiveDuration]=[comp].[dbo].[Price].[EffectiveDuration] as [a2].[EffectiveDuration], [a2].[EffectiveConvexity]=[comp].[dbo].[Price].[EffectiveConvexity] as [a2].[EffectiveConvexity]))
| |--Sort(ORDER BY: ([Expr1135] ASC))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1135]))
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([a2].[ID], [Expr1233]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES: (.[ID], [Expr1062], [Expr1232]) WITH UNORDERED PREFETCH)
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE: (.[Identifier])=(.[Identifier]), RESIDUAL: ([comp].[dbo].[Security].[Identifier] as .[Identifier]=[comp].[dbo].[Security].[Identifier] as .[Identifier]))
| | | |--Compute Scalar(DEFINE: ([a2].[Price]=[comp].[dbo].[Price].[Price] as [a2].[Price], [a2].[ModifiedDuration]=[comp].[dbo].[Price].[ModifiedDuration] as [a2].[ModifiedDuration], [a2].[EffectiveDuration]=[comp].[dbo].[Price].[EffectiveDuration] as [a2].[EffectiveDuration]))
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier]), ORDER BY: (.[Identifier] ASC))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([a2].[ID], [Expr1231]) WITH ORDERED PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: (.[ID], [Expr1072], [Expr1230]) WITH ORDERED PREFETCH)
| | | | | |--Merge Join(Inner Join, MERGE: (.[Identifier])=(.[Identifier]), RESIDUAL: ([comp].[dbo].[Security].[Identifier] as .[Identifier]=[comp].[dbo].[Security].[Identifier] as .[Identifier]))
| | | | | | |--Merge Join(Inner Join, MERGE: (.[Identifier])=(.[Identifier]), RESIDUAL: ([comp].[dbo].[Security].[Identifier] as .[Identifier]=[comp].[dbo].[Security].[Identifier] as .[Identifier]))
| | | | | | | |--Stream Aggregate(GROUP BY: (.[Identifier]) DEFINE: ([Expr1072]=MAX([partialagg1226])))
| | | | | | | | |--Sort(ORDER BY: (.[Identifier] ASC))
| | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier]))
| | | | | | | | |--Hash Match(Inner Join, HASH: (.[ID])=([p].[SecurityID]))
| | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[ID]))
| | | | | | | | | |--Index Scan(OBJECT: ([comp].[dbo].[Security].[IDX_Security__Identifier] AS ))
| | | | | | | | |--Hash Match(Aggregate, HASH: ([p].[SecurityID]) DEFINE: ([partialagg1226]=MAX([comp].[dbo].[Price].[AsOfDate] as [p].[AsOfDate])))
| | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[SecurityID]))
| | | | | | | | |--Index Scan(OBJECT: ([comp].[dbo].[Price].[IX_Price_SecurityID_PriceSourceID_AsOfDate] AS [p]), WHERE: ([comp].[dbo].[Price].[PriceSourceID] as [p].[PriceSourceID]=(1)))
| | | | | | | |--Compute Scalar(DEFINE: ([Expr1135]=CONVERT_IMPLICIT(nvarchar(30),[comp].[dbo].[Security].[Identifier] as .[Identifier],0)))
| | | | | | | |--Stream Aggregate(GROUP BY: (.[Identifier]) DEFINE: ([Expr1062]=MAX([partialagg1225])))
| | | | | | | |--Sort(ORDER BY: (.[Identifier] ASC))
| | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier]))
| | | | | | | |--Hash Match(Inner Join, HASH: ([p].[SecurityID])=(.[ID]))
| | | | | | | |--Bitmap(HASH: ([p].[SecurityID]), DEFINE: ([Bitmap1229]))
| | | | | | | | |--Hash Match(Aggregate, HASH: ([p].[SecurityID]) DEFINE: ([partialagg1225]=MAX([comp].[dbo].[Price].[AsOfDate] as [p].[AsOfDate])))
| | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[SecurityID]))
| | | | | | | | |--Index Scan(OBJECT: ([comp].[dbo].[Price].[IX_Price_SecurityID_PriceSourceID_AsOfDate] AS [p]), WHERE: ([comp].[dbo].[Price].[PriceSourceID] as [p].[PriceSourceID]=(7)))
| | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[ID]), WHERE: (PROBE([Bitmap1229])=TRUE) [IN ROW])
| | | | | | | |--Index Scan(OBJECT: ([comp].[dbo].[Security].[IDX_Security__Identifier] AS ))
| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier]), ORDER BY: (.[Identifier] ASC))
| | | | | | |--Index Scan(OBJECT: ([comp].[dbo].[Security].[IDX_Security__Identifier] AS ), ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT: ([comp].[dbo].[Price].[IX_Price_SecurityID_PriceSourceID_AsOfDate] AS [a2]), SEEK: ([a2].[SecurityID]=[comp].[dbo].[Security].[ID] as .[ID] AND [a2].[PriceSourceID]=(1) AND [a2].[AsOfDate]=[Expr1072]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT: ([comp].[dbo].[Price].[PK_Price_ID] AS [a2]), SEEK: ([a2].[ID]=[comp].[dbo].[Price].[ID] as [a2].[ID]) LOOKUP ORDERED FORWARD)
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier]), ORDER BY: (.[Identifier] ASC))
| | | |--Index Scan(OBJECT: ([comp].[dbo].[Security].[IDX_Security__Identifier] AS ), ORDERED FORWARD)
| | |--Index Seek(OBJECT: ([comp].[dbo].[Price].[IX_Price_SecurityID_PriceSourceID_AsOfDate] AS [a2]), SEEK: ([a2].[SecurityID]=[comp].[dbo].[Security].[ID] as .[ID] AND [a2].[PriceSourceID]=(7) AND [a2].[AsOfDate]=[Expr1062]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT: ([comp].[dbo].[Price].[PK_Price_ID] AS [a2]), SEEK: ([a2].[ID]=[comp].[dbo].[Price].[ID] as [a2].[ID]) LOOKUP ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([ppp].[SecurityIdentifier]), ORDER BY: ([ppp].[SecurityIdentifier] ASC))
|--Merge Join(Left Outer Join, MANY-TO-MANY MERGE: ([ppp].[SecurityIdentifier])=([Expr1136]), RESIDUAL: ([Strategist].[dbo].[compPricedPortfolio].[SecurityIdentifier] as [ppp].[SecurityIdentifier]=[Expr1136]))
|--Merge Join(Left Outer Join, MANY-TO-MANY MERGE: ([ppp].[SecurityIdentifier])=([Expr1137]), RESIDUAL: ([Strategist].[dbo].[compPricedPortfolio].[SecurityIdentifier] as [ppp].[SecurityIdentifier]=[Expr1137] AND [Strategist].[dbo].[compPricedPortfolio].[PortfolioID] as [ppp].[PortfolioID]=[comp].[dbo].[RestrictedSecurity].[PortfolioID] as [rs].[PortfolioID]))
| |--Sort(ORDER BY: ([ppp].[SecurityIdentifier] ASC))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([ppp].[SecurityIdentifier]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([p].[ID], [Expr1234]) WITH UNORDERED PREFETCH)
| | |--Index Scan(OBJECT: ([comp].[dbo].[Portfolio].[IX_Portfolio_ID_DisplayName] AS [p]), ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([Strategist].[dbo].[compPricedPortfolio].[PK_compPricedPortfolio] AS [ppp]), SEEK: ([ppp].[PortfolioID]=[comp].[dbo].[Portfolio].[ID] as [p].[ID]) ORDERED FORWARD)
| |--Sort(ORDER BY: ([Expr1137] ASC))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1137]))
| |--Compute Scalar(DEFINE: ([Expr1014]=[Expr1014], [Expr1137]=CONVERT_IMPLICIT(nvarchar(30),[comp].[dbo].[Security].[Identifier] as .[Identifier],0)))
| |--Stream Aggregate(GROUP BY: (.[Identifier], [rs].[PortfolioID]) DEFINE: ([Expr1014]=MAX([comp].[dbo].[RestrictionType].[ID] as [rt].[ID])))
| |--Sort(ORDER BY: (.[Identifier] ASC, [rs].[PortfolioID] ASC))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Identifier], [p].[ID]))
| |--Merge Join(Inner Join, MERGE: ([p].[ID])=([rs].[PortfolioID]), RESIDUAL: ([comp].[dbo].[RestrictedSecurity].[PortfolioID] as [rs].[PortfolioID]=[comp].[dbo].[Portfolio].[ID] as [p].[ID]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[ID]), ORDER BY: ([p].[ID] ASC))
| | |--Index Scan(OBJECT: ([comp].[dbo].[Portfolio].[IX_Portfolio_ID_DisplayName] AS [p]), ORDERED FORWARD)
| |--Sort(ORDER BY: ([rs].[PortfolioID] ASC))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([rs].[PortfolioID]))
| |--Hash Match(Inner Join, HASH: ([rs].[SecurityID])=(.[ID]))
| |--Bitmap(HASH: ([rs].[SecurityID]), DEFINE: ([Bitmap1235]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([rs].[SecurityID]))
| | |--Hash Match(Inner Join, HASH: ([rt].[ID])=([rs].[RestrictionTypeID]))
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Index Scan(OBJECT: ([comp].[dbo].[RestrictionType].[idxRESTRICTION_TYPE_ID] AS [rt]))
| | |--Clustered Index Scan(OBJECT: ([comp].[dbo].[RestrictedSecurity].[PK_RESTRICTED_SECURITIES_ID] AS [rs]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[ID]), WHERE: (PROBE([Bitmap1235])=TRUE) [IN ROW])
| |--Index Scan(OBJECT: ([comp].[dbo].[Security].[IDX_Security__Identifier] AS ))
|--Sort(ORDER BY: ([Expr1136] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1136]))
|--Compute Scalar(DEFINE: ([Expr1136]=CONVERT_IMPLICIT(nvarchar(12),[comp].[dbo].[BloombergFTPSecurityMaster].[SecurityIdentifier] as .[SecurityIdentifier],0)))
|--Sort(DISTINCT ORDER BY: (.[SecurityIdentifier] ASC, [Expr1052] ASC, [Expr1053] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[SecurityIdentifier], [Expr1052], [Expr1053]))
|--Compute Scalar(DEFINE: ([Expr1052]=CASE WHEN [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] as .[Ticker] IS NOT NULL THEN [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] as .[Ticker] ELSE substring([Expr1041],(1),(6)) END, [Expr1053]=CONVERT(varchar(25),CASE WHEN CASE WHEN [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] as .[Ticker] IS NOT NULL THEN [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] as .[Ticker] ELSE substring([Expr1041],(1),(6)) END='SLMA' THEN 'Finance Companies' ELSE CASE WHEN ltrim(substring([comp].[dbo].[BloombergFTPSecurityMaster].[SecurityIdentifier] as .[SecurityIdentifier],(1),(6)))='36966R' THEN 'Finance Companies' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='Banking' THEN 'Banking' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='BROKERAGE' THEN 'Brokerage' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='CAPITAL_GOODS' THEN 'Capital Goods' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='COMMUNICATIONS' THEN 'Communication' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='CONSUMER_CYCLICAL' THEN 'Consumer Cyclical' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='CONSUMER_NON_CYCLICAL' THEN 'Consumer Non-Cyclical' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='ELECTRIC' THEN 'Electric' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='ENERGY' THEN 'Energy' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='INDUSTRIAL_OTHER' THEN 'Industrial Other' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='INSURANCE' THEN 'Insurance' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL THEN [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] ELSE [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] END='FOREIGN_LOCAL_GOVERNMENTS' OR [comp].[dbo].[BloombergFTPSecurityMaster].[ProductGroup] as .[ProductGroup]=(3) THEN 'Foreign Local Governments' ELSE CASE WHEN CASE WHEN [comp].[dbo].[LehmanPCAg..
|--Hash Match(Left Outer Join, HASH: ([Expr1138])=([Expr1041]), RESIDUAL: ([Expr1138]=[Expr1041]))
|--Bitmap(HASH: ([Expr1138]), DEFINE: ([Bitmap1243]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1138]))
| |--Hash Match(Left Outer Join, HASH: (.[Ticker], [Expr1138])=([comp].[dbo].[BloombergFTPSecurityMaster].[Ticker], [p].[CUSIP]), RESIDUAL: ([comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] as .[Ticker]=[comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] AND [Expr1138]=[comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP]))
| |--Bitmap(HASH: (.[Ticker], [Expr1138]), DEFINE: ([Bitmap1239]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[Ticker], [Expr1138]))
| | |--Hash Match(Right Outer Join, HASH: ([ind].[ID])=(.[IndustryType]), RESIDUAL: ([comp].[dbo].[BloombergFTPSecurityMaster].[IndustryType] as .[IndustryType]=[comp].[dbo].[Industry].[ID] as [ind].[ID]))
| | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS: ([ind].[ID]))
| | | |--Clustered Index Scan(OBJECT: ([comp].[dbo].[Industry].[PK_Industry_ID] AS [ind]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: (.[IndustryType]))
| | |--Compute Scalar(DEFINE: ([Expr1138]=substring([comp].[dbo].[BloombergFTPSecurityMaster].[SecurityIdentifier] as .[SecurityIdentifier],(1),(8))))
| | |--Clustered Index Scan(OBJECT: ([comp].[dbo].[BloombergFTPSecurityMaster].[PK_BloombergFTPSecurityMaster] AS ), WHERE: ([comp].[dbo].[BloombergFTPSecurityMaster].[ProductGroup] as .[ProductGroup]=(8) OR [comp].[dbo].[BloombergFTPSecurityMaster].[ProductGroup] as .[ProductGroup]=(7) AND [comp].[dbo].[BloombergFTPSecurityMaster].[CountryCode] as .[CountryCode]<>(1) OR [comp].[dbo].[BloombergFTPSecurityMaster].[ProductGroup] as .[ProductGroup]=(3) AND ([comp].[dbo].[BloombergFTPSecurityMaster].[TaxStatusCode] as .[TaxStatusCode]='E' OR [comp].[dbo].[BloombergFTPSecurityMaster].[TaxStatusCode] as .[TaxStatusCode]='F' OR [comp].[dbo].[BloombergFTPSecurityMaster].[TaxStatusCode] as .[TaxStatusCode]='P' OR [comp].[dbo].[BloombergFTPSecurityMaster].[TaxStatusCode] as .[TaxStatusCode]='Q' OR [comp].[dbo].[BloombergFTPSecurityMaster].[TaxStatusCode] as .[TaxStatusCode]='Y')))
| |--Compute Scalar(DEFINE: ([p].[ClassTier]=[comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([comp].[dbo].[BloombergFTPSecurityMaster].[Ticker], [p].[CUSIP]), WHERE: (PROBE([Bitmap1239])=TRUE))
| |--Hash Match(Inner Join, HASH: ([Expr1026])=([p].[CUSIP]), RESIDUAL: ([comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP]=[Expr1026]))
| |--Bitmap(HASH: ([Expr1026]), DEFINE: ([Bitmap1238]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1026]))
| | |--Hash Match(Aggregate, HASH: ([Expr1026], [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]), RESIDUAL: ([Expr1026] = [Expr1026] AND [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] = [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1026], [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]))
| | |--Compute Scalar(DEFINE: ([Expr1026]=substring([comp].[dbo].[BloombergFTPSecurityMaster].[SecurityIdentifier],(1),(8))))
| | |--Index Scan(OBJECT: ([comp].[dbo].[BloombergFTPSecurityMaster].[IX_BloombergFTPSecurityMaster_SecurityID_BloombergID_Ticker]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[CUSIP]), WHERE: (PROBE([Bitmap1238])=TRUE))
| |--Hash Match(Aggregate, HASH: ([p].[CUSIP], [p].[ClassTier], [p].[Classification]), RESIDUAL: ([comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP] = [comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP] AND [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] = [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] AND [comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification] = [comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[CUSIP], [p].[ClassTier], [p].[Classification]))
| |--Compute Scalar(DEFINE: ([p].[CUSIP]=[comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP], [p].[ClassTier]=[comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier], [p].[Classification]=[comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification]))
| |--Filter(WHERE: ([comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL))
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([p].[ID], [Expr1237]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([d].[CUSIP], [Expr1031], [Expr1236]) OPTIMIZED WITH UNORDERED PREFETCH)
| | |--Stream Aggregate(GROUP BY: ([d].[CUSIP]) DEFINE: ([Expr1031]=MAX([partialagg1169])))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([d].[CUSIP]), ORDER BY: ([d].[CUSIP] ASC))
| | | |--Stream Aggregate(GROUP BY: ([d].[CUSIP]) DEFINE: ([partialagg1169]=MAX([comp].[dbo].[LehmanPCAggDetail].[LastUpdateDate] as [d].[LastUpdateDate])))
| | | |--Index Scan(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[IDX_LehmanPCAggDetail_Cusip_Last_Update_Date] AS [d]), ORDERED FORWARD)
| | |--Index Seek(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[IDX_LehmanPCAggDetail_Cusip_Last_Update_Date] AS [p]), SEEK: ([p].[CUSIP]=[comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [d].[CUSIP] AND [p].[LastUpdateDate]=[Expr1031]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[PK_LehmanPCAggDetail] AS [p]), SEEK: ([p].[ID]=[comp].[dbo].[LehmanPCAggDetail].[ID] as [p].[ID]) LOOKUP ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1041]), WHERE: (PROBE([Bitmap1243])=TRUE))
|--Sort(DISTINCT ORDER BY: ([Expr1041] ASC, [p].[ClassTier] ASC, [p].[Classification] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1041], [p].[ClassTier], [p].[Classification]))
|--Compute Scalar(DEFINE: ([Expr1041]=[Expr1041], [p].[ClassTier]=[comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier]))
|--Hash Match(Inner Join, HASH: ([Expr1041])=([p].[CUSIP]), RESIDUAL: ([comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP]=[Expr1041]))
|--Bitmap(HASH: ([Expr1041]), DEFINE: ([Bitmap1242]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1041]))
| |--Hash Match(Aggregate, HASH: ([Expr1041], [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]), RESIDUAL: ([Expr1041] = [Expr1041] AND [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker] = [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([Expr1041], [comp].[dbo].[BloombergFTPSecurityMaster].[Ticker]))
| |--Compute Scalar(DEFINE: ([Expr1041]=substring([comp].[dbo].[BloombergFTPSecurityMaster].[SecurityIdentifier],(1),(8))))
| |--Index Scan(OBJECT: ([comp].[dbo].[BloombergFTPSecurityMaster].[IX_BloombergFTPSecurityMaster_SecurityID_BloombergID_Ticker]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[CUSIP]), WHERE: (PROBE([Bitmap1242])=TRUE))
|--Hash Match(Aggregate, HASH: ([p].[CUSIP], [p].[ClassTier], [p].[Classification]), RESIDUAL: ([comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP] = [comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP] AND [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] = [comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] AND [comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification] = [comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([p].[CUSIP], [p].[ClassTier], [p].[Classification]))
|--Compute Scalar(DEFINE: ([p].[CUSIP]=[comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [p].[CUSIP], [p].[ClassTier]=[comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier], [p].[Classification]=[comp].[dbo].[LehmanPCAggDetail].[Classification] as [p].[Classification]))
|--Filter(WHERE: ([comp].[dbo].[LehmanPCAggDetail].[ClassTier] as [p].[ClassTier] IS NOT NULL))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([p].[ID], [Expr1241]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([d].[CUSIP], [Expr1046], [Expr1240]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Stream Aggregate(GROUP BY: ([d].[CUSIP]) DEFINE: ([Expr1046]=MAX([partialagg1170])))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([d].[CUSIP]), ORDER BY: ([d].[CUSIP] ASC))
| | |--Stream Aggregate(GROUP BY: ([d].[CUSIP]) DEFINE: ([partialagg1170]=MAX([comp].[dbo].[LehmanPCAggDetail].[LastUpdateDate] as [d].[LastUpdateDate])))
| | |--Index Scan(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[IDX_LehmanPCAggDetail_Cusip_Last_Update_Date] AS [d]), ORDERED FORWARD)
| |--Index Seek(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[IDX_LehmanPCAggDetail_Cusip_Last_Update_Date] AS [p]), SEEK: ([p].[CUSIP]=[comp].[dbo].[LehmanPCAggDetail].[CUSIP] as [d].[CUSIP] AND [p].[LastUpdateDate]=[Expr1046]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT: ([comp].[dbo].[LehmanPCAggDetail].[PK_LehmanPCAggDetail] AS [p]), SEEK: ([p].[ID]=[comp].[dbo].[LehmanPCAggDetail].[ID] as [p].[ID]) LOOKUP ORDERED FORWARD)
Now one big question I have is on the system where using less data returns in 8 seconds, the executaion plan is WAY different looking and smaller. All of the SQL is identical however, the database was copied over about 2 weeks ago and nobody has made any changes.
Any clues on it or did folks collective eyes roll back into their heads (I think mine did already)
thanks,
Chris
December 5, 2007 at 12:45 pm
The index scan on table [Price] is likely to be expensive with 14m rows.
Ideally you'd want an index seek.
Can you create additional indices on these tables ?
Also, I see the use of SELECT TOP with an ORDER BY.
Sql2K lets you get away with this but it is unsupported and not recommended.
A view should never contain an ORDER BY, and using TOP to force the issue
means this will not upgrade to future versions of SQL without breaking.
December 5, 2007 at 1:36 pm
The merge joins seem to be killing you. They're occurring because of the "many to many" merge joins, which are causing multiple (apparently very large) worktables be created in your tempDB. You're doing roughly the SAME operation 2 times (all based on multiple joins from PRICE), and joining those derived tables together. Derived tables are great, until they get really big, because they're roughly equivalent to large, unindexed tables (i.e. very inefficient to join to ).
The fact that these scans are being run (10 times over) against seomthing 28 times bigger (and the resulting datasets no doubt much bigger as well) has apparently pushed you into slowpoke territory.
How long does THIS take? How many rows does it return?
SELECT Security_Identifier2
,Price_Date2
,Price2 = Price
,Yield_Market2 = MarketYield
,Duration_Modified2 = ModifiedDuration
,Duration_Effective2 = EffectiveDuration
,Convexity_Effective2 = EffectiveConvexity
,Source2 = 'Bloomberg'
FROM Comp.dbo.Price a2
INNER JOIN Comp.dbo.Security s
ON a2.SecurityID = S.ID
INNER JOIN (
SELECT Security_Identifier2 = Identifier
,Price_Date2 = MAX(AsOfDate)
FROM Comp.dbo.Price p
INNER JOIN Comp.dbo.Security s
ON p.SecurityID = S.ID
WHERE PriceSourceID = 1
GROUP BY Identifier
) a1
ON s.Identifier = a1.Security_Identifier2
AND a2.AsOfDate = a1.Price_Date2
WHERE a2.PriceSourceID = 1
Also - try inserting that to a temp table and measure time?
Just curious
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2007 at 1:57 pm
Do you have the definition of the following 2 views?
Comp.dbo.vwRestrictedPPP
Comp.dbo.vwLehmanPCAggSectorsByCusip
I'll second the dislike of the top and order by. Ordering should only be done in the final select statement that reads from the view, not in the definition of the view.
In terms of IO, these 2 tables seem to be the worst offenders
Table 'Price'. Scan count 10, logical reads 141385
Table 'LehmanPCAggDetail'. Scan count 39252, logical reads 261432
Both are getting an index scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2007 at 2:38 pm
I removed the order by... that was a throwback to bad SQL2000 habits
I can't really post the other two views...
the script snipet returned 46k rows. It completed in about 45 seconds. Inserting and selecting from a #tmptable returned results in same average amount of time (ran 5 times)
The biggest boggle I have is why doing a select * from view so much more expensive than just running the code? That is how we've solved the issue for the moment, the where the view is called in a stored procedure, we just replaced it with the actual code.
thanks,
Chris
December 5, 2007 at 3:49 pm
I'm sure I know the answer to this...but there's no chance of maintaining a "most current" boolean flag on the security prices, correct (after all - you don't seem to much care WHEN the price updated, just pull the most recent one)? going to get that over and over again using an aggregate function seems to be a LOT of work....
I know, I know....but one can dream, right?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2007 at 11:01 pm
First thing I'd try is to recompile all the views involved... could be you have a bad execution plan cached up somewhere.
I've run into this problem with "views of views" before... can't really explain it except that it seems that the "interior" views sometimes need to fully materialize before the outer view can use their info... I fixed it by writing a single view. Seems to be especially true if the "interior" views contain aggragates and/or outer joins. That would possibly explain why your code runs fine as a script, but not as a view.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2007 at 7:03 am
Ill try the recompile when I get in the office (stuck in traffic on blackberry atm). That would explain why the execution plan looks seriously different on the 2nd server where it runs fine(withalot less data involved)
thanks,
Chris
December 6, 2007 at 7:32 am
Jeff Moden (12/5/2007)
First thing I'd try is to recompile all the views involved... could be you have a bad execution plan cached up somewhere.I've run into this problem with "views of views" before... can't really explain it except that it seems that the "interior" views sometimes need to fully materialize before the outer view can use their info... I fixed it by writing a single view. Seems to be especially true if the "interior" views contain aggragates and/or outer joins. That would possibly explain why your code runs fine as a script, but not as a view.
That was also compounded when the TOP predicate was put in with a larger number of rows than what the query was going to put out. You did remove that as well, correct?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 7:46 am
I will once again agree with Jeff on this one - I too have seen exactly the same situation before.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2007 at 8:45 am
GilaMonster (12/5/2007)
I'll second the dislike of the top and order by. Ordering should only be done in the final select statement that reads from the view, not in the definition of the view.
It was a cheat... we are loading the results of the view directly into an excel spreadsheet. When it was originally coded nobody knew how to get the ordering to properly show up so the columns were sorted.
It's being adressed and people are being appropriately flogged. :w00t:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply