Question

Locked

Help ! Need to speed up this sql function

By adSoft ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Back to Networks Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums