Alternatives to "Order By NewId() - Slow Performance" http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-21-udf_Num_RandInt.htm http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql http://msdn.microsoft.com/en-us/library/ms189108.aspx http://sqlblog.com/blogs/merrill_aldrich/archive/2009/07/20/random-people-stat.aspx
On Sun, Dec 6, 2009 at 6:41 PM, Kadir Avci <[email protected]> wrote: > 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 >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >> >
