Merhaba arkadaşlar,

Bir SQL querymiz var:

USE [Capretto_V3]
GO
/****** Object: UserDefinedFunction [dbo].[CaprettoGuncelle] Script Date: 15-Aug-17 13:17:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CaprettoGuncelle] (@LangCode Char5,@BarcodeTypeCode Char20,@PriceGroupCode Char20, @LastNHour int )
RETURNS TABLE
AS RETURN
(

SELECT
Barcode = ISNULL((SELECT TOP 1 Barcode
FROM prItemBarcode WITH(NOLOCK)
WHERE prItemBarcode.ItemTypeCode = Product.ItemTypeCode
AND prItemBarcode.ItemCode = Product.ItemCode
AND prItemBarcode.ColorCode = Product.ColorCode
AND prItemBarcode.ItemDim1Code = Product.ItemDim1Code
AND prItemBarcode.ItemDim2Code = Product.ItemDim2Code
AND prItemBarcode.ItemDim3Code = Product.ItemDim3Code
AND prItemBarcode.BarcodeTypeCode = @BarcodeTypeCode), SPACE(0))

, Product.ItemCode
--, ProductDescription
, ColorCode = Product.ColorCode
, ItemDim1Code = Product.ItemDim1Code
, ItemDim2Code = Product.ItemDim2Code
, ItemDim3Code = Product.ItemDim3Code
, cdItemDesc.ItemDescription
, cdItem.ProductHierarchyID
, Price2 = ISNULL((SELECT TOP 1 Price FROM ProductValidPrices(GETDATE(),GETDATE())
WHERE ProductValidPrices.ItemTypeCode = 1
AND ProductValidPrices.ItemCode = prItemVariant.ItemCode
AND ProductValidPrices.ColorCode IN (prItemVariant.ColorCode, SPACE(0))
AND ProductValidPrices.ItemDim1Code = prItemVariant.ItemDim1Code
AND ProductValidPrices.PriceGroupCode = 'B2C'
AND ProductValidPrices.PaymentPlanCode = SPACE(0)
ORDER BY ProductValidPrices.ColorCode DESC
), SPACE(0))

, Price1 = ISNULL((SELECT TOP 1 Price FROM ProductValidPrices(GETDATE(),GETDATE())
WHERE ProductValidPrices.ItemTypeCode = 1
AND ProductValidPrices.ItemCode = prItemVariant.ItemCode
AND ProductValidPrices.ColorCode IN (prItemVariant.ColorCode, SPACE(0))
AND ProductValidPrices.ItemDim1Code = prItemVariant.ItemDim1Code
AND ProductValidPrices.PaymentPlanCode = 'ON'
ORDER BY ProductValidPrices.ColorCode DESC
), SPACE(0))

, WarehouseInventoryQty = ISNULL(WarehouseInv.AvailableInventory,0)


, TransferNotApprovedQty1 = ISNULL((SELECT SUM(TransferNotApprovedQty1)
FROM ItemTransferNotApprovedByDate(getdate())
WHERE ItemTransferNotApprovedByDate.ItemTypeCode = Product.ItemTypeCode
AND ItemTransferNotApprovedByDate.ItemCode = Product.ItemCode
AND ItemTransferNotApprovedByDate.ColorCode = Product.ColorCode
AND ItemTransferNotApprovedByDate.ItemDim1Code = Product.ItemDim1Code
AND ItemTransferNotApprovedByDate.ItemDim2Code = Product.ItemDim2Code
AND ItemTransferNotApprovedByDate.ItemDim3Code = Product.ItemDim3Code),0)
,prItemNotes.Notes



FROM prItemVariant AS Product WITH (NOLOCK)
--INNER JOIN prItemVariant WITH(NOLOCK)
-- ON prItemVariant.ItemCode = Product.ProductCode
-- AND prItemVariant.ItemTypeCode = Product.ProductTypeCode
INNER JOIN cdItem WITH(NOLOCK)
ON cdItem.ItemCode = Product.ItemCode
AND cdItem.ItemTypeCode = Product.ItemTypeCode


LEFT JOIN (
SELECT ItemCode,ItemTypeCode,ColorCode,ItemDim1Code,ItemD im2Code,ItemDim3Code,
SUM(AvailableInventoryQty1) AvailableInventory
FROM ItemInventory WITH (NOLOCK) where ItemInventory.WarehouseCode IN('D01','M01','D02','D03','D04')
GROUP BY ItemCode,ItemTypeCode,ColorCode,ItemDim1Code,ItemD im2Code,ItemDim3Code
) AS WarehouseInv ON WarehouseInv.ItemTypeCode = Product.ItemTypeCode
AND WarehouseInv.ItemCode = Product.ItemCode
AND WarehouseInv.ColorCode = Product.ColorCode
AND WarehouseInv.ItemDim1Code = Product.ItemDim1Code
AND WarehouseInv.ItemDim2Code = Product.ItemDim2Code
AND WarehouseInv.ItemDim3Code = Product.ItemDim3Code
LEFT OUTER JOIN prItemNotes WITH (NOLOCK) ON prItemNotes.ItemTypeCode=Product.ItemTypeCode
AND prItemNotes.ItemCode=Product.ItemCode
LEFT OUTER JOIN trStock WITH (NOLOCK) ON trStock.ItemTypeCode=Product.ItemTypeCode
AND trStock.ItemCode=Product.ItemCode
AND trStock.ItemTypeCode = Product.ItemTypeCode
AND trStock.ItemCode = Product.ItemCode
AND trStock.ColorCode = Product.ColorCode
AND trStock.ItemDim1Code = Product.ItemDim1Code
AND trStock.ItemDim2Code = Product.ItemDim2Code
AND trStock.ItemDim3Code = product.ItemDim3Code
INNER JOIN cdItemDesc WITH (NOLOCK) ON cdItemDesc.ItemTypeCode=Product.ItemTypeCode
AND cdItemDesc.ItemCode=Product.ItemCode
AND cdItemDesc.LangCode=@LangCode
LEFT OUTER JOIN trPriceListLine WITH (NOLOCK) ON trPriceListLine.ItemTypeCode=Product.ItemTypeCode
AND trPriceListLine.ItemCode=Product.ItemCode
AND trPriceListLine.ColorCode=Product.ColorCode
INNER JOIN prItemVariant ON prItemvariant.Itemcode=Product.ItemCode
AND prItemvariant.ItemTypeCode=Product.ItemTypeCode
AND prItemVariant.ItemDim1Code=Product.ItemDim1Code
AND prItemVariant.ItemDim2Code=Product.ItemDim2Code




WHERE Product.IsBlocked = 0
AND cdItem.UseInternet= 1
AND cdItem.IsBlocked = 0
AND Product.ItemTypeCode=1

AND (EXISTS (SELECT * FROM trStock WITH(NOLOCK)
WHERE trStock.ItemTypeCode = Product.ItemTypeCode
AND trStock.ItemCode = Product.ItemCode
AND trStock.ColorCode = Product.ColorCode
AND trStock.ItemDim1Code = Product.ItemDim1Code
AND trStock.ItemDim2Code = Product.ItemDim2Code
AND trStock.WarehouseCode in ('D01','M01','D02','D03','D04') --BALGAT VE MERKEZ

AND trStock.OperationDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE() )

OR (cdItem.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE() )
OR (cdItemDesc.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE())
OR (Product.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE())
OR (prItemNotes.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE())
OR (trStock.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE())
OR (trPriceListLine.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND GETDATE())
)


)

Alttaki sorguyu çalıştırdığımda satır sayısına bakmaksızın yani 10 satırda 1000 satırda 20 küsür saniye sürüyor.

SELECT ItemCode,ColorCode,ItemDim1Code,Price1,Price2,Ware houseInventoryQty FROM CaprettoGuncelle ('TR','','B2C',1) GROUP BY ItemCode,ColorCode,ItemDim1Code,Price1,Price2,Ware houseInventoryQty

yardımlarınızı bekliyorum