amby
Sat Jul 19 11:41:46 CDT 2003
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
"Stephen Muecke" <stevejo@senet.com.au> wrote in message
news:uanI1MrRDHA.2408@TK2MSFTNGP10.phx.gbl...
> 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
>
> "Ajay [MVP .NET]" <ajay.kumar@polaris.co.in> wrote in message
> news:074901c346a6$27db3a20$a501280a@phx.gbl...
> > 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
> >
> >
> >
> > >-----Original Message-----
> > >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
> > >
> > >
> > >.
> > >
>
>