Select Timeout With 2 Parameters

Question

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 .

Answer 1

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.

Answer 2

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.

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

Answer 3

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.