Re: Recursive queries in slow database

On Jan 13, 3:03 am, "Dr. Coffee" <dr.coff..._at_gmail.com> wrote:
> On Jan 11, 7:13 pm, Roy Hann <specia..._at_processed.almost.meat> wrote:
>
>
>
>
>
>
>
>
>
> > Dr. Coffee wrote:
> > > Hi all.
>
> > > I am the user of a database, which is ridiculously slow for
> > > even the simplest queries.  The vendor of the db soes a lot
> > > of stuff that I am deeply suspicious of:
>
> > > - They don't hire staff with SW/dB experience, but general
> > >   MSc's and give them OJT.
>
> > > - They have had no resources to provide proper OJT on SW/dbs
>
> > > - They use one generic db model with a large number of clients
>
> > > - Because of the generic architecture they use variables to
> > >   indicate data type in the table, slowing queries down
>
> > > - They claim to have come up with a particular nifty trick
> > >   for recursive calls into the db, that they claim to be
> > >   sole users of
>
> > > All in all, I suspect these people are amateurs and dillettantes
> > > who have no idea what they are doing. I would love to hire pros
> > > to re-do the work these people are not able to, but in order to
> > > do that I will need to come up with convincing arguments to
> > > support a claim that there are severe problems.
>
> > > Any hints on how to proceed?
>
> > I assume "OJT" is on-the-job-training?  You claim they claim to do
> > that training but don't?   One place to dig would be wherever you think
> > there is evidence to support that view.
>
> > Google for, and read about the Entity-Attribute-Value (EAV) Model, which
> > is what your description of their approach sounds like.
>
> The Wikipedia EAV page
>
> http://en.wikipedia.org/wiki/Entity-attribute-value_model
>
> certainly describes my situation with some accuracy.
>
> > You will find
> > ample discussion of why it is bad--not the least reason being that its
> > practitioners are reimplementing the very thing an SQL DBMS is designed
> > to do.
>
> ...which happens to be one of my main reasons for complaint
> wrt the product in question...
>
> > It is invariably done only partially too, leaving out almost
> > all of the important stuff a DBMS does, like providing data integrity
> > checks, transaction isolation, etc.  
>
> You wouldn't happen to know of some key words / phrases / acronyms
> to search for, that would guid me to alternatives to EAVs...?
>
> DoC

The use of EAV is also aimed at keeping your information dense. Let's say that you have a database of patients and that a patient can have about two hundred different attributes, but on average a patient only has ten of these filled with information. Such sparse data would using many different modeling techniques yield a lot of NULL values in the database, but not using EAV.

In the case of flexibility the EAV does not provide anything that cannot be done using another technique. The benefit in EAV is not that it is more flexible, but that the cost for implementing a change is low. However, I do agree that EAV should be avoided at all costs, so what is the solution?

There is a modeling technique called Anchor Modeling in which you will never store NULL values to represent missing information. Missing information is instead represented by the absence of rows, and therefore take up no space. In AM flexibility (in the sense of low cost when implementing changes) is achieved through a high degree of normalization. I am not sure if it is right for you, but all available information can be found at http://www.anchormodeling.com. Received on Thu Mar 10 2011 - 12:53:39 CET