select the columns you require, do not select all columns using *
On Thu, Dec 3, 2009 at 9:08 PM, Kadir Avci <[email protected]> wrote: > 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 >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
