Reputation: 1941
I've got the following View, called ViewGoods:
SELECT
G.Gid,
SI.[$Id] AS FirstSiteInId,
SI.Date AS FirstSiteInDate,
SI.Comments AS FirstSiteInComments,
S.[$Id] AS FirstSiteId,
S.[$Refex] AS FirstSiteRefex,
SI.Client AS ClientId,
C.[$Refex] AS ClientRefex,
CASE WHEN SI.Contract IS NULL THEN (SELECT Contract.[$Id]
FROM StockType AS ST
INNER JOIN StockTypeContract AS STC ON ST.[$Id] = STC.[$ParentId]
INNER JOIN Contract ON STC.Contract = Contract.[$Id]
WHERE ST.[$Id] = VGST.StockType
AND SI.Date >= STC.StartDate)
ELSE SI.Contract END AS Contract,
CASE WHEN SI.Contract IS NULL THEN (SELECT Contract.[$Refex]
FROM StockType AS ST
INNER JOIN StockTypeContract AS STC ON ST.[$Id] = STC.[$ParentId]
INNER JOIN Contract ON STC.Contract = Contract.[$Id]
WHERE ST.[$Id] = VGST.StockType
AND SI.Date >= STC.StartDate)
ELSE CT.[$Refex] END AS ContractRefex,
CASE WHEN COALESCE (Q.Quantity, 0) > 0 THEN L.SiteId ELSE NULL END AS SiteId,
CASE WHEN COALESCE (Q.Quantity, 0) > 0 THEN L.SiteRefex ELSE NULL END AS SiteRefex,
CASE WHEN COALESCE (Q.Quantity, 0) > 0 THEN L.Lid ELSE NULL END AS Lid,
ISNULL(W.Weight, VGSA.Weight * Q.Quantity) AS Weight,
COALESCE (Q.Quantity, 0) AS Quantity,
VGSA.Article,
VGSA.ArticleName,
VGST.StockType,
VGST.StockTypeRefex
FROM dbo.Goods AS G
INNER JOIN dbo.SiteIn AS SI ON G.SiteIn = SI.[$Id]
INNER JOIN dbo.Client AS C ON C.[$Id] = SI.Client
INNER JOIN dbo.Site AS S ON SI.Site = S.[$Id]
LEFT OUTER JOIN dbo.Contract AS CT ON SI.Contract = CT.[$Id]
LEFT OUTER JOIN dbo.ViewGoodsLocation AS L ON G.Gid = L.Gid
LEFT OUTER JOIN dbo.ViewGoodsWeight AS W ON G.Gid = W.Gid
LEFT OUTER JOIN dbo.ViewGoodsQuantity AS Q ON G.Gid = Q.Gid
LEFT OUTER JOIN dbo.ViewGoodsSingleArticle AS VGSA ON G.Gid = VGSA.Gid
LEFT OUTER JOIN dbo.ViewGoodsStockType AS VGST ON VGST.Gid = G.Gid
When querying that View with the parameter Client or the parameter Lid, individually, everything runs well. But if I try to mix the two of them, the View times out with no results. Below is the query getting the timeout:
SELECT [t0].[Gid], [t0].[FirstSiteInId], [t0].[FirstSiteInDate], [t0].[FirstSiteInComments], [t0].[FirstSiteId], [t0].[FirstSiteRefex], [t0].[ClientId], [t0].[ClientRefex], [t0].[Contract], [t0].[ContractRefex], [t0].[SiteId], [t0].[SiteRefex], [t0].[Lid], [t0].[Weight], [t0].[Quantity], [t0].[Article], [t0].[ArticleName], [t0].[StockType], [t0].[StockTypeRefex]
FROM [ViewGoods] AS [t0]
WHERE ([t0].[Lid] IS NOT NULL) AND (([t0].[ClientId]) = 70)
Where have I gone wrong?
EDIT: I included here the Actual Execution Plan http://pastebin.com/PMY0aLE1 .
Upvotes: 2
Views: 340
Reputation: 41899
Uh oh......
I can see in your view definition that you are joining to at least 9 other data structures, that appear to be views as well (so there could be further table joins within these).
This is probably not the answer you are going to want to hear but if you need to join this many data structures together then something has gone wrong at design time.
My suggestion is that you go back to the drawing board and rethink the design of this database.
Edit: Additional thoughts..
Consider that when you are performing queries that regularly require you to join multiple tables, these are candidates for Indexed Views i.e. materialized structures, which are effectively tables. Regularly performing large numbers of join operations results in poor performing queries with limited scalability.
Keep in mind that Normalization is the starting point for good database design, it should not necessarily be your end point.
Upvotes: 0
Reputation: 453910
From the Query plan you posted it seems to be accessing 10 tables
Article, Client, Contract, Goods, GoodsArticle, GoodsEvent, Site, SiteIn, StockType, StockTypeContract
Are all of these actually required for your results or are any of them just artefacts of being in a view that you happen to be joining on?
There are 25 root nodes in the plan for these 10 tables so definitely some tables are being accessed more than once and it seems in quite a wasteful way.
You can see in this portion of the plan (Adds up to 40% of the cost) GoodsEvent seems to be accessed three times. I'm pretty sure if you get rid of the views that you will be able to consolidate this.
Portion of plan http://img245.imageshack.us/img245/4105/executionplan.png
I think at the moment this bit of the plan is doing something like this
SELECT Query3.Gid, Query3.SiteId, Query3.Lid, Query3.Expr1017
FROM
(
SELECT
Gid,
SUM(CASE WHEN Type ='SO' THEN -Quantity ELSE Quantity END) AS Expr1017
FROM GoodsEvent
WHERE Type IN('AQ','SI','SO') AND IsDeleted = 0
GROUP BY Gid
) Query1
JOIN
(
SELECT
Gid,
MAX(EventOn) AS Expr1014
FROM GoodsEvent
WHERE IsDeleted = 0
GROUP BY Gid
) Query2 ON Query1.GID = Query2.GID
JOIN
(
SELECT
GoodsEvent.Gid,
GoodsEvent.EventOn,
GoodsEvent.SiteId,
GoodsEvent.Lid
FROM GoodsEvent WHERE IsDeleted = 0
) Query3 ON Query3.gid=Query2.gid AND Query3.EventOn = Query2.Expr1014
It might be worth testing whether this is semantically equivalent and performs any better
;WITH X AS
(
SELECT Gid,
SiteId,
Lid,
RANK() OVER (PARTITION BY Gid ORDER BY EventOn DESC) AS RN,
Type
FROM GoodsEvent
WHERE IsDeleted = 0
)
SELECT Gid,SiteId, Lid,
SUM(CASE WHEN Type ='SO' THEN -Quantity ELSE Quantity END)
OVER(PARTITION BY Gid) AS Expr1017,
FROM X WHERE RN=1 AND Type IN('AQ','SI','SO')
Upvotes: 2
Reputation: 96650
Views should never reference other views if you want performance. This is just out and out poor design. You should not be using a view to do this. When you do this it has to completely materialize those views first before it can create the records set. So for maybe 200 final records you possibly have to callup severall billion. This will slow things down tremendously and I have found when people use this techinique that if you follow through the views all the way down to the bottom you are often calling the same data from the same table many, many times. Do NOT use a view this way. If you must use a view, then access the tables directly and do not call other views. This is a road you do not want to go down, we almost lost a mulimillion $ customer becasue someone designed this way instead of using good data access methosds.
This is a guaranteed, no way to fix it performance problem that will cause you database to eventually come to a screeching halt. It is BAD design period and must be changes as soon as humanly possible.
Upvotes: 1