Mayber it would be good to try DoS using simple page refreshing.... If it will get frozen, then something is wrong...
2009/12/3 Jamie Fraser <[email protected]> > 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 >>>>>> >>>>> >>>>> >>>> >>> >> >
