I want to get items randomly. For example If I got 1000 items and my query (select top 20) these 20 items get randomly in 1000 items. If I put Order By Newid() query take long time like 10 sec.
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Üçkuyular, İzmir, Turkey On Sun, Dec 6, 2009 at 6:36 AM, Raghupathi Kamuni <[email protected]>wrote: > >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 >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >>> >> >> >
