none
MS-SQLSERVER - SQL Query using ROW_NUMBER taking long time with 0 result from query

    Question

  •     select autoid from (SELECT ROW_NUMBER() OVER (ORDER
        BY MYTABLE.DETECTEDUTC DESC) as rownum, autoId from
        MYTABLE where MYTABLE.guid in (..guids..)) as tab1 where rownum >=1000 and rownum < 1020 


    We are having a table MYTABLE which may contain millions of records currently it is having 10 million records.
    above SQL query used to get paginated data in our code, it works fine till query giving results, but hangs for hours if query returning 0 results.
    Also SQL server start consuming system RAM while running above query and which is not returning any record.

    on the other hand following query works fine with 0 results -

        select autoid from (SELECT ROW_NUMBER() OVER (ORDER
        BY MYTABLE.DETECTEDUTC DESC) as rownum, autoId from
        MYTABLE where MYTABLE.guid in( ..guids..)) as tab1

    • Moved by Stephanie Lv Thursday, November 10, 2011 1:51 AM (From:SQL Server Data Access)
    Wednesday, November 09, 2011 4:11 AM

Answers

  • Hi Ashish Gupta India,

    Could you please post the table structure here?

    ROW_NUMBER is really a flexible hand handy method in this scenario. For large data, it might be expensive since it needs to sort data. In this case, you may have a try to use SET ROWCOUNT with appropriate indexes on the table. For the usage of SET ROWCOUNT in this scenario, please have a look at the last sample on this article: A More Efficient Method for Paging Through Large Result Sets.


    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Wednesday, November 16, 2011 6:59 AM
    • Marked as answer by Stephanie Lv Sunday, November 20, 2011 4:23 AM
    Friday, November 11, 2011 8:47 AM

All replies

  • Hi Ashish Gupta India,

    Could you please post the table structure here?

    ROW_NUMBER is really a flexible hand handy method in this scenario. For large data, it might be expensive since it needs to sort data. In this case, you may have a try to use SET ROWCOUNT with appropriate indexes on the table. For the usage of SET ROWCOUNT in this scenario, please have a look at the last sample on this article: A More Efficient Method for Paging Through Large Result Sets.


    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Wednesday, November 16, 2011 6:59 AM
    • Marked as answer by Stephanie Lv Sunday, November 20, 2011 4:23 AM
    Friday, November 11, 2011 8:47 AM
  • might have some missing index can boost query performance -

    http://uk.linkedin.com/in/ramjaddu
    Friday, November 11, 2011 10:47 AM
  • Hi Ashish,
    See if the below script works. I just tried to reduce the number of records on which ROW_NUMBER is to be applied
    I am taking 100 records additional than the maxRowNum you try to filter (as an additional buffer) and applying ROW_NUMBER on top of that result. As ROW_NUMBER (in this case) does just provide running count of records in resultset I hope the expected output result doesn't change with the below snippet as well.
     
     
    DECLARE @cnt INT
    DECLARE @maxRowNum INT
    DECLARE @minRowNum INT
    SET @minRowNum = 1000
    SET @maxRowNum = 1020
    SET @cnt = @maxRowNum + 100
    
    SELECT	AutoId
    FROM	(
    			SELECT	ROW_NUMBER() OVER(ORDER BY MYTABLE.DETECTEDUTC DESC) RowNum, AutoId
    			FROM	(
    						SELECT	TOP(@cnt) *
    						FROM	MyTable
    						WHERE	MYTABLE.[guid] in (..guids..)
    						ORDER BY MYTABLE.DETECTEDUTC DESC
    					) MyTable
    		)tab1
    WHERE	RowNum >= @minRowNum AND RowNum < @maxRowNum
    Friday, November 11, 2011 8:20 PM