T-Sql ROW_NUMBER usage slows down query performance drastically, SQL 2008r2

Hi,
when used ROW_NUMBER () in the below query .. it takes 20 sec to return 35,000 rows,.

WITH test_CTE as(
SELECT
      ContractNum
    Pay_Date,
    InvoiceAmount,
    InvoiceNumber,
    InvoiceDate,
    [Source],
    FundingMst.CFDANumber
      
 FROM  View1      
)
select *
      , ROW_NUMBER()  OVER (ORDER BY InvoiceNumber) AS Row
      from test_CTE
        
-- this takes 20 sec for 35,000 records.
-- If Row_Number() commented, it only takes 2 sec.

I basically need a row number based on InvoiceNumber.. how should i tweek my query

Thanks for the help guys

Environmet:- Sql server 2008 r2
kishan66Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
OVER( ORDER BY NULL ) --<< for Oracle (sorry)
or
OVER( ORDER BY (SELECT 1) ) --<< for SQL Server

These are both "workarounds" that satisfy a syntax need for an ORDER BY clause inside the OVER().

As I understand it as there is no true basis for an order to be applied - so it doesn't actually apply an order, just accepting the order of rows as raw input - making it quicker. It is likely that the clustered index order is the order used (I have not tested this though).

---
{+ edit}
it's quite possible OVER( ORDER BY NULL ) doesn't work in SQL Server
(it does work for Oracle)

---
{+ edit 2}
use
OVER( ORDER BY (SELECT 1) ) for sql server
0
 
Saurabh Singh TeotiaCommented:
If you have a unique invoice number..then try rank..its comparatively faster...

select *
      , rank()  OVER (ORDER BY InvoiceNumber) AS Row
      from test_CTE

Open in new window


Saurabh...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define what you mean by 'I basically need a row number based on InvoiceNumber'.

>this takes 20 sec for 35,000 records.
>If Row_Number() commented, it only takes 2 sec.
Using ROW_NUMBER() means a sort, which are always expensive, and a numeric assignment based on that sort, so this is not surprising.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kishan66Author Commented:
Hi Saurabh,

Invoice# is not unique.

Hi Jim,
I basically wan to arrange the result set
    - order by InvoiceNum#
    - Identity each row with a unique number
0
 
PortletPaulfreelancerCommented:
You indicate that you are using a VIEW inside that CTE

1. It may be way more efficient to use source tables instead of that view.
2. That CTE is purely cosmetic, it is not technically required by the query you present here

Have you looked at the source query for that view? is using it really needed for this query?

I would suggest using source tables to see if that helps improve performance (as it may allow better access to indexes)

I would also NOT use CTEs for cosmetic reasons.

If nothing I suggest helps, provide the execution plan (& use SET STATISTICS IO ON when you do that)
You can attach execution plan (.sqlplan) files here for us to interpret

{+ edit}
Oh, one other question.

Does it matter what order is used for the unique number?

have you tried:
SELECT
      *
FROM (SELECT
            ContractNum
            Pay_Date
          , InvoiceAmount
          , InvoiceNumber
          , InvoiceDate
          , [Source]
          , FundingMst.CFDANumber
          , ROW_NUMBER() OVER (ORDER BY NULL) AS Row
      FROM View1) TST

Open in new window


if "order by null" does not work try:

"order by (select 1)"
0
 
kishan66Author Commented:
hi Portletpaul,

:)
"order by(Select 1)"  worked.
Also, I added order by invoice# at the end of select statement ..it only took 3 sec.

Can you pls explain me what actually happens when I give "Row_Number() Over (order by InvoiceNumber)" and why is it slow compared to "Row_Number() Over(order by (select 1)) ".

Thanks
0
 
PortletPaulfreelancerCommented:
Thanks. Just to confirm through a small test, the clustered index does act as the default sequence and no SORT operation is added when using OVER( ORDER BY (SELECT 1) )

execution plan using OVER( ORDER BY (SELECT 1) )order by (select 1)
execution plan using OVER( ORDER BY [some_field] )order by [a field]


details:
    CREATE TABLE ForgeRock
        ([productName] varchar(13), [description] varchar(120))
    ;
    
    create clustered index idx_clus_ForgeRock on ForgeRock(description);
    
    INSERT INTO ForgeRock
        ([productName], [description])
    VALUES
        ('OpenIDM', '05 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '06 Full-featured access management'),
        ('OpenIDM', '13 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '04 Full-featured access management'),
        ('OpenIDM', '07 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '08 Full-featured access management'),
        ('OpenIDM', '03 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '09 Full-featured access management'),
        ('OpenIDM', '02 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '10 Full-featured access management'),
        ('OpenIDM', '01 Platform for building enterprise provisioning solutions'),
        ('OpenAM', '11 ull-featured access management'),
        ('OpenDJ', '12 Robust LDAP server for Java')
    ;
    
**Query 1**:

    
    SELECT
      productName,
      description,
      row_number() over(order by (select 1)) as x
    FROM
      ForgeRock
      

**[Results][2]**:
    | productName |                                                description |  x |
    |-------------|------------------------------------------------------------|----|
    |     OpenIDM | 01 Platform for building enterprise provisioning solutions |  1 |
    |     OpenIDM | 02 Platform for building enterprise provisioning solutions |  2 |
    |     OpenIDM | 03 Platform for building enterprise provisioning solutions |  3 |
    |      OpenAM |                         04 Full-featured access management |  4 |
    |     OpenIDM | 05 Platform for building enterprise provisioning solutions |  5 |
    |      OpenAM |                         06 Full-featured access management |  6 |
    |     OpenIDM | 07 Platform for building enterprise provisioning solutions |  7 |
    |      OpenAM |                         08 Full-featured access management |  8 |
    |      OpenAM |                         09 Full-featured access management |  9 |
    |      OpenAM |                         10 Full-featured access management | 10 |
    |      OpenAM |                          11 ull-featured access management | 11 |
    |      OpenDJ |                             12 Robust LDAP server for Java | 12 |
    |     OpenIDM | 13 Platform for building enterprise provisioning solutions | 13 |
**Query 2**:

    
      
    SELECT
      productName,
      description,
      row_number() over(order by productName) as x
    FROM
      ForgeRock
      

**[Results][3]**:
    | productName |                                                description |  x |
    |-------------|------------------------------------------------------------|----|
    |      OpenAM |                         04 Full-featured access management |  1 |
    |      OpenAM |                         08 Full-featured access management |  2 |
    |      OpenAM |                         09 Full-featured access management |  3 |
    |      OpenAM |                         10 Full-featured access management |  4 |
    |      OpenAM |                          11 ull-featured access management |  5 |
    |      OpenAM |                         06 Full-featured access management |  6 |
    |      OpenDJ |                             12 Robust LDAP server for Java |  7 |
    |     OpenIDM | 13 Platform for building enterprise provisioning solutions |  8 |
    |     OpenIDM | 05 Platform for building enterprise provisioning solutions |  9 |
    |     OpenIDM | 01 Platform for building enterprise provisioning solutions | 10 |
    |     OpenIDM | 02 Platform for building enterprise provisioning solutions | 11 |
    |     OpenIDM | 03 Platform for building enterprise provisioning solutions | 12 |
    |     OpenIDM | 07 Platform for building enterprise provisioning solutions | 13 |

  [1]: http://sqlfiddle.com/#!6/62ccb/1

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.