Even if you select all columns from items table, better select using column names Select using column names from Other tables where you select one or two fields.
http://www.mssqlcity.com/Tips/tipTSQL.htm http://www.sql-server-performance.com/tips/tsql_main.aspx http://www.mssqltips.com/category.asp?page=1&catid=37 http://blog.sqlauthority.com/2009/01/20/sql-server-rules-for-optimizining-any-query-best-practices-for-query-optimization/ http://www.c-sharpcorner.com/UploadFile/john_charles/QueryoptimizationinSQLServer200512112007154303PM/QueryoptimizationinSQLServer2005.aspx On Fri, Dec 4, 2009 at 4:19 PM, Kadir Avci <[email protected]> wrote: > @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 >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
