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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

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

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server


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.






View Complete Post

More Related Resource Links

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?

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

SQL Query Where Clause



FROM dbo.SSD a , dbo.SSAAppEAL b
AND (a.AssocClaim = 1)
AND b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

When ever I add this Query to my Select Statement. SQL management studio is shutting down abruptly.

The mail fault is with this statement

b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

Can any one help me out


Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

How does the IN ( ) clause affect performance?

When you have a query like this... Select * from Table1 WHERE a=1 and b=2 and c IN (5,6,7,8,9,0,1,2,3,4) How does SQL process that? I am trying to figure out if there is any real performance difference between the IN ( ) clause and a join to a temp table. Thanks! Jim

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Full text performance of a certain query

I am doing a full text query on a very simple table and when I include the search term "y5v" in the conditions it slows the query down like 8 to 1. The table is defined as: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblTextSrchData]( [intItemId] [int] NOT NULL, [srchTxt] [varchar](max) NOT NULL, CONSTRAINT [PK_tblTextSrchData] PRIMARY KEY CLUSTERED ( [intItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is the query: SELECT II.intItemID, KEY_TBL.[RANK] as Score FROM tblItems II INNER JOIN CONTAINSTABLE(dbo.tblTextSrchData,srchTxt, '("capacitor" OR FORMSOF(INFLECTIONAL, "capacitor") OR "capacitor*") AND ("active" OR FORMSOF(INFLECTIONAL, "active") OR "active*") AND ("y5v" OR FORMSOF(INFLECTIONAL, "y5v") OR "y5v*")') KEY_TBL ON KEY_TBL.[KEY] = II.intItemID If I take any of the additional search terms out it improves performance.  If I remove the yv5 search term the results are returned in less than a second. What seems to be the problem is for some reason when I add yv5 it starts doing these sorts after the full text matching.  These sorts take most of the time.  I'm not sure

SQL Query keeps failing on Where clause

I am very new to SQL so forgive me for this. We are upgrading to Dynamics AX 2009 and in doing so I have been volunteered as the report writer. Lucky me. I have a query that should return all items for a customer and their resective OnHand quantity in one field and in another field it should only return an OnHand quantity if certain conditions are met in another field, in this case a location field (example; product in Embargo). Also this Embargo could be a variation of locations, (example) louembarg, embargff, louembargun, etc. I have cleaned my query back up since I believe my path was way off track. Below the query is an example of what the end result should be. SELECT     TOP (100) PERCENT dbo.INVENTTABLE.DATAAREAID, dbo.INVENTTABLE.CUSTACCOUNT, dbo.INVENTTABLE.CUSTDIVISIONID,                       dbo.INVENTTABLE.PACKAGINGGROUPID, dbo.INVENTTABLE.ITEMGROUPID, dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.NAMEALIAS,                       dbo.INVENTTABLE.ITEMNAME, dbo.INVENTTABLE.STANDARDPALLETQUANTITY, dbo.INVENTTABLE.BOMUNITID, dbo.INVENTDIM.WMSLOCATIONID,            &n

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

how to create a List order and unourder a simple Sql Query

A Simple Query Pass to connection String 1 My Query is SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a   2,My Data is HD    HEAD    SH    SUBHEAD    TRK    TRACK    TGT    TARGET    PDC    DT_RELEASE    STATUS    COHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR1    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP    EdlinkHR    HR  

query w.r.t to SharePoint 2010 Performance Planning & Extranet Topology

  1.      Which extranet topology to go with in case of exposing Intranet to Remote Employees / Partners ?   My understanding:   There are 3 extranet topologies : Edge Firewall , Back to Back , Split back to back   If Scenario is : a)      Only Employees needs to access Intranet from Home ,we can go for Edge Firewall b)      If partners & Employees , both needs to access Intranet ( it will have some partner sites ) , then we can go for Back to Back OR Split back to back .   Questions: ·         Is Understanding correct ? ·         Is there any factor which can help in making a decision between Back to Back Or Split back to back ?   2.      As part of sizing , I read that we should capture following information -          What should be maximum acceptable CPU utilization in production servers ? ( Example : 70%) -          What will be the concurrent number of users accessing portal during peak hour of a day ? ( Example 1000 users from total of 18000 users ) -          How many request per second are done to portal

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.
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