Hmm I got it. Thanks for links. They really helpful for me. Is it possible to get random fields except order by newid()?
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Izmir, 35, Turkey On Sat, Dec 5, 2009 at 9:46 AM, Raghupathi Kamuni <[email protected]>wrote: > 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 >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
