Re: OTLT again

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:1100346530.005279.31280_at_z14g2000cwz.googlegroups.com...
> Zsolt Ujvari wrote:
> > I searched on the subject and I know/expect to be trashed but I just
> have to
> > pop it up anyway because I didn't see any discussions on speed
> gains/losses
> > with a OTLT, only that you loose constraints, data type checks etc.
> >
> > Today, we have a db structure with 16 lookup tables, all of them with
> > "value-description" structure. These are all connected with FK to a
> > "document" table, and that means that when you want all the data on
> the
> > document (like the "values" from the lookups) you have to join 16
> tables (+
> > some additional tables). So it's pretty big join and is slow. Here
> comes the
> > first contradiction for me, eg. you should not have more then 4
> tables in a
> > join, and having lot's of lookup tables instead of OTLT...
>
> You are joking right? Who ever said you shouldn't join more than 4
> tables in a query? That's chicken feed to a serious DBMS (and you
> mention MS and Oracle below, which are serious, unless you meant MS
> Access?) In any case, with OTLT you will still have the same number of
> joins, except that many of them will reference the same table.

Yes, I'm talking about MS SQL Server. The number of 4 on joins came from db guru working with huge ERP databases and they cashed/denormalized tables not to have more than 4 joins because performance. But the general "max" number is pretty low - not even close to 16+. If you ask for tuning tip on your query on a NG and you show the sql with 16 joins what do you think that the respons/suggestion will be? :) We even had to drop Sybase support because they had max 15-16 joins in one query (don't remember exactly) and we had 16 lookup + some more tables. They fixed it by now but it was a real limit. I'm not saying that the current dbs can't handle this join, it's just pretty slow.

If I would have the results in the same format back (like one row per document) then I guess I need the same amount of joins, but if I get more rows per document (one for each lookup) than the nr. of joins is constant. Of course i will have handle this new type of results in the code...

> > In the next version of the app, the users have to be able to create
> > unlimited number of lookups - and that's the main reason for posting
> here.
> > Continuing today's design would mean dinamically creating new tables
> (we
> > support MS and Oracle but is possible to do it i guess) and joining
> to these
> > tables for the values. Potensially join on 100 tables??? I guess you
> > understand why I think OTLT looks tempting here, simple joins, new
> lookups
> > are added by inserting some rows in two tables. I would have a new
> table
> > that connects the "document" to the lookup values table, but I can
> live with
> > that ...
>
> Well if the requirements really are for users to invent new lookup
> tables - and lots of them - then probably you do need OTLT. But I
> can't imagine why that would be the case. Of course users have to
> create new data, but why new "tables"? Also, if the query requires 100
> joins, it will also require 100 joins with OTLT - most of them to the
> same table.

They do need to create new lookups, but probably not 100 on one document type but maybe up to 30. And then they need an other 30 for a different doc. type etc. But it has to be flexible. And as you mentioned, maybe the OTLT is the only sollution in this case and have to live with that...

> Regarding performance, have you actually tested your theory that OTLT
> gives better query performance? I bet it doesn't actually!

I didn't test it yet, I thought that I get some feedback before I go head dow testing it, maybe find out that is faster but has some big drawbacks that I find after a year in prod when is no way back.

Thanks Received on Sat Nov 13 2004 - 14:11:15 CET