>Is it possible to get random fields except order by newid()? Please eloborate on the above statement, please clearly state what you are trying to achieve...
On 12/5/09, Kadir Avci <[email protected]> wrote: > > 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 >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >>> >> >> >> > >
