.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Melody Anderson
Eminent IT
Post New Web Links

perfomance issue with 'order by' clause

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server


  I have copied 2 queries and their execution plans below.first one taking long time to execute and the second one finishes with in a second.can u guys pls explain what is happening inside sql server engine.


--first part

select   se.eventdate
haregistration r inner join
screening_event_slot ses

on r.ScreeningSlotID=ses.SlotId
inner join screening_event se
on se.eventid=ses.eventid  order by EventDate

 |--Nested Loops(Inner Join, WHERE:([microsoft].[dbo].[HARegistration].[ScreeningSlotID] as [r].[ScreeningSlotID]=[microsoft].[dbo].[screening_event_slot].[SlotId] as [ses].[SlotId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([se].[EventId], [Expr1006]) WITH ORDERED PREFETCH)
       |    |--Index Scan(OBJECT:([microsoft].[dbo].[screening_event].[IX_EventDate] AS [se]), ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([microsoft].[dbo].[screening_event_slot].[AK_screening_event_slot] AS [ses]), SEEK:([ses].[EventId]=[microsoft].[dbo].[screening_event].[EventId] as [se].[EventId]) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([microsoft].[dbo].[HARegistration].[PK_HAR

View Complete Post

More Related Resource Links

'HAVING' clause issue

Hi - hopefully someone her can help me straighten out this query.  I have two tables from which I'm 'retrieving' records to add to a third table. Table 1 has three columns ([TheDate], [A], [B], [C]) and contains approximately 20 records per minute. Table 2 has two columns ([TheDate]. [D], [E]) and contains approximately 1 record per minute. Table 3 (#TempRes) is a table with six columns ([TheDate], [A], [B], [C], [D], [E]) containing all the records in tables 1 and 2. Below is my SQL command for returning information from #TempRes that groups (and averages) the information from Table 3 by minute - but the problem with my command is that in the 'HAVING' clause, where the COUNT([A]) > 19 text is, I really want something like 'COUNT([A] where A>0) > 19'.  (That is, I only want to include records that have greater than 19 non-zero A values).  The function is merely a function to convert the [TheDate] column to always have '00' seconds (ie, the result of the function on '14:21:43' would be '14:21:00'). Any idea how I can do this, please? SIncerely, Peter --------------------------  SELECT     dbo.fn_DateTimeInterval([TheDate],@Interval),     CASE WHEN SUM([B]) IS NULL OR SUM([B]) = 0 THEN 0 ELSE SUM([A] * [B])/SUM([B]) END,     CASE WHEN SUM([B]) >100 THEN 100 ELSE SUM([B]) END,   

Do changing clause order impress on speed?

Hi suppose there is a table with feilds: idMember, idHost, ip , dateofAccess. this table save all accesses to system and means:a "idMember" with iPaddress "ip" through "idHost" accessed to system at "dateofAccess". now we want to write a query like below: select Count(idMemeber) from myTable where (idMemeber = 1) and (idHost = 2) and (ip = and (dateOfAccess < '1.1.2009') my question is:Do speed differ if change the order of up clause like bellow. select Count(idMemeber) from myTable where (dateOfAccess < '1.1.2009') and (ip = and (idHost = 2) and (idMemeber = 1) Best Regards.Morteza  

ORDER BY clause causes 'A severe error occurred on the current command. The results, if any, shoul



I'm trying to execute the following:

  FROM qaeda.R727702b.NF29005
  FROM qaeda.r727702b.NF29008

In both 2005 and 2008 versions of the SQL Server the query parses ok, but on execute I get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

If I remove the the 'ORDER BY' clause, I am able to execute the query with no problem. 

I've searched the forum and I know this error comes up often in various circumstances, however I didn't see anything similar to this.  Please advise, maybe I'm writing the query incorrectly?  Thanks



Order By clause in RowNumber function slowing down the performance of my query.



I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.






Order by property issue



I have a dimension called DimLocation which has this strucutre





I want to arrange the State members using the OrderBy property, if I select the Value  AttributeName and then try to precise the attribute according to which attribute members would be ordered using the next property OrderByAttribute then I didn't find any corresponding attributes in the OrderByAttribute drop own list, I tried to specify this by direct writing the name of the attribute on the property cell but it won't work if I hover the given attribute in the attributes zone (next to the hierarchy zone) a  tooltip tells me "The ordering criteria is set to 'Attribute Name', but the ordering attribute is not set "

The complexity resides in the simplicity

Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



Split Container Tab Order Issue



  I have a split container on my form with a MonthCalendar control in Panel1 on the left, tab order 1.0.0 and a Datagridview control in Panel2 on the right, tab order 1.1.0.  When the form opens I want the focus to be on the first visible cell of the Datagridview, but I am unable to change the tab order of the split container panels.

I've tried adding Datagridview.Select or Datagridview.Rows(0).Selected = True to the Form_Load event, but once it runs through all the code & events after it still seems to put the focus back to the MonthCalendar.

can i create a Clr ud Aggregate Function with over/ order by clause?


i want create a UD Aggregate like  Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)

is it possible?

Perfomance of EXISTS clause with different result styles of nested SELECT


Is there any difference (perfomance difference) between:


and another variant



Issue with top and order by


I have two different queries in which the only difference is the order by clause. There result set is empty (no records returned). Query 1 runs completes immediately, but Query 2 take 1:06 to complete. Any ideas on why adding the order by clause results in this type of performance penalty?

Thanks, Mike



select cp.* 

from tblcourseregistration cr 

inner join tblcourseposition cp on cr.sitememberid = 77434 and cr.courseregistrationid = cp.courseregistrationid 

order by cp.coursepositionid desc



select top 1 cp.* 

from tblcourseregistration cr 

inner join tblcourseposition cp on cr.sitememberid = 77434 and cr.courseregistrationid = cp.courseregistrationid 

order by cp.coursepositionid desc

Use Two Order By Clause In One Statement


I want to use two order by clause in one statemet or i just want to show one column in ascending order in other column in descending order without using T-SQL Statement

Please Suggest Me .

Thanks In Advance

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta


Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!

			 COUNT(*) AS [Count]
		GROUP BY System_User_ID
		ORDER BY [Count]) l
	INNER JOIN aspnet_Membership m
	ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
Msg 1033, Level 15, State 1, Line 11
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Two statement in WHERE clause one is comparing strings one integers, should there be any order to ma


OK. Enough complexity in the subject line, here are the details

I have a table with many columns, two of them are ManagerId INT and DesignationCode VARCHAR(30), Now the following query

SELECT EmpId, EmpName FROM Employee WHERE DesignationCode='SWENG' AND ManagerId=3


A friend of mine has been told by an MVP that in this case integer comparison should be the first one in order and varchar should be the second to make it perform a lot better.

I tried googling for 15 min, found no evidence. Do you know of any performance suggestion ?

My friend says that his MVP friend says that SQL Server's engine is designed to process it faster this way. So the WHERE clause should be changed to WHERE ManagerId=3 AND DesignationCode='SWENG' 


Do you guys know anything about it ?




Improve Perfomance in ASP.net

While developing any web site, one should keep some points in mind.
1) Set debug=false under compilation as follows:

2) Use Server.Transfer instead of Response.Redirect.

3) Always check Page.IsValid when using Validator Controls

Power of using Clause in SharePoint

The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,

IsapiModule Error 500 Issue


I am about exhausted trying to figure out this error:

HTTP Error 500.0 - Internal Server Error

Description: The page cannot be displayed because an internal server error has occurred.

Error Code: 0x8007007b

Notification: ExecuteRequestHandler

Module: IsapiModule

Requested URL: http://localhost:80/ReportServer

Physical Path: c:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer

When I enable Failed Request Tracing Rules and view the log output I am supposing that the actual problem is in the 2nd call where there are 2 backslashes before the actual filename in the specified path:


CALL_ISAPI_EXTENSION DllName="C:\Windows\Microsoft.NET\Framework\v2.0.50727\\aspnet_isapi.dll"


MVC2 issue - Stuck. Trying to create a cms with nested partials


I am currently trying to display multiple items on a single page. I am not sure as to how to tackle this. What I have is a database table that has all the page data in. I have this run as a PagesController and this works fine. However what I would like to do is, if say the Products page is selected via the menu, I want to pull back not only the Products page html but also get all the products, and even be able to pull them through via category. I was trying to use a partial view, but I can't get it to work. The same would be for other pages, having partial views to display the other content, ie if Gallery is selected, the page html for Gallery shows and it then can push to a partial view that pulls all the gallery images from the gallery db table and so on. 

Maybe I am tackling this the wrong way. I have the pages stored in the db to allow for a CMS system I have setup. Maybe I need to use models for all the other items(Products, Gallery, Videos and such) and use the partial for the page html?

Any help would be greatly appreciated as I am confused.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend