Networks

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

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.

Thread display: Collapse - | Expand +

All Answers

Related Discussions

Related Forums