Recursive queries in slow database

#11

Dr. Coffee wrote:

Quote:

When people start getting enthusiastic about EAV you usually start
hearing about how the database is "static" and "inflexible". *Well BS on
that. *There is nothing simpler or quicker than creating a new table in
an SQL database. Once the analysis is done, it takes seconds. Literally
seconds.

Hah! It seems your point supports my main position that the
vendors are amateurs who don't know hos to do their job...
I like the way this is going!

The inflexibility is in the application programmers. *They won't get off
their backsides to learn how to access the meta data in the system
catalogues--which is *always* there. *They are perfectly willing to
write dynamic applications that use metadata at run-time, but they
insist it has to be their own metadata from their own zany homegrown
catalogues. *It seems to be some kind of mad "not invented here" kind of
mentality.

Now this is becoming interesting. As an application programmer
who craves for APIs - what are you talking about? Where do I
look to learn more about this? (Yes, this is new to me.)

There are various proprietary APIs, but I was thinking specifically of
JDBC.

Quote:

There was some very slight reason to be wary of dynamic programming back
when we were using Cobol and C because dynamic SQL (which is a
legitimate part of the ANSI/ISO SQL standard) required a small amount of
understanding and was never (as far as I know) covered in any
introductory SQL books (e.g. SQL for Feral Idiots). *But today, when
everyone seems happy enough to grovel down to the level of API
programming it's all basically dynamic anyway, so all you need to do is
encourage them to get familiar with the SQL catalogues.

SQL *catalogues*? As opposed to SQL what? Where do I learn more?

Every SQL database, regardless of DBMS vendor, contains a set of
catalogue tables that describe the database. There is a table that
tells you what all the tables are called, who owns them, when they
were created, etc. etc. There will be another table that tells you
what all the columns of all the tables are, and the type of each, and
its size, etc. etc. There will be yet another table that tells you
what all the indexes are, and what columns are included in the index
key. And another telling you what the referential constraints are, and
so on and so on. Everything you could ever want to know about the
database is supposed to be documented in the database in tables that
can be queried using SQL just like any other table. Most products do a
pretty good job of fulfilling this requirement. Thus you can
relatively easily write programs that discover what the database looks
like so they can figure out how to use it. That's what EAV tries to
re-invent.

One problem with the database catalogue tables is that all products do
it differently because in spite of the existence of some standards for
the catalogues I don't think too many products even attempt to comply.
You might get tied to a specific DBMS product because of the way its
catalogues are designed. However most of the APIs provide the
same information in a product-independent form.

Here's a randonly chosen link to an article on DB2 UDB catalogues.
Maybe not the most obvious choice but it supports the idea that all
DBMSs provide (most) of this information, usually:
http://www.ibm.com/developerworks/da.../dm-0411melnyk

--
Roy

#12

On Jan 13, 3:03*am, "Dr. Coffee" <dr.coff... (AT) gmail (DOT) com> wrote:

Quote:

On Jan 11, 7:13*pm, Roy Hann <specia... (AT) processed (DOT) 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-...te-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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Recursive queries in slow database

comp.databases.theory comp.databases.theory

Re: Recursive queries in slow database - 01-13-2011 , 11:15 AM

Re: Recursive queries in slow database - 03-10-2011 , 05:53 AM