perfomance issue with 'order by' clause

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,   

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 = 12.12.12.12) 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 = 12.12.12.12) and (idHost = 2) and (idMemeber = 1) Best Regards.Morteza  

Hello,

I'm trying to execute the following:

SELECT STATE_CODE5
  FROM qaeda.R727702b.NF29005
  WHERE STATE_CODE5 IN ('IL','NV','VA','NH','HI')
  InTERSECT
  SELECT STATE_CODE8
  FROM qaeda.r727702b.NF29008
  WHERE STATE_NAME8 = 'HAWAII'
 ORDER BY STATE_CODE5;

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

 

 

Hi,

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.

Thanks,

Deepti

 

 

 

Hi

I have a dimension called DimLocation which has this strucutre

Region

    Country

        State

            City

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

Hi

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.

 

---

SNIVAS

Hello,

  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.

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

is it possible?
thanks.

Is there any difference (perfomance difference) between:

.. WHERE EXISTS (SELECT * FROM myTable)

and another variant

.. WHERE EXISTS (SELECT TOP (1) * FROM myTable)

??

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

 

QUERY 1:

select cp.* 

from tblcourseregistration cr 

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

order by cp.coursepositionid desc

 

QUERY 2:

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

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

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!

	SELECT
		 l.[Count]
		,m.LoweredEmail
	FROM 
		(SELECT 
			 COUNT(*) AS [Count]
			,System_User_ID
		FROM LOGSEARCH 
		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

Error:
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.

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 ?

 

Thanks

---

Fahad

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

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,

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:

ISAPI_START

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.
Categories:
ASP.Net Windows Application   .NET Framework   C#   VB.Net   ADO.Net   Sql Server   SharePoint   Silverlight   Others   All