Nice idea Process Devil. Thanks. Jamie, webpage openning time is 3.8 sec. Query is not taking 1 sec. (It's for 5000 column.)
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 5:31 PM, Processor Devil <[email protected]>wrote: > 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 >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >> >
