Re: Decouple SQL queries from class in OOP design


EricF wrote:
> In article <1132861141.979015.217210@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>, "Mikito Harakiri" <mikharakiri_nospaum@xxxxxxxxx> wrote:
> >EricF wrote:
> >> I had a contract position with a major telco. One of the application had
> >> terrible performance. There was a query that joined about 25 tables. Duh! I
> >> denormalized the database and ended up with a join of ... maybe 5 to 7
> > tables.
> >> The performance was quite acceptable. (Turns out my manager designed the
> >> database but that's another story. Let's just say I wasn't there long ;-)
> >
> >The number of joins is not a problem. Siebel queries, for example are
> >often join more than 30 tables. They execute in milliseconds. I've seen
> >45 table join in Oracle Apps. Complex query doesn't have to be slow. It
> >is the optimization of complex query that is challenging.
>
> The front end was a web app and the SQL was dynamic. That certainly slowed it
> down. But there was not a lot of data - I think the largest table had less
> than 500 rows. But it's possible the query had problems - it's real easy to
> get a catersian product with a lot of tables.

No, it's not. When there is a lot of tables, optimizer search space is
huge. It can't simply afford to investigate all join order
permutations, in general, and join orders with cartesian products, in
particular. Db implementations differ, so I can't speak for all of
them, of course. The big three would give you some minimal quality
optimiser, at least.

.