3.8 seconds is a long time for a fairly simple query like this. Have you use appropriate indexes?
On Thu, Dec 3, 2009 at 12:05 PM, Kadir Avci <[email protected]> wrote: > 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 >>>>> >>>> >>>> >>> >> >
