SqlDataAdepter vs Manual Data binding - Performance question.

Discussion in 'ASP .Net' started by Aykut Canturk, Aug 6, 2008.

  1. As all we know there are 2 ways for binding gridview to sql table. using
    sqldata adapter and using code. What I want to know is, in large tables, is
    there a perfomance difference. When using code we don't specify any page
    size or anything. so all data must be retreived from sql table to web server
    memory, is that right ? Maybe, only maybe, sql data adapter uses different
    approach about loading table rows for perfomance issues.

    in VB6, with classic ado, there were some concepts like getting some data
    first and shot it to user, in grid for example, then if user scrolls down,
    we get neccesseary data. also we could see this in components, after showing
    data on screen, when you observe scrollbar, you could understand that data
    is still loading while you work.

    in classic asp we did paging, for example using IDs and pagesize commands.
    but again we would load limited amount data into memory.

    ado.net changed the concept and I don't understand how memory never gets
    full or there is no performance issue when I open dataset into memory with
    code. espescially microsoft encourage us to use multiple datasets, data
    tables, disconnected recordsets,.... and all these eats up memory and
    performance right ?

    any explanation or suggestion wihich approach is better when working with
    large tables and grids ? Or any reliable article suggestion on that ?

    thanks.
     
    Aykut Canturk, Aug 6, 2008
    #1
    1. Advertising

  2. Aykut Canturk

    Guest

    On 6 août, 11:10, "Aykut Canturk" <> wrote:
    > As all we know there are 2 ways for binding gridview to sql table. using
    > sqldata adapter and using code. What I want to know is, in large tables, is
    > there a perfomance difference. When using code we don't specify any page
    > size or anything. so all data must be retreived from sql table to web server
    > memory, is that right ? Maybe, only maybe, sql data adapter uses different
    > approach about loading table rows for perfomance issues.
    >
    > in VB6, with classic ado, there were some concepts like getting some data
    > first and shot it to user, in grid for example, then if user scrolls down,
    > we get neccesseary data. also we could see this in components, after showing
    > data on screen, when you observe scrollbar, you could understand that data
    > is still loading while you work.
    >
    > in classic asp we did paging, for example using IDs and pagesize commands..
    > but again we would load limited amount data into memory.
    >
    > ado.net changed the concept and I don't understand how memory never gets
    > full or there is no performance issue when I open dataset into memory with
    > code. espescially microsoft encourage us to use multiple datasets, data
    > tables, disconnected recordsets,.... and all these eats up memory and
    > performance right ?
    >
    > any explanation or suggestion wihich approach is better when working with
    > large tables and grids ? Or any reliable article suggestion on that ?
    >
    > thanks.


    Just one point about your sentence "When using code we don't specify
    any page
    size or anything". I usually build my tables manually and not through
    binding, but I do specify a page size and number of rows through code,
    like this for instance :

    SELECT ID, Name FROM (SELECT ROW_NUMBER() OVER (ORDER BY Name) AS
    RowNumber, * FROM MyTable) AS Result WHERE RowNumber BETWEEN 10 AND 20

    This way, you only return the rows you're interested in. Obviously,
    you have to handle pagination yourself instead of relying on the
    framework, but performance-wise, I'm pretty much on par with a
    standard gridview binding (which, by the way, if correctly configured,
    will also limit the number of rows you're working on at one point in
    time). So, memory and performance aren't hampered.

    PS : the code above is for SQL 2005, it was way more convoluted with
    SQL 2000.

    Michel
     
    , Aug 7, 2008
    #2
    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. Jordan
    Replies:
    2
    Views:
    2,560
    Jordan
    Feb 10, 2004
  2. Thomas Nick
    Replies:
    0
    Views:
    1,953
    Thomas Nick
    Jun 13, 2005
  3. Leon Mayne

    ObjectDataSource vs manual binding

    Leon Mayne, Feb 6, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    880
    Eliyahu Goldin
    Feb 6, 2008
  4. gnewsgroup
    Replies:
    2
    Views:
    298
    gnewsgroup
    Feb 12, 2008
  5. JcFx
    Replies:
    0
    Views:
    302
Loading...

Share This Page