2

I have ASP.NET website which performance is very important. This website works but with low performance. I have to increase performance of this project.

First I see paging in the grids in the report of this project. I have to say database of this project is SQL Server 2000 and I used temp table for paging, but I think with this stored procedure. I fetch all records then I chose 10 rows of this result and I think it's not efficient. Because it fetches 50000 rows but choses 10 of them.

I want to just get time 10 rows each time. We want to change SQL Server version to 2008 and I want to use paging methods in the 2008 version. I searched a lot. I find this stored procedure but I think it's same as my first query; fetch all rows then use row number.

I want to know what's the best stored procedure for paging in SQL Server 2008 in a large database? Any tips or tricks would be welcome. Thanks in advance.

This is my first paging method :

USE AdventureWorks
GO
DECLARE  @StartRow INT
DECLARE  @EndRow INT
    SET    @StartRow = 120
    SET @EndRow = 140


CREATE TABLE #tables (RowNumber INT IDENTITY(1,1),
                FirstName VARCHAR(100), LastName VARCHAR(100),
                EmailAddress VARCHAR(100))
INSERT INTO #tables (FirstName, LastName, EmailAddress)
    SELECT    PC.FirstName, PC.LastName, PC.EmailAddress
    FROM    Person.Contact PC
    ORDER BY FirstName, LastName, EmailAddress

SELECT    FirstName, LastName, EmailAddress
    FROM    #tables
    WHERE    RowNumber > @StartRow
        AND RowNumber < @EndRow
DROP TABLE #tables

and this second in SQL Server 2008:

SELECT * FROM ( 
     SELECT 
          ROW_NUMBER() OVER (ORDER BY fdate) AS row, * 
     FROM somthing 
) AS a WHERE row BETWEEN 1 AND 10
sara Sodagari
  • 423
  • 1
  • 9
  • 23
  • 1
    SP with CTE instead of temptable & has sorting, paging and filtering: http://pranavwithyou.wordpress.com/2013/01/07/stored-procedure-having-sorting-paging-and-filtering/ – Pranav Singh May 20 '14 at 09:46
  • thanks for your answer but when i see all ways .i think all of them first fetch all records then in where clause say if row number or offset or index is between this number and that number. i want to know we have to fetch all data or can we fetch just 10 record. in your example link you fetch all record in CTE table, then chose one page with row number .yes ?am i right or not ? – sara Sodagari May 20 '14 at 10:02
  • 1
    Anyway query has to look all records in any case. Performance depends on how much data is filtered, returned & how much indexes are used in execution. Make sure proper indexes are there & fragmentation is less. When you say you fetch all records, does this means you transport all data to ASP.net? If so its issue. – Pranav Singh May 20 '14 at 10:11
  • do you see the second query it uses row number no cte or temp table i want to know performance of your way is better than this because in this way it dos not use any CTE and temp table – sara Sodagari May 20 '14 at 10:11
  • no when i say fetch all record because when i insert my result in temp table or CTE or in second query use inner select then in this situation i get all my result then i use row number or other way to get one page of it and send this 1 page in my application.i want to know when i fetch all result when my result is big then it take time and i want to know can i decrease this time ? – sara Sodagari May 20 '14 at 10:16
  • why you chose CTE in this link i see temp table vs cte performance http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables – sara Sodagari May 20 '14 at 10:20
  • Inserting data into temp table without any filtering or where condition is just overhead. Instead use original table if you are using whole records(it will use indexing thus good performance). CTE is just way to access data in memory & not on hard-disk as in temptable is stored in tempdb. – Pranav Singh May 20 '14 at 10:28
  • ok thanks for your answer and why you didn't use of top in your query i see it lose time to execution it use by row number ? – sara Sodagari May 20 '14 at 10:35

1 Answers1

0

You have a good idea to consider if it is faster to
-pull all rows somewhere and then filter
-pull only limited rows.

You're also on track to realize the queries with TOP or Row_Number may still also pull all rows first.

If you want to explicitly control how many rows you pull, you need to manage the paging parameters in your application, and only run a query on the next range.

That may or may not perform faster for your application. It depends on the total number of records, your paging size, and network speed... and application architecture (do you have a good cache solution? Can you share the results? ...? )

Mike M
  • 1,382
  • 11
  • 23