Re: ANN: Query A Dataset Beta Download

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I agree with both of you. ;)
The approach I often recommend is to upload to a permanent "work"
table--which has no constraints. Next, run a SP that merges the data and
reports inconsistencies. This protects the data integrity and keeps
performance high as well...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Sahil Malik [MVP]" <contactmethrumyblog@xxxxxxxxxx> wrote in message
news:e$Zf6RQbFHA.724@xxxxxxxxxxxxxxxxxxxxxxx
>I meant rollback is toss the dataset out the window i.e. set it to null and
> move on :-)
>
> --
>
> - Sahil Malik [MVP]
> Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
> ----------------------------------------------------------------------------
> ---------------
>
> "Sahil Malik [MVP]" <contactmethrumyblog@xxxxxxxxxx> wrote in message
> news:Oa8T$JPbFHA.3328@xxxxxxxxxxxxxxxxxxxxxxx
>> > For initial bulk loads, no problem. Though for every subsequential
>> > load, i.e. there is already data in the db, you're a fool if you
>> > disable constraints during load times.
>>
>> <--- That is when you'd use this trick in DBs (and of course you can
>> rollback). Ditto for datasets - the initial load is when you'd use it
> (your
>> rollback is to toss the database out the window :) ).
>>
>> - Sahil Malik [MVP]
>> Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
>> --------------------------------------------------------------------------
> --
>>
>>
>> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in message
>> news:xn0e3agg53qpks002@xxxxxxxxxxxxxxxxxxxxx
>> > Sahil Malik [MVP] wrote:
>> >
>> >> Okay I gotta rush somewhere so I'll reply quickly -
>> >>
>> >> Why disable constraints when u have them. This is a conventional
>> >> database trick. If you disable constraints before a bulk data load,
>> >> you save some SERIOUS time in the entire load process. That is why.
>> >
>> > yeah duh, I know that ;) but the reason I mentioned was: if you do
>> > that, you will see a constraint violation AFTER the complete load is
>> > done. Perhaps you're loading 100 tables with thousands of rows and a
>> > constraint violates somewhere in the beginning. You can then terminate
>> > the transaction and fix it. If you re-apply the constraint AFTERWARDS,
>> > your whole database is inconsistent and you'd better have some serious
>> > backups ready.
>> >
>> > If you don't want constraints to kick in when doing things with the
>> > database, don't define them, otherwise, why bother? They're there for a
>> > reason, compromising for that reason is equal to not defining them in
>> > the first place.
>> >
>> > For initial bulk loads, no problem. Though for every subsequential
>> > load, i.e. there is already data in the db, you're a fool if you
>> > disable constraints during load times.
>> >
>> > That's why.:)
>> >
>> > FB
>> >
>> >>
>> >> - Sahil Malik [MVP]
>> >> Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
>> >> ----------------------------------------------------------------------
>> >> ------
>> >>
>> >> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in
>> >> message news:xn0e396as9fjm6000@xxxxxxxxxxxxxxxxxxxxx
>> >> > Sahil Malik [MVP] wrote:
>> >> >
>> >> >> Of course it depends on what you use it for. I do maintain that for
>> >> >> Readonly data, datasets are an overkill. I wish datasets were
>> >> written >> in a plugin model architecture that allowed you to make
>> >> them as heavy >> or as light as you wanted them to be.
>> >> >
>> >> > I don't think they're overkill per se. In fact, readonly lists
>> >> > especially fit nicely in the table-oriented approach the dataset
>> >> > implies. What should be done in that stage of course is no
>> >> > constraints whatsoever so data addition through a fetch is fast.
>> >> >
>> >> > Plugin's are a nice idea, though with a generic purpose container,
>> >> > it leads to problems I think: the generic object has no meaning, the
>> >> > semantical meaning it has depends on the data it holds. So plugging
>> >> > in a plugin with behavior can help, but which one to plug in, and
>> >> > what if you load different data into the dataset?
>> >> >
>> >> >> The dataset contains datatables as an arraylist in 1.0 - which
>> >> means >> adding is fast, and removing is a pig. 2.0 implements it as
>> >> a red >> black tree - ditto for DataRows.
>> >> >
>> >> > what's a red black tree?
>> >> >
>> >> >> Now if you had 3 datatables and you were worried about constraints,
>> >> >> you could always use the BeginLoadData method to prevent constraint
>> >> >> checking on every row added - otherwise - Hey you asked for the
>> >> >> constraints :-), so don't crib about something you asked for,
>> >> >> especially if datasets do give you a workaround ( just like
>> >> databases >> .. wink wink ;) )
>> >> >
>> >> > heh, but why disable constraints if you have them...
>> >> >
>> >> >> The two operations in which I completely agree - datasets are pigs
>> >> >> are - GetChanges and Merge. The decrease in performance is
>> >> >> exponential with the # of FK-constraints and tables inside the
>> >> >> dataset. For 2,3 tables it's not such a big deal, but when you have
>> >> >> 5, 10, 40 datatables (Oh yes I've seen 40 tables in a "catch all
>> >> >> strongly typed dataset" in an enterprise app - yes I puked too) -
>> >> >> that is when you start getting serious performance drop.
>> >> >
>> >> > true. Give people a large hall and they will be able to fill it to
>> >> > the roof with crap. :)
>> >> >
>> >> > FB
>> >> >
>> >> > >
>> >> >> - Sahil Malik [MVP]
>> >> >> http://codebetter.com/blogs/sahil.malik/
>> >> > >
>> >> > >
>> >> >> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in
>> >> >> message news:xn0e37p9p50lef000@xxxxxxxxxxxxxxxxxxxxx
>> >> >> > Sahil Malik [MVP] wrote:
>> >> >> >
>> >> >> >> I agree Bill !!! Datasets have a lot of undeserved bad
>> >> impression >> >> about them - which in my mind is expressed by the
>> >> phrase "Little >> >> knowledge is a dangerous thing".
>> >> >> >
>> >> >> > Though it depends on what you do with the dataset. A normal
>> >> dataset >> > with 1 datatable and no constraints, yes that's
>> >> efficient. The >> > point is what's it doing after 3 tables with a
>> >> lot of data are >> > added and the constraints set have to be checked
>> >> each time you add >> > a row. That's a whole different thing.
>> >> >> >
>> >> >> > A dataset by itself is very basic. It contains a list of tables
>> >> >> > (hashtable probably), then each datatable has a collection of
>> >> >> > columns and a collection of rows. The datarow itself is an array
>> >> of >> > object, so very fast to fill with data, and the row collection
>> >> >> > itself is an arraylist so very fast to add a row. The indexer on
>> >> >> > the datarow uses the table's columns collection to find back a
>> >> >> > column, if names are specified, but for the rest, it's a very
>> >> basic >> > setup, which doesn't have a lot of bottlenecks. The main
>> >> issues >> > start when multiple tables with a lot of data are added
>> >> and you're >> > adding data which has to be verified through
>> >> constraints, that's a >> > whole different thing.
>> >
>> >
>> > --
>> > ------------------------------------------------------------------------
>> > Get LLBLGen Pro, productive O/R mapping for .NET:
>> > http://www.llblgen.com
>> > My .NET blog: http://weblogs.asp.net/fbouma
>> > Microsoft MVP (C#)
>> > ------------------------------------------------------------------------
>>
>>
>
>


.

Relevant Pages

  • Re: How to disable foreign key constraints in a stored procedure?
    ... constraints, perform some actions, and then re-enable them. ... My suggestion would be to re-think your 'reasoning' for disabling the ... Suppose you want to bulk load data into database - the data is coming ... Could you guarantee no other connected user ...
    (comp.databases.oracle.server)
  • Re: How to disable foreign key constraints in a stored procedure?
    ... constaints there) and you want the quickest load time (perhaps this is a ... constraints in place. ... Disable the constraints before the bulk load ...
    (comp.databases.oracle.server)
  • Re: OK, its the matter of database structure rather
    ... handle any possible, albeit unlikely, load of data, aside from the ... extra mental effort required to think things all the way through. ... constraints in maintenance. ... There is no such thing as "all the way through" in real life ...
    (comp.lang.tcl)
  • Re: ANN: Query A Dataset Beta Download
    ... Sahil Malik [MVP] wrote: ... If you disable constraints before a bulk data load, ... > you save some SERIOUS time in the entire load process. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... Yes, that's all facts are, though constraints mean that your facts are ... value of a candidate key determines the values of all other attributes, ... The frame of reference for a candidate key is a ...
    (comp.databases.theory)