I added this line and works perfect. row_number() OVER ( ORDER BY NEWID() ) randid Thanks for helps.
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r Sent from Izmir, 35, Turkey On Mon, Dec 7, 2009 at 12:47 PM, Raghupathi Kamuni <[email protected]>wrote: > 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 >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >> >
