Second procedure is working but if the item has 2 pictures I saw 2 same item. If 5 or more pictures I saw 5 or more item. But I need just 1 item even if the item has 1 or more pictures. So it's not working. Can I explain it? Sorry for my English.
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Izmir, 35, Turkey On Wed, Dec 2, 2009 at 1:55 PM, Kadir Avci <[email protected]> wrote: > Hmm. I use dynamic because the user set the @gosUrun. I take it from > codebehind and send to sql procedure. Yesterday I wrote inner join and now > its ok. But I didn't know your solution. > I will try it asap. How is the performance of yours? > > > Kadir Avcı > Software Developer | Freelance Web Designer > web: www.kad1r.com > twt: www.twitter.com/kad1r > > > On Wed, Dec 2, 2009 at 1:49 PM, Jamie Fraser <[email protected]>wrote: > >> Why are you using SQL like that? You should avoid the use of dynamic SQL >> like this as it performs comparatively poorly. >> >> If you are using SQL 2005 you can parameterise the TOP query (SELECT TOP >> @topcount....) >> >> If you are using SQL 2000 or before (why?) you can use the SET ROWCOUNT >> @topcount pattern. >> >> So you could rewrite your query as (assuming SQL2005 or later) >> >> >> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >> @subcatID nvarchar (500), >> @gosUrun nvarchar(50) >> AS >> BEGIN >> SELECT TOP (@gosUrun) >> AVMWeb_Items.*, >> (SELECT subcatname FROM AVMWeb_ItemSubCategorys WHERE >> AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID) AS subcatname, >> (SELECT brandname FROM AVMWeb_ItemBrands WHERE AVMWeb_ItemBrands.id = >> AVMWeb_Items.brandID) AS brandname, >> (SELECT TOp 1 deger FROM AVMWeb_Item_Resources WHERE >> AVMWeb_Item_Resources.itemID = AVMWeb_Items.id) AS deger >> FROM AVMWeb_Items WHERE AVMWeb_Items.subcatID = @subcatID >> >> And the second procedure as >> >> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >> @subcatID nvarchar (500), >> @gosUrun nvarchar(50) >> AS >> BEGIN >> Select top @gosUrun >> AVMWeb_Items.* , >> AVMWeb_ItemSubCategorys.*, >> AVMWeb_ItemBrands.*, >> AVMWeb_Item_Resources.* >> From AVMWeb_Items, AVMWeb_ItemSubCategorys, AVMWeb_ItemBrands, >> AVMWeb_Item_Resources >> Where AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID and >> AVMWeb_ItemBrands.id = AVMWeb_Items.brandID and >> AVMWeb_Item_Resources.itemID = AVMWeb_Items.id and >> AVMWeb_Items.subcatID = @subcatID + ; >> >> When you say "second procedure is not working" what do you mean? It looks >> to me like you should be using a combination of INNER and LEFT JOINs to >> achieve what you want. >> >> >> >> On Tue, Dec 1, 2009 at 11:01 AM, Kadir Avci <[email protected]> wrote: >> >>> Hello. I have an sql procedure and when I run it the time is 10 seconds. >>> I optimize it and now its take 1 second. But I have a problem. If item >>> has two or more pictures second procedure is not working properly. >>> How can I get only one item_resource in second procedure? >>> >>> *Here is my first sql procedure.* >>> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >>> @subcatID nvarchar (500), >>> @gosUrun nvarchar(50) >>> AS >>> BEGIN >>> DECLARE @sqlStr nvarchar(4000); >>> set @sqlStr = 'Select top ' + @gosUrun + 'AVMWeb_Items.*, ' + >>> '(Select subcatname from AVMWeb_ItemSubCategorys where >>> AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID) as subcatname, ' + >>> '(Select brandname from AVMWeb_ItemBrands where AVMWeb_ItemBrands.id >>> = AVMWeb_Items.brandID) as brandname, ' + >>> '(select top 1 deger from AVMWeb_Item_Resources where >>> AVMWeb_Item_Resources.itemID = AVMWeb_Items.id) as deger ' + >>> 'from AVMWeb_Items ' + >>> 'where AVMWeb_Items.subcatID = ''' + @subcatID + ''''; >>> exec (@sqlStr) >>> END >>> >>> >>> *Here is my second sql procedure.* >>> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >>> @subcatID nvarchar (500), >>> @gosUrun nvarchar(50) >>> AS >>> BEGIN >>> DECLARE @sqlStr nvarchar(4000); >>> set @sqlStr = 'Select top ' + @gosUrun + >>> 'AVMWeb_Items.* , ' + >>> 'AVMWeb_ItemSubCategorys.*, ' + >>> 'AVMWeb_ItemBrands.*,' + >>> 'AVMWeb_Item_Resources.* ' + >>> 'From AVMWeb_Items, AVMWeb_ItemSubCategorys, >>> AVMWeb_ItemBrands, AVMWeb_Item_Resources ' + >>> 'Where AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID and >>> ' + >>> 'AVMWeb_ItemBrands.id = AVMWeb_Items.brandID and ' + >>> 'AVMWeb_Item_Resources.itemID = AVMWeb_Items.id and ' + >>> 'AVMWeb_Items.subcatID = ''' + @subcatID + ''''; >>> exec (@sqlStr) >>> END >>> >>> >>> >>> Kadir Avcı >>> Software Developer | Freelance Web Designer >>> web: www.kad1r.com >>> twt: www.twitter.com/kad1r >>> >> >> >
