data perfomance?

S

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
 
K

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!)
 
S

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.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
 
K

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!)
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top