|
mrsaif
Starting Member
43 Posts |
 Posted - 10/19/2006 : 09:27:42
|
Thanks, but my problem is not solved yet, but my query imporves a litle bit now it is taking almost 7 seconds and have improved 2.5 secords :) . if i do not used these 2 functions which contais that previous logic than it takes 0.69 secods. By using above method it does not improve in great deal.
and it is neccessary to use fucntion for me bcz the resultnat row returns 2 records with one column , i concate these recrds in the main query that is why i am using function is there any other way to improve my query.
Here is my main query.
SELECT top 20 dbo.Products.ProductID, dbo.GetFieldColorForProducts(ProductID) AS FieldColorString, dbo.GetBorderColorForProducts(ProductID) AS BorderColorString, dbo.Collections.CollectionCode,
dbo.Products.Description, dbo.Designs.DesignCode, dbo.Countries.CountryCode, dbo.Ages.AgeCode
FROM dbo.Products
INNER JOIN dbo.Collections ON dbo.Collections.CollectionID = dbo.Products.CollectionIDFK
INNER JOIN dbo.Designs ON dbo.Products.DesignIDFK = dbo.Designs.DesignID
INNER JOIN dbo.ItemDetails ON dbo.ItemDetails.ItemIdFK = dbo.Products.ProductID AND dbo.ItemDetails.ProductTypeIDFK = 1 AND dbo.ItemDetails.LockingCodeIdFK <> 15
LEFT OUTER JOIN dbo.Locations ON dbo.Locations.LocationID = dbo.ItemDetails.LocationIDFK
BorderColor.ColorID = dbo.Products.BorderColorIDFK
FieldColor.ColorID = dbo.Products.FieldColorIDFK
INNER JOIN dbo.Countries ON dbo.Countries.CountryID = dbo.Products.OriginCountryIDFK
dbo.SizeCategories.SizeCategoryID = dbo.Products.SizeCategoryIDFK
INNER JOIN dbo.Ages ON dbo.Ages.AgeID = dbo.Products.AgeIDFK
INNER JOIN dbo.Shapes ON dbo.Products.ShapeIDFK = dbo.Shapes.ShapeId
LEFT OUTER JOIN dbo.LockingCodes ON dbo.LockingCodes.LockingCodeId = dbo.ItemDetails.LockingCodeIdFK '
dbo.ProductColors.ProductIDFK '
+ @whereStr + ' and Productid>@Productid
----------------------------------------
function GetBorderColorForProducts (@ProductID BIGINT) has following query
DECLARE @ColorDescriptions AS VARCHAR(1000)
SET @ColorDescriptions = ''
select @ColorDescriptions = coalesce(@ColorDescriptions + ' OR ','') + Colors.ColorDescription FROM ProductColors, Colors WHERE ProductColors.ProductIDFK = @ProductID AND ProductColors.ColorTypeID = 0 AND Colors.ColorID = ProductColors.ColorIDFK --Assuming 2 is for border color
RETURN @ColorDescriptions
----------------------------------
and function GetFieldColorForProducts (@ProductID BIGINT) has follwing qyery
DECLARE @ColorDescriptions AS VARCHAR(1000)
SET @ColorDescriptions = ''
select @ColorDescriptions = coalesce(@ColorDescriptions+' OR ','') +Colors.ColorDescription FROM ProductColors, Colors WHERE ProductColors.ProductIDFK = @ProductID AND ProductColors.ColorTypeID = 1 AND Colors.ColorID = ProductColors.ColorIDFK --Assuming 1 is for field color
return @ColorDescriptions
Muhammad Saifullah |
 |
|
How to avoid from Cursor in query
. Please help me. if there is another way