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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.