SQL Server Performance

Row_Number Over Partition Performance Issues

Discussion in 'ALL SQL SERVER QUESTIONS' started by shad_ow, Feb 12, 2014.

  1. shad_ow New Member

    I have the following query which takes around 20 minutes to execute for 1 million row result set. Is there any way to improve the performance on this query? I am using SQL Server 2012. I have an index on Volumes Table for ID, Volume and VolumeDay.

    select ID,
    VolumeDay,
    volume,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Volume, VolumeDay ASC) as rownumber
    From Volumes
  2. rahul.neekhra New Member

    Hi,

    You can take the data into temp table without using multiple columns in order by just writing like this

    select ID,
    VolumeDay,
    volume,
    ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY ID) as rownumber
    INTO #temp
    From Volumes

    then select the data from #temp like this
    SELECT * FROM #Temp
    ORDER BY ID, Volume, VolumeDay ASC

    That might help you.

    Thanks & regards,
    Rahul Neekhra
    Blog: www.sqlserverpeoples.com
  3. AJITH123 Member

    20 min is quite big...did you execute the query in the SQL server itself? if not there are chances of network latency.. also put a NOLOCK hint. if still need to look in to hardware side. What is you server configuration, database size and the file allocation?

Share This Page