How to speed up store procedure[sqlserver2005-420 Lines]

[sivacak]

Hello,

I have wrote a stored procedure[Sqlserver2005] but its real slow. Its activated by a
dropdownlist[asp] on web page but its takes to long to process.
How to access faster in asp pages.

CREATE PROCEDURE [dbo].[sp_storefrontshoppingcategoryGetproductlistbyManufacturermodel_v3] 
( 
@aintEnterpriseId INT=0,
@aintStoreId INT=0,
@aintUserId INT=0,
@aintProductCategoryId INT=0,
@aintProductCategoryFilterId INT=0,
@aintUserHasQuotedPricing TINYINT=0,
@astrUserProdCatFilter VARCHAR(30)='',
@aintUserPricingLevelID INT=0,
@adecStorePoint2DollarRatio DECIMAL(11,2)=1,
@aintCurrentPageNum TINYINT=1,
@aintNumberOfItemsPerPage INT=35,
@astrManufacturer VARCHAR(50)='',
@astrModel VARCHAR(50)='',
@aintSortby TINYINT=0,
@aintTotalItems int=0 out
)
AS
BEGIN
SET NOCOUNT ON
-- -----------------
-- PRODUCT ATTRIBUTES
-- 1 QtyMinimumEnforced
-- 4 ASSET_TAG_PRODUCT_QTY_MIN
-- 5 ASSET_TAG_PRODUCT_QTY_MAX
-- -----------------
--------------------------------------------------
--Declarations of local variables
--------------------------------------------------
DECLARE @intProductInMultipleCategoriesEnabled TINYINT
DECLARE @ubound INT
DECLARE @lbound INT
DECLARE @intDisplayOrder SMALLINT
DECLARE @strProductName VARCHAR(150) 
DECLARE @intAccessCode INT --#6
DECLARE @intAccesscodeTypeId INT --#6
SET @intAccesscodeTypeId=71 --#6 
-- --------------------------------------
-- Initialize variables
-- --------------------------------------
SELECT @ubound=0,
@lbound=0,
@intDisplayOrder=0,
@strProductName='',
@intProductInMultipleCategoriesEnabled=0
 
SELECT @intProductInMultipleCategoriesEnabled=ISNULL(StoreproductInMultiCategoryEnabled,'')
FROM CompanyStore.dbo.t_Stores with (nolock)
WHERE EnterpriseId=@aintEnterpriseId AND
StoreId=@aintStoreId
 
SET @ubound=@aintNumberOfItemsPerPage * @aintCurrentPageNum
SET @lbound=@ubound - (@aintNumberOfItemsPerPage - 1)
 
-- Get Default Sorting Option
--#6
IF (@aintStoreId=7223 AND @aintSortby=0)--tesing purpose set the store
BEGIN
SELECT @intAccessCode=AccessCode
FROM CompanyStore.dbo.t_AccessCode
WHERE EnterpriseId=@aintEnterpriseId AND
StoreId=@aintStoreId AND
AccessCodeTypeId=@intAccesscodeTypeId
 
SET @aintSortby=CASE @intAccessCode
WHEN 1 THEN 1
WHEN 2 THEN 3
WHEN 3 THEN 2
END --End#6
END
 
CREATE TABLE #tmp_GetProductList_Product
(
ProductId INT,
ProductName VARCHAR(150) Null,
ProductEnterpriseId INT,
ProductSellingPageId MONEY,
ProductPrice MONEY,
ProductThumbnailImageTypeId TINYINT,
ProductVisibilityCodes VARCHAR(300),
ProductSKU VARCHAR(150),
ManufacturerName VARCHAR(300),
ProductCategoryId INT,
ProductDescriptors VARCHAR(500),
ProductEnlargedImageTypeId TINYINT,
ProductDescription VARCHAR(5000), 
ProductIsReadOnly TINYINT,
SupplierCatalog VARCHAR(250),--#2
EnLargedImageName VARCHAR(50), --#1
ThumbNailImage VARCHAR(50),
productunitofMeasure VARCHAR(25),
ProductNumOptions SMALLINT, --#3
DisplayOrder INT,
RowNum INT NOT NULL IDENTITY(1,1)
)
 
IF(@intProductInMultipleCategoriesEnabled=1)
BEGIN
SELECT @aintTotalItems=COUNT(DISTINCT t_StorefrontProduct.ProductId)
FROM t_StorefrontProduct AS t_StorefrontProduct WITH(nolock)
INNER JOIN CompanyStore.dbo.t_StorefrontCategoryProduct AS t_StorefrontCategoryProduct WITH(nolock)
ON (t_StorefrontProduct.StoreId=t_StorefrontCategoryProduct.StoreId AND
t_StorefrontProduct.ProductId=t_StorefrontCategoryProduct.ProductId )
LEFT JOIN CompanyStore.dbo.t_ManufacturerModelSkuMapping AS t_ManufacturerModelSkuMapping WITH(nolock)
ON (t_ManufacturerModelSkuMapping.EnterpriseId=t_StorefrontProduct.EnterpriseId AND
t_ManufacturerModelSkuMapping.ProductId=t_StorefrontProduct.ProductId )
INNER JOIN CompanyStore.dbo.t_products t_products
ON (t_products.EnterpriseId=t_StorefrontProduct.ProductEnterpriseId AND
t_products.ProductId=t_StorefrontProduct.ProductId)
WHERE t_StorefrontProduct.EnterpriseId=@aintEnterpriseId AND
t_StorefrontProduct.StoreId=@aintStoreId AND
t_StorefrontCategoryProduct.CategoryId=@aintProductCategoryId AND
dbo.fn_IsProductVisible(@aintEnterpriseId, @aintStoreId,t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@astrUserProdCatFilter)=1 AND --#4
ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')=CASE WHEN ISNULL(@astrManufacturer,'')<>'' THEN @astrManufacturer
ELSE ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')
END AND
CAST(ISNULL(t_ManufacturerModelSkuMapping.Model,'') AS VARCHAR)=CASE WHEN ISNULL(@astrModel,'')<>'' THEN @astrModel
ELSE ISNULL(t_ManufacturerModelSkuMapping.Model,'')
END
 
SET ROWCOUNT @ubound
 
INSERT #tmp_GetProductList_Product
(
ProductId,
ProductName,
ProductEnterpriseId,
ProductSellingPageId,
ProductPrice,
ProductThumbnailImageTypeId,
ProductVisibilityCodes,
ProductSKU,
ManufacturerName,
ProductCategoryId,
ProductDescriptors,
ProductEnlargedImageTypeId,
ProductDescription,
ProductIsReadOnly,
SupplierCatalog, --#2
EnLargedImageName, --#1
ThumbNailImage,
productunitofMeasure,
ProductNumOptions, --#3
DisplayOrder
)
SELECT productId,
ProductName,
ProductEnterpriseId,
ProductSellingPageId,
Productprice,
ProductThumbnailImageTypeId,
ProductVisibilityCodes,
ProductSKU,
ManufacturerName,
ProductCategoryId,
ProductDescriptors,
ProductEnlargedImageTypeId,
ProductDescription,
ProductIsReadOnly,
SupplierCatalogLink,
productenlargedimagefilename,
ProductImageFileName,
productunitofMeasure,
ProductNumOptions, --#3
DisplayOrder
FROM
( SELECT DISTINCT
t_StorefrontProduct.ProductId AS ProductId,
t_StorefrontProduct.ProductName AS ProductName,
t_StorefrontProduct.ProductEnterpriseId AS ProductEnterpriseId,
t_StorefrontProduct.ProductSellingPageId AS ProductSellingPageId,
CASE
WHEN @aintUserHasQuotedPricing=1AND (dbo.fn_StorefrontProductPriceGetQuoted_NER(@aintEnterpriseId, @aintStoreId, @aintUserId, t_StorefrontProduct.ProductEnterpriseId, t_StorefrontProduct.ProductId)>0 )
THEN dbo.fn_StorefrontProductPriceGetQuoted_NER(@aintEnterpriseId, @aintStoreId, @aintUserId, t_StorefrontProduct.ProductEnterpriseId, t_StorefrontProduct.ProductId)
ELSE
CASE WHEN @aintUserPricingLevelID=0 THEN ProductDefaultPriceA
WHEN (@aintUserPricingLevelID>0) AND (dbo.fn_EnterprisePublishProductPriceGetAltPrice_NER(t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@aintUserPricingLevelID,-1)>0)
THEN dbo.fn_EnterprisePublishProductPriceGetAltPrice_NER(t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@aintUserPricingLevelID,-1)
ELSE ProductDefaultPriceA
END
END AS ProductPrice,
t_StorefrontProduct.ProductThumbnailImageTypeId ProductThumbnailImageTypeId,
t_StorefrontProduct.ProductVisibilityCodes ProductVisibilityCodes,
CompanyStore.dbo.fn_StorefrontGetproductSKU (t_StorefrontProduct.ProductId, 0, '') AS ProductSku,
dbo.fn_StorefrontGetManufacturerNameByProductId(@aintEnterpriseId,t_StorefrontProduct.ProductId) AS ManufacturerName,
t_StorefrontCategoryProduct.CategoryId ProductCategoryId,
ISNULL(ProductDescriptors,'') AS ProductDescriptors,
t_StorefrontProduct.ProductEnlargedImageTypeId ProductEnlargedImageTypeId,
t_StorefrontProduct.ProductDescriptiON ProductDescription,
ISNULL(t_StorefrontProduct.ProductIsReadOnly,0) AS 'ProductIsReadOnly',
CoStoreStorefront.dbo.fn_GetStoreFrontProductAttributeValue(@aintEnterpriseId,@aintStoreid,t_StorefrontProduct.ProductId,6) AS 'SupplierCatalogLink',
ISNULL(productenlargedimagefilename, 'noimage.gif') productenlargedimagefilename, --#1
ISNULL(ProductImageFileName,'noImage.gif') ProductImageFileName,
ISNULL(t_products.productunitofMeasure,'') AS productunitofMeasure,
ProductNumOptions AS ProductNumOptions, --#3
t_Products.DisplayOrder AS DisplayOrder
FROM t_StorefrontProduct t_StorefrontProduct WITH(nolock)
INNER JOIN CompanyStore.dbo.t_StorefrontCategoryProduct AS t_StorefrontCategoryProduct WITH(nolock)
ON (t_StorefrontProduct.StoreId=t_StorefrontCategoryProduct.StoreId AND
t_StorefrontProduct.ProductId=t_StorefrontCategoryProduct.ProductId)
LEFT JOIN CompanyStore.dbo.t_ManufacturerModelSkuMapping AS t_ManufacturerModelSkuMapping WITH(nolock)
ON (t_ManufacturerModelSkuMapping.EnterpriseId=t_StorefrontProduct.EnterpriseId AND
t_ManufacturerModelSkuMapping.ProductId=t_StorefrontProduct.ProductId)
INNER JOIN CompanyStore.dbo.t_products t_products
ON (t_products.enterpriseid=t_StorefrontProduct.ProductEnterpriseId AND
t_products.ProductId=t_StorefrontProduct.ProductId)
WHERE t_StorefrontProduct.EnterpriseId=@aintEnterpriseId AND
t_StorefrontProduct.StoreId=@aintStoreId AND
t_StorefrontCategoryProduct.CategoryId=@aintProductCategoryId AND
dbo.fn_IsProductVisible(@aintEnterpriseId, @aintStoreId,t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@astrUserProdCatFilter) = 1 AND
ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')=CASE WHEN ISNULL(@astrManufacturer,'')<>'' THEN @astrManufacturer
ELSE ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')
END AND
CAST(ISNULL(t_ManufacturerModelSkuMapping.Model,'') AS VARCHAR)=CASE WHEN ISNULL(@astrModel,'')<>'' THEN @astrModel
ELSE ISNULL(t_ManufacturerModelSkuMapping.Model,'')
END
) AS tblTemp
ORDER BY
CASE WHEN @aintSortby=1
THEN tblTemp.ProductName END ASC,
CASE WHEN @aintSortby=2
THEN tblTemp.ProductPrice END DESC,
CASE WHEN @aintSortby=3
THEN tblTemp.ProductPrice END ASC,
CASE WHEN @aintSortby=4
THEN tblTemp.ProductSku END ASC,
CASE WHEN @aintSortby=0
THEN tblTemp.DisplayOrder END ASC
END
------------ 
--ELSE Part
------------
ELSE IF(@intProductInMultipleCategoriesEnabled = 0)
BEGIN
SELECT @aintTotalItems=COUNT(DISTINCT t_StorefrontProduct.ProductId)
FROM CoStoreStorefront.dbo.t_StorefrontProduct t_StorefrontProduct WITH (nolock)
INNER JOIN CompanyStore.dbo.t_StoreProducts AS t_StoreProducts WITH (nolock)
ON (t_StorefrontProduct.StoreId=t_StoreProducts.StoreId AND
t_StorefrontProduct.ProductId=t_StoreProducts.ProductId)
INNER JOIN CompanyStore.dbo.t_Categories AS t_Categories WITH(nolock)
ON (t_StoreProducts.StoreId=t_Categories.StoreId AND
t_StoreProducts.CategoryId=t_Categories.CategoryId)
LEFT JOIN CompanyStore.dbo.t_ManufacturerModelSkuMapping AS t_ManufacturerModelSkuMapping WITH(nolock)
ON (t_ManufacturerModelSkuMapping.EnterpriseId=t_StorefrontProduct.EnterpriseId AND
t_ManufacturerModelSkuMapping.StoreId=t_StorefrontProduct.StoreId AND
t_ManufacturerModelSkuMapping.ProductId=t_StorefrontProduct.ProductId)
INNER JOIN CompanyStore.dbo.t_products t_products
ON (t_products.enterpriseid=t_StorefrontProduct.EnterpriseId AND
t_products.ProductId=t_StorefrontProduct.ProductId)
WHERE t_StorefrontProduct.EnterpriseId=@aintEnterpriseId AND
t_StorefrontProduct.StoreId=@aintStoreId AND
t_StoreProducts.CategoryId=@aintProductCategoryId AND
dbo.fn_IsProductVisible(@aintEnterpriseId, @aintStoreId,t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@astrUserProdCatFilter)=1 AND
ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')=CASE WHEN ISNULL(@astrManufacturer,'')<>'' THEN @astrManufacturer
ELSE ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')
END AND
CAST(ISNULL(t_ManufacturerModelSkuMapping.Model,'') AS VARCHAR)=CASE WHEN ISNULL(@astrModel,'')<>'' THEN @astrModel
ELSE ISNULL(t_ManufacturerModelSkuMapping.Model,'')
END
 
SET ROWCOUNT @ubound
 
INSERT #tmp_GetProductList_Product
(
ProductId,
ProductName,
ProductEnterpriseId,
ProductSellingPageId,
ProductPrice,
ProductThumbnailImageTypeId,
ProductVisibilityCodes,
ProductSKU,
ManufacturerName,
ProductCategoryId,
ProductDescriptors,
ProductEnlargedImageTypeId,
ProductDescription,
ProductIsReadOnly,
SupplierCatalog, --#2
EnLargedImageName, --#1
ThumbNailImage,
productunitofMeasure,
ProductNumOptions, --#3
DisplayOrder
)
SELECT productId,
ProductName,
ProductEnterpriseId,
ProductSellingPageId,
Productprice,
ProductThumbnailImageTypeId,
ProductVisibilityCodes,
ProductSKU,
ManufacturerName,
ProductCategoryId,
ProductDescriptors,
ProductEnlargedImageTypeId,
ProductDescription,
ProductIsReadOnly,
SupplierCatalogLink,
productenlargedimagefilename,
ProductImageFileName,
productunitofMeasure,
ProductNumOptions, --#3
DisplayOrder
FROM
(
SELECT DISTINCT
t_StorefrontProduct.ProductId AS productid,
t_StorefrontProduct.ProductName AS productname,
t_StorefrontProduct.ProductEnterpriseId AS ProductEnterpriseId,
t_StorefrontProduct.ProductSellingPageId AS ProductSellingPageId,
CASE
WHEN @aintUserHasQuotedPricing=1 and (dbo.fn_StorefrontProductPriceGetQuoted_NER(@aintEnterpriseId, @aintStoreId, @aintUserId, t_StorefrontProduct.ProductEnterpriseId, t_StorefrontProduct.ProductId) > 0 )
THEN dbo.fn_StorefrontProductPriceGetQuoted_NER(@aintEnterpriseId, @aintStoreId, @aintUserId, t_StorefrontProduct.ProductEnterpriseId, t_StorefrontProduct.ProductId)
ELSE CASE WHEN @aintUserPricingLevelID=0 THEN ProductDefaultPriceA
WHEN (@aintUserPricingLevelID > 0) and (dbo.fn_EnterprisePublishProductPriceGetAltPrice_NER(t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@aintUserPricingLevelID,-1) >0)
THEN dbo.fn_EnterprisePublishProductPriceGetAltPrice_NER(t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@aintUserPricingLevelID,-1)
ELSE ProductDefaultPriceA
END
END AS ProductPrice,
t_StorefrontProduct.ProductThumbnailImageTypeId ProductThumbnailImageTypeId,
t_StorefrontProduct.ProductVisibilityCodes ProductVisibilityCodes,
CompanyStore.dbo.fn_StorefrontGetproductSKU (t_StorefrontProduct.ProductId, 0, '') AS ProductSku,
dbo.fn_StorefrontGetManufacturerNameByProductId(@aintEnterpriseId,t_StorefrontProduct.ProductId) AS ManufacturerName,
t_StorefrontCategoryProduct.CategoryId ProductCategoryId,
ISNULL(ProductDescriptors,'') AS ProductDescriptors,
t_StorefrontProduct.ProductEnlargedImageTypeId ProductEnlargedImageTypeId,
t_StorefrontProduct.ProductDescription ProductDescription,
ISNULL(t_StorefrontProduct.ProductIsReadOnly,0) AS 'ProductIsReadOnly',
CoStoreStorefront.dbo.fn_GetStoreFrontProductAttributeValue(@aintEnterpriseId,@aintStoreid,t_StorefrontProduct.ProductId,6) AS 'SupplierCatalogLink',
ISNULL(productenlargedimagefilename, 'noimage.gif') productenlargedimagefilename, --#1
ISNULL(ProductImageFileName,'noImage.gif') ProductImageFileName,
ISNULL(t_products.productunitofMeasure,'') AS productunitofMeasure,
ProductNumOptions as ProductNumOptions --#3
,t_Products.DisplayOrder AS DisplayOrder
FROM t_StorefrontProduct t_StorefrontProduct with(nolock)
INNER JOIN CompanyStore.dbo.t_StorefrontCategoryProduct AS t_StorefrontCategoryProduct with(nolock)
On (
t_StorefrontProduct.StoreId=t_StorefrontCategoryProduct.StoreId And
t_StorefrontProduct.ProductId=t_StorefrontCategoryProduct.ProductId
)
left JOIN CompanyStore.dbo.t_ManufacturerModelSkuMapping AS t_ManufacturerModelSkuMapping with(nolock)
On (
t_ManufacturerModelSkuMapping.EnterpriseId=t_StorefrontProduct.EnterpriseId and
t_ManufacturerModelSkuMapping.ProductId=t_StorefrontProduct.ProductId
)
INNER JOIN CompanyStore.dbo.t_products t_products on --#1
(
t_products.enterpriseid=t_StorefrontProduct.ProductEnterpriseId and
t_products.ProductId=t_StorefrontProduct.ProductId
)
where t_StorefrontProduct.EnterpriseId=@aintEnterpriseId and
t_StorefrontProduct.StoreId=@aintStoreId And
t_StorefrontCategoryProduct.CategoryId=@aintProductCategoryId and
dbo.fn_IsProductVisible(@aintEnterpriseId, @aintStoreId,t_StorefrontProduct.ProductEnterpriseId,t_StorefrontProduct.ProductId,@astrUserProdCatFilter)=1 --#4
And
ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')=CASE WHEN ISNULL(@astrManufacturer,'')<>''
THEN @astrManufacturer
ELSE ISNULL(t_ManufacturerModelSkuMapping.Manufacturer,'')
END and
cast(ISNULL(t_ManufacturerModelSkuMapping.Model,'') as varchar)=CASE WHEN ISNULL(@astrModel,'')<>''
THEN @astrModel
ELSE ISNULL(t_ManufacturerModelSkuMapping.Model,'')
END
) AS tblTemp
Order by
CASE WHEN @aintSortby=1
THEN tblTemp.ProductName END ASC,
CASE WHEN @aintSortby=2
THEN tblTemp.ProductPrice END DESC,
CASE WHEN @aintSortby=3
THEN tblTemp.ProductPrice END ASC,
CASE WHEN @aintSortby=4
THEN ProductSku END ASC,
CASE WHEN @aintSortby=0 
THEN tblTemp.DisplayOrder END ASC
END
 
SET NOCOUNT ON
SELECT ProductId,
ProductName,
ProductEnterpriseId,
ProductSellingPageId,
ProductPrice,
ProductThumbnailImageTypeId,
ProductVisibilityCodes,
ProductSKU,
ManufacturerName,
ProductCategoryId,
ProductDescriptors,
ProductEnlargedImageTypeId,
ProductDescription,
ProductIsReadOnly,
SupplierCatalog,
EnLargedImageName,
ThumbNailImage,
productunitofMeasure,
ProductNumOptions,
RowNum,
CoStoreStorefront.dbo.fn_GetProductMinimumQty(@aintEnterpriseId,ProductId) AS 'QuantityMinimum', --#5
CoStoreStorefront.dbo.fn_GetProductAttribute(@aintEnterpriseId,@aintStoreid,ProductId,1) AS 'IsQtyMinEnforced' --#5
FROM #tmp_GetProductList_Product
WHERE RowNum BETWEEN @lbound AND @ubound
 
DROP TABLE #tmp_GetProductList_Product
END
 

Note:-
The problem is when i select dropdownlist it should take some time(Slow) to loading on the asp webpage, but the sp is correct..,



Regards
Siva