@Raghupathi, I need all fields for just Items table. Other tables I select one or two fields.
@Jamie Your first message it works perfect. Can I ask a question? I select items random. I mean I put a condition 'order by newid()' newid = my id and it is uniqueidentifier. If I put this condition query takes 6 sec. Do you think any idea for random? Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Izmir, 35, Turkey On Fri, Dec 4, 2009 at 9:22 AM, Raghupathi Kamuni <[email protected]>wrote: > 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 >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
