Speeding up GridView

Discussion in 'ASP .Net' started by tshad, Feb 4, 2010.

  1. tshad

    tshad Guest

    I have a GridView in my pages that is very slow since I am sending all the
    data back from server and letting the GridView handle the paging.

    I need to change that since in one of my projects I could have a couple
    thousand rows sent back.

    Where does the GridView put the data when it is doing the paging?

    Does it call the Sql Server each time it pages it?

    I am planning to change the code so that the paging is done on the server.
    My Sql would look something like:

    With PagedResults AS
    (
    SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
    FROM HumanResources.vEmployee
    )
    SELECT *
    FROM PagedResults
    WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50

    Where the Order By and row number would be dynamic.

    Would I have to set this up as a dynamic SQL to accomplish this?

    Or could I do something like:

    With PagedResults AS
    (
    SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
    FROM HumanResources.vEmployee
    )
    SELECT *
    FROM PagedResults
    WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow

    Thanks,

    Tom
     
    tshad, Feb 4, 2010
    #1
    1. Advertising

  2. The datasource control is retrieving the entire results, and then displaying
    only those for the current page.

    Set your data source to an object, and there is an option to have arguments
    for the starting record and number of records. Your method will then need to
    retrieve only those rows from the database.

    This is critical for any database of substantial size.

    --
    Jonathan Wood
    SoftCircuits Programming
    http://www.softcircuits.com

    "tshad" <> wrote in message
    news:...
    > I have a GridView in my pages that is very slow since I am sending all the
    > data back from server and letting the GridView handle the paging.
    >
    > I need to change that since in one of my projects I could have a couple
    > thousand rows sent back.
    >
    > Where does the GridView put the data when it is doing the paging?
    >
    > Does it call the Sql Server each time it pages it?
    >
    > I am planning to change the code so that the paging is done on the server.
    > My Sql would look something like:
    >
    > With PagedResults AS
    > (
    > SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    > OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
    > FROM HumanResources.vEmployee
    > )
    > SELECT *
    > FROM PagedResults
    > WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50
    >
    > Where the Order By and row number would be dynamic.
    >
    > Would I have to set this up as a dynamic SQL to accomplish this?
    >
    > Or could I do something like:
    >
    > With PagedResults AS
    > (
    > SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    > OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
    > FROM HumanResources.vEmployee
    > )
    > SELECT *
    > FROM PagedResults
    > WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow
    >
    > Thanks,
    >
    > Tom
    >
    >
     
    Jonathan Wood, Feb 4, 2010
    #2
    1. Advertising

  3. tshad

    Mr. Arnold Guest

    tshad wrote:
    > I have a GridView in my pages that is very slow since I am sending all the
    > data back from server and letting the GridView handle the paging.
    >
    > I need to change that since in one of my projects I could have a couple
    > thousand rows sent back.
    >
    > Where does the GridView put the data when it is doing the paging?
    >
    > Does it call the Sql Server each time it pages it?


    I would control the loading of the Gridview on subsets of data being
    returned, like Lastname starts with "A" with a Like statement. Starting
    with Lastname starts with "A" would be the first subset seen by the
    user. They would be given a TextBox they would use to give "B" or "C" or
    "T".

    You give the illusion of speed, by not bringing back all the data, only
    subsets.
     
    Mr. Arnold, Feb 4, 2010
    #3
  4. On Feb 4, 1:33 am, "tshad" <> wrote:
    > I have a GridView in my pages that is very slow since I am sending all the
    > data back from server and letting the GridView handle the paging.
    >
    > I need to change that since in one of my projects I could have a couple
    > thousand rows sent back.
    >
    > Where does the GridView put the data when it is doing the paging?
    >
    > Does it call the Sql Server each time it pages it?
    >
    > I am planning to change the code so that the paging is done on the server..
    > My Sql would look something like:
    >
    > With PagedResults AS
    > (
    >  SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    > OVER (ORDER BY LastName, FirstName) AS ResultSetRowNumber
    >  FROM HumanResources.vEmployee
    > )
    > SELECT *
    > FROM PagedResults
    > WHERE ResultSetRowNumber > 40 AND ResultSetRowNumber <= 50
    >
    > Where the Order By and row number would be dynamic.
    >
    > Would I have to set this up as a dynamic SQL to accomplish this?
    >
    > Or could I do something like:
    >
    > With PagedResults AS
    > (
    >  SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle, ROW_NUMBER()
    > OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
    >  FROM HumanResources.vEmployee
    > )
    > SELECT *
    > FROM PagedResults
    > WHERE ResultSetRowNumber > @StartRow AND ResultSetRowNumber <= @EndRow
    >
    > Thanks,
    >
    > Tom


    I think it makes sense to do a custom paging with only records you
    need to show on the current page (like you told above). In many cases
    user will not go to the next page, so why to return more than he want
    to see?
     
    Alexey Smirnov, Feb 4, 2010
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Spamtrap

    Need some hints on speeding up

    Spamtrap, Aug 11, 2004, in forum: Perl
    Replies:
    1
    Views:
    376
    Jim Gibson
    Aug 12, 2004
  2. Troy

    Speeding up page display

    Troy, Jan 21, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    519
    George Ter-Saakov
    Jan 22, 2004
  3. OHM

    Speeding up pages

    OHM, May 24, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    361
  4. =?Utf-8?B?TWF0dA==?=

    Speeding up a DataGrid with XML

    =?Utf-8?B?TWF0dA==?=, May 20, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    475
    =?Utf-8?B?TWF0dA==?=
    May 20, 2005
  5. Harald Hein

    Speeding up Swing application

    Harald Hein, Oct 27, 2003, in forum: Java
    Replies:
    6
    Views:
    528
    Daniel Dyer
    Oct 28, 2003
Loading...

Share This Page