Hmm now it's ok. It has take only 3.8 sec. I make inner join and then I use again second select. Thanks for help.
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Izmir, 35, Turkey On Thu, Dec 3, 2009 at 12:44 PM, Jamie Fraser <[email protected]>wrote: > 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 >>>> >>> >>> >> >
