Using SQL Command version Fill Ranges for Self Paginated Load of DataTable RRS feed

  • Question

  • Okay,

    So I'm currently writing my own Wrapper class which will handle "Paged" loads of DataTables.  This is designed to basically alleviate the excessive load times for larger tables, and since there is no "cursor" based DataSet (like the old RecordSet) class in .Net, I need to write this myself.

    Not much of a problem, really, you get the Total Rows from the DataTable, via a SqlCommand.ExecuteScalar (on a SQL Count() stmt) or one could use LINQ to SQL to retrieve the count of a specific table in question.  Then Set the Select Statement of a SqlCommand and use a SqlDataAdapter to fill the table. 

    Given this Base SQL:
    SELECT Columns FROM (
        SELECT Columns, ROW_NUMBER() over (Order by PageCols) as RowNum
        FROM MyTable
    Naturally we can assume that the "Columns" in the statment above represents a comma delimited list of the Table's Columns.  and PageCols is an orderby clause's comma delimited list of column names (with ASC/DESC as appropriate).  This is to to allow the programmer (me) to devise in what sort order the pages should be aligned.  (ie: if I have orderstable that is a child to the Customers table, I might want to page fill the Orders table by sorting on the Customer ID not the Order ID)

    Assume PageSize = 1000

    My Question is which would be most efficient within the .Net framework to fill by page a specific DataTable():

    A) I could per "GetPage(PageNum)" call, reformat the SQL statement in the SqlDataAdapter.SelectCOmmand.CommandText with the appended
    "WHERE RowNum BETWEEN" &  (PageNum * PageSize) + 1 & " AND " & (PageNum + 1) * PageSize and then simply execute a SqlDataAdapter.Fill()

    B) I Could simple Set the Above Base SQL to the SelectCommand of the SqlDataAdapter and simple Execute a SqlDataAdapter.Fill(Table, (PageNum * PageSize) + 1, PageSize)

    One uses the SQL statement to limit which rows to select, and is handled outside the SqlDataAdapter within the actual SQL Transaction call to the database, the other uses the Internal SqlDataAdapters fill method to populate the table, and I'm curious which is more efficient and/or which is better recommended design for use with the .Net environment. 

    Jaeden "Sifo Dyas" al'Raec Ruiner
    "Never Trust a computer. Your brain is smarter than any micro-chip."
    Monday, July 13, 2009 4:28 PM


All replies

  • I can't find it right now but using RowNum is more efficient for the server and will take less time, the other method I believe returns the data but just doesn't add it. You can see this easily by looking at the queries that are issued in Sql Server. If you crawl around the web I believe you can find a site that has actual perf numbers comparing the two options.

    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 13, 2009 5:02 PM
  • Oh, really. 
    SO basically, I have a class that currently parses SQL commands into a fully object model, which I use for many process executions directly against the database (instead of having to handle the conversion two and from datasets/datacontexts, and one of them is for "filling" a datatable based upon the Select statement.  Currently I evaluation the size (count of rows) of the Table and then run the process breaking down the fill calls for progress updates (especially on larger selects).  This is all part of the SQL Editor mode of my first App, which allows the user to directly write SQL against the DB and execute it.  SO I can process updates, deletes, selects, etc.  When I perform a select (or other operation that returns rows) it fills a datatable and puts it in a results tab in a DGV beneath the editor.  My process currently does:

    while Rows < RowCount
      Rows +=  Adapter.Fill(Table, CurRow, 1000)
      CurRow += 1000
      UpdateProgress(Rows, RowCount)
    end while

    (basically, it is more complicated than that), but what your saying is that for a table of say 10000 rows, the above loop reselects all 10000 rows each execution of the Fill() method, it just doesn't save the other 9000 when it updates the datatable?

    "Never Trust a computer. Your brain is smarter than any micro-chip."
    Monday, July 13, 2009 5:29 PM
  • Take a look at the following connect bug

    As you can see it talks about the SqlDataAdapter and how it does not do this efficiently. I think RowNum is the way to go.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 14, 2009 12:44 AM