Which alternative acheives best performance?

S

Stephen Muecke

VB.NET/SQL Server
Using datareader to create objects and populate collections (no datasets
involved)

Part of my object model looks like this (with corresponding tables in the
database with PrimaryKey/ForeignKey relationships)

Organisation
contains Projects
contains Contacts
contains Allowances
contains Adjustments
contains Adjustment Costs

Which of the following will provide better performance in terms of
downloading data?

Read all Projects belonging to the Organisation, then loop through each
Project and read all Contracts belonging to the Project,then loop through
each Contract and read all allowances belonging to the Contract etc.

OR

Amend my stored procedures to include multiple joins (up to 6 levels deep)
so that I read all Projects belong to the Organisation, then read all
Contracts belonging to the Organisation, then read all Allowance belonging
to the Organisation etc.

The first method means I can create the correct heirarchy as data is read
but involves many more calls to server and corresponding open/closing of the
reader.
The second method involves less (but more complex) calls. In addition, I
would need to create a temporary hashtable of each collection in order to
find out where objects lower down the model belong

Stephen
 
A

Ajay [MVP .NET]

Stephen,
The first alternative is the best one. But it requires
a certain amendment. Instead of bringing all the data at
one go, why cant we bring data on demand.
i.e. probably you can give the user what he wants to see,
in a tree kind of structure and then ask the user to click
which hierarchy he wants to see.

if there is one org say A, and contains 5 projects
aa,ab,ac,ad,ae and so on....let the user choose which
project he wants to bring the data. And infact based on
the app, he would want to see entire structure for each
project or he would want to see for each contact.
Depending on the app, then make this either of the
approach which you want to do.

This also reduces bringing up of one million records where
the user really wanted to see one record.

Yes!! the barter is the number of calls would increase,
but the calls are never in the same time, and they are
staggard. So you get the data (voila) and you also reduce
the number of data you have to bring from the database.

if you need more clarification lets discuss..

bye
ajay
 
S

Stephen Muecke

Thanks Ajay

The first method is the one I have been working on so I am pleased with your
answer.
In fact, one reason I was doing this is so I could load on demand as you say
(just start with the Projects and their Contracts), particularly as it is
unlikely that a user will need to work on more than a few child collections
of a Contract in any one session (I only gave you a small part of the object
model - It runs to 60 lines!)
Each organisation also has a collection of Tasks which need to be included
in the initial download.

To avoid having to re-open the connection unnecessarily (may be 1,000's of
users), I was considering adding a bit column to each table to indicate if
there are child records (eg. In the Contracts table, adding the following
columns HasAllowances, HasVariations, HasExtensions). This can then be
checked in the client application to determine there is any data to
download. Seems a small price to pay.

One other point I was considering, which I would appreciate any advice on.
Once certain types of the objects have been created and submited to the
database, they are rarely changed. For example, there may be 2000
Organisations but only occasionally will a new one be added or changed (say
they change their email address).
To avoid having to connect and download each one every time, would it be
worth writing them to a local file(XML?) on exiting the application and
reading them back in on startup, and only downloading those that have
changed since the last download (every record in every table has a
"LastModified" DateTime column whose value is generated using GetDate() in
the UPDATE command). I suspect this will be significantly faster?

Thanks again
Stephen
 
J

Jay B. Harlow [MVP - Outlook]

Stephen,
Martin Fowler's book "Patterns of Enterprise Application Architecture" from
Addison Wesley, has a number of patterns that may help you.

http://www.martinfowler.com/eaaCatalog/

I remember he had one example of retrieving the all the tables via a Join,
then split the data into the respective objects.

Unfortunately: I don't remember which pattern it was. The book actually
covers both of your suggestions and a third or fourth one also.

Either way the book should give you helpful information on creating a
solution for your problem.

The book is in Java & C#, however the concepts apply to VB.NET equally well.

Hope this helps
Jay
 
A

amby

great idea to add the tag for child rows presence
however no need to add a colum
you can use a simple sql subquery that will give you the current no of child
rows for the parent.

select ..... , (select count(*) from child where foriegn key =
parent.primarykey ) from parent table
 

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