data perfomance?

Discussion in 'ASP .Net' started by Scott Reynolds, Mar 1, 2005.

  1. Hello!

    I developed a web application to display results from the database. Now I
    need to add search function, to search, sort and filter data.

    My question is, which way is better...

    1) Store all related data in a single DataSet and use DataView to filter and
    sort data

    OR

    2) Join related data to a single results set and filter data on the database
    side, then store already filtered data in DataSet.

    Database structure:

    Table "Users" (parent) ~ 5000 records
    Table "Orders" (child)
    Table "Payments" (child)

    I am using MS Access database.

    All suggestions are welcome!

    Scott
    Scott Reynolds, Mar 1, 2005
    #1
    1. Advertising

  2. Scott Reynolds

    Karl Seguin Guest

    Scott,
    It's a pretty hard question to answer since it's depend on a lot of factors
    we don't know, namely the size of data (I mean, if you have huge text
    fields, it's very likely the pure in memory solution won't work) and how
    it's use (# of users).

    here's what I can tell you:
    - Often times there's a 20/80 rule in play where 20% of activity account for
    80% of performance implication. If you can identify this 20% and optimize
    it (cache) you'll likely hit the right balance.
    - See how much memory is used by storing all data in-memory and make sure
    you don't come too close to causing an application restart (by default it's
    60% of your total RAM))...if you can't store all the data, then this option
    won't be an alternative and you'll have answered your own question
    - Consider doing a "lazy load". This is similar to the first point, where
    you load the most frequent and small data in memory and only go to the
    database for the more infrequent stuff. Such an example is with a search
    functionally. You might cache the search criterias as well as the id and
    name/title to quickly display the result, but go to the database for the
    much larger data such as the content/description (this works particularly
    well for "result" pages and "detail" pages). Also note, Detail pages can
    often make use of OutputCache so you can have your cake and eat it too
    - Finally, you mention Access, I'll assume that you don't plan on having
    large amounts (or even small amounts) of concurrent users. Much like I
    recommend you test how much data you can store to see if you'll exceed the
    limit, you'll need to test your databases capability to handle numerous
    requests. If it isn't up to the job you'll need to levarage a more
    in-memory solution.

    Karl


    --
    MY ASP.Net tutorials
    http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
    annoying)
    http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
    come!)


    "Scott Reynolds" <> wrote in message
    news:...
    > Hello!
    >
    > I developed a web application to display results from the database. Now I
    > need to add search function, to search, sort and filter data.
    >
    > My question is, which way is better...
    >
    > 1) Store all related data in a single DataSet and use DataView to filter

    and
    > sort data
    >
    > OR
    >
    > 2) Join related data to a single results set and filter data on the

    database
    > side, then store already filtered data in DataSet.
    >
    > Database structure:
    >
    > Table "Users" (parent) ~ 5000 records
    > Table "Orders" (child)
    > Table "Payments" (child)
    >
    > I am using MS Access database.
    >
    > All suggestions are welcome!
    >
    > Scott
    >
    >
    Karl Seguin, Mar 1, 2005
    #2
    1. Advertising

  3. Hello Karl

    Thank you for good suggestions.

    Although the next question is off this topic, but maybe you could help me
    little more...?

    Lets say that I would like to display all Sellers who have at least 3
    products.... then I must use DataRowView.CreateChildView or I can achieve
    it?

    I am using code below to fill DataSet and create Relations:
    .....
    myDA = New OleDbDataAdapter("select * from Users", myConn)
    myDA .Fill(myDS, "Users")

    myDA = New OleDbDataAdapter("select * from Payments", myConn)
    myDA .Fill(myDS, "Payments")

    myDA = New OleDbDataAdapter("select * from Orders", myConn)
    myDA .Fill(myDS, "Orders")

    myDS.Relations.Add("Users_PaymentsREL", _
    myDS.Tables("Users").Columns("Id"), _
    myDS.Tables("Payments").Columns("UsersId"), False)

    myDS.Relations.Add("Users_OrdersREL", _
    myDS.Tables("Users").Columns("Id"), _
    myDS.Tables("Orders").Columns("UsersId"), False)

    UsersDV = NewBuildDS.Tables("Users").DefaultView

    myDataGrid.DataSource = UsersDV
    myDataGrid.DataBind()

    Regards,
    Scott


    "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
    wrote in message news:%...
    > Scott,
    > It's a pretty hard question to answer since it's depend on a lot of
    > factors
    > we don't know, namely the size of data (I mean, if you have huge text
    > fields, it's very likely the pure in memory solution won't work) and how
    > it's use (# of users).
    >
    > here's what I can tell you:
    > - Often times there's a 20/80 rule in play where 20% of activity account
    > for
    > 80% of performance implication. If you can identify this 20% and optimize
    > it (cache) you'll likely hit the right balance.
    > - See how much memory is used by storing all data in-memory and make sure
    > you don't come too close to causing an application restart (by default
    > it's
    > 60% of your total RAM))...if you can't store all the data, then this
    > option
    > won't be an alternative and you'll have answered your own question
    > - Consider doing a "lazy load". This is similar to the first point,
    > where
    > you load the most frequent and small data in memory and only go to the
    > database for the more infrequent stuff. Such an example is with a search
    > functionally. You might cache the search criterias as well as the id and
    > name/title to quickly display the result, but go to the database for the
    > much larger data such as the content/description (this works particularly
    > well for "result" pages and "detail" pages). Also note, Detail pages can
    > often make use of OutputCache so you can have your cake and eat it too
    > - Finally, you mention Access, I'll assume that you don't plan on having
    > large amounts (or even small amounts) of concurrent users. Much like I
    > recommend you test how much data you can store to see if you'll exceed the
    > limit, you'll need to test your databases capability to handle numerous
    > requests. If it isn't up to the job you'll need to levarage a more
    > in-memory solution.
    >
    > Karl
    Scott Reynolds, Mar 1, 2005
    #3
  4. Scott Reynolds

    Karl Seguin Guest

    To do it in memory you can create a DataView and set the filter
    property....i think this is the better solution. Atlernatively, you could
    use the SELECT feature of the datatable, but this resutls an array of
    datarows which isn't as nice to work with as a dataview..

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
    annoying)
    http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
    come!)
    "Scott Reynolds" <> wrote in message
    news:%...
    > Hello Karl
    >
    > Thank you for good suggestions.
    >
    > Although the next question is off this topic, but maybe you could help me
    > little more...?
    >
    > Lets say that I would like to display all Sellers who have at least 3
    > products.... then I must use DataRowView.CreateChildView or I can achieve
    > it?
    >
    > I am using code below to fill DataSet and create Relations:
    > ....
    > myDA = New OleDbDataAdapter("select * from Users", myConn)
    > myDA .Fill(myDS, "Users")
    >
    > myDA = New OleDbDataAdapter("select * from Payments", myConn)
    > myDA .Fill(myDS, "Payments")
    >
    > myDA = New OleDbDataAdapter("select * from Orders", myConn)
    > myDA .Fill(myDS, "Orders")
    >
    > myDS.Relations.Add("Users_PaymentsREL", _
    > myDS.Tables("Users").Columns("Id"), _
    > myDS.Tables("Payments").Columns("UsersId"), False)
    >
    > myDS.Relations.Add("Users_OrdersREL", _
    > myDS.Tables("Users").Columns("Id"), _
    > myDS.Tables("Orders").Columns("UsersId"), False)
    >
    > UsersDV = NewBuildDS.Tables("Users").DefaultView
    >
    > myDataGrid.DataSource = UsersDV
    > myDataGrid.DataBind()
    >
    > Regards,
    > Scott
    >
    >
    > "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
    > wrote in message news:%...
    > > Scott,
    > > It's a pretty hard question to answer since it's depend on a lot of
    > > factors
    > > we don't know, namely the size of data (I mean, if you have huge text
    > > fields, it's very likely the pure in memory solution won't work) and how
    > > it's use (# of users).
    > >
    > > here's what I can tell you:
    > > - Often times there's a 20/80 rule in play where 20% of activity account
    > > for
    > > 80% of performance implication. If you can identify this 20% and

    optimize
    > > it (cache) you'll likely hit the right balance.
    > > - See how much memory is used by storing all data in-memory and make

    sure
    > > you don't come too close to causing an application restart (by default
    > > it's
    > > 60% of your total RAM))...if you can't store all the data, then this
    > > option
    > > won't be an alternative and you'll have answered your own question
    > > - Consider doing a "lazy load". This is similar to the first point,
    > > where
    > > you load the most frequent and small data in memory and only go to the
    > > database for the more infrequent stuff. Such an example is with a

    search
    > > functionally. You might cache the search criterias as well as the id

    and
    > > name/title to quickly display the result, but go to the database for the
    > > much larger data such as the content/description (this works

    particularly
    > > well for "result" pages and "detail" pages). Also note, Detail pages

    can
    > > often make use of OutputCache so you can have your cake and eat it too
    > > - Finally, you mention Access, I'll assume that you don't plan on

    having
    > > large amounts (or even small amounts) of concurrent users. Much like I
    > > recommend you test how much data you can store to see if you'll exceed

    the
    > > limit, you'll need to test your databases capability to handle numerous
    > > requests. If it isn't up to the job you'll need to levarage a more
    > > in-memory solution.
    > >
    > > Karl

    >
    >
    Karl Seguin, Mar 1, 2005
    #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. Gnanaprakash Rathinam

    CreateInstranceAndUnwrap slow perfomance

    Gnanaprakash Rathinam, Dec 29, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    2,118
    David Levine
    Dec 30, 2004
  2. Fredrik Melin

    Server perfomance

    Fredrik Melin, Oct 27, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    412
    Paul Glavich [MVP - ASP.NET]
    Oct 27, 2004
  3. George

    Huge HTML output perfomance

    George, Mar 28, 2005, in forum: ASP .Net
    Replies:
    10
    Views:
    672
    Robbe Morris [C# MVP]
    Mar 29, 2005
  4. James T.

    Perfomance test

    James T., Feb 26, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    404
    James T.
    Feb 26, 2006
  5. iksrazal
    Replies:
    2
    Views:
    348
    enrique
    Apr 27, 2005
Loading...

Share This Page