data perfomance?

[Scott Reynolds]

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]

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" <sc***@it-xenius.co.uk> wrote in message
news:Os******** ******@TK2MSFTN GP12.phx.gbl...

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]

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" <sc***@it-xenius.co.uk> wrote in message
news:Os******** ******@TK2MSFTN GP12.phx.gbl...

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]

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.Cre ateChildView or I can achieve
it?

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

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

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

myDS.Relations. Add("Users_Paym entsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Pa yments").Column s("UsersId"), False)

myDS.Relations. Add("Users_Orde rsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Or ders").Columns( "UsersId"), False)

UsersDV = NewBuildDS.Tabl es("Users").Def aultView

myDataGrid.Data Source = UsersDV
myDataGrid.Data Bind()

Regards,
Scott
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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]

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.Cre ateChildView or I can achieve
it?

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

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

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

myDS.Relations. Add("Users_Paym entsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Pa yments").Column s("UsersId"), False)

myDS.Relations. Add("Users_Orde rsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Or ders").Columns( "UsersId"), False)

UsersDV = NewBuildDS.Tabl es("Users").Def aultView

myDataGrid.Data Source = UsersDV
myDataGrid.Data Bind()

Regards,
Scott
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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]

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" <sc***@it-xenius.co.uk> wrote in message
news:%2******** *******@TK2MSFT NGP12.phx.gbl.. .

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.Cre ateChildView or I can achieve
it?

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

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

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

myDS.Relations. Add("Users_Paym entsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Pa yments").Column s("UsersId"), False)

myDS.Relations. Add("Users_Orde rsREL", _
myDS.Tables("Us ers").Columns(" Id"), _
myDS.Tables("Or ders").Columns( "UsersId"), False)

UsersDV = NewBuildDS.Tabl es("Users").Def aultView

myDataGrid.Data Source = UsersDV
myDataGrid.Data Bind()

Regards,
Scott
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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