Hello this is my first time here . Have started as a dev trainee and one of my first jobs has me stuck … I am using server 2008 r2 . Tsql . . . have looked at the estimated exec plan for a query and traced it back to this function . . . .. Could anyone give me a tip on how to increase performance . . Code BELOW . . . Thanks heaps in advance … Im a total beginner on a very big learning curve
Im wondering if it has somthing to do with the way it was written ( maybe the self joins . all of the join fields are indexed . . I may e wrong but think it may have somthing to do with the first sub select . . . .
CREATE FUNCTION [dbo].[fGetStockTakePackNotOnSystem]
(
@StockTakeID INT
)
RETURNS INT
AS
BEGIN
RETURN
(
SELECT
COUNT(dbo.tStockTakeStock.StockCode) AS PackNotOnSystem
FROM
dbo.tStockTake
INNER JOIN
dbo.tStockTakePreCount
ON dbo.tStockTakePreCount.StockTakeID = dbo.tStockTake.StockTakeID
INNER JOIN
dbo.tStockTakeStock
ON dbo.tStockTakeStock.StockTakePreCountID = dbo.tStockTakePreCount.StockTakePreCountID
INNER JOIN
dbo.tStockType
ON dbo.tStockType.StockTypeID = dbo.tStockTakePreCount.StockTypeID
LEFT JOIN
dbo.tStock
ON dbo.tStock.StockCode = dbo.tStockTakeStock.StockCode
–LEFT JOIN
— dbo.tProduct
–ON dbo.tProduct.ProductID = dbo.tStock.ProductID
LEFT JOIN
(SELECT
StockSnapShotID,
StockCode,
StockID,
StockLocationID,
tStockStatus.StockStatusID
FROM
dbo.tStockHistory
INNER JOIN
dbo.tStockSnapShotItem
ON dbo.tStockSnapShotItem.StockHistoryID = dbo.tStockHistory.StockhistoryID
INNER JOIN
dbo.tStockStatus
ON dbo.tStockStatus.StockStatusID = dbo.tStockHistory.StockStatusID
WHERE
dbo.tStockStatus.IsTransferred = 0
AND
dbo.tStockStatus.IsInTransit = 0
) SS
ON SS.StockSnapShotID = dbo.tStockTake.StockSnapShotID
AND
SS.StockCode = dbo.tStockTakeStock.StockCode
LEFT JOIN
(SELECT
tStock.StockCode,
tStockHistory.Area AS CurrentArea,
tStockHistory.Row AS CurrentRow,
tStockStatus.StockStatusName
FROM
dbo.tStock
INNER JOIN
dbo.tStockHistory
ON tStockHistory.StockHistoryID = tStock.StockHistoryCurrentID
INNER JOIN
dbo.tStockStatus
ON dbo.tStockStatus.StockStatusID = dbo.tStockHistory.StockStatusID
)SHDetails
ON SHDetails.StockCode = dbo.tStockTakeStock.StockCode
WHERE
dbo.tStockTake.StockTakeID = @StockTakeID
AND
(
SS.StockCode IS NULL
OR
SS.StockLocationID IS NULL
)
)
END
GO
