Assuming you are using SQL2005, the performance of my solution should be better than using dynamic SQL, plus you get the benefits of query caching etc.
If your SQL is returning more than one picture, you need to add conditions to return 1 picture only - SQL doesn't know that you only want one. You'll need to add further conditions to your WHERE / JOIN clauses to return a single image based on "something" (I don't know how you are determining which image you actually want so I can't comment further). -Jamie On Wed, Dec 2, 2009 at 11:55 AM, 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 >>> >> >> >
