taking python enterprise level?...
mrkafk at gmail.com
Thu Mar 4 13:34:39 CET 2010
Philip Semanchuk wrote:
> Well OK, but that's a very different argument. Yes, joins can be
> expensive. They're often still the best option, though. The first step
> people usually take to get away from joins is denormalization which can
> improve SELECT performance at the expense of slowing down INSERTs,
> UPDATEs, and DELETEs, not to mention complicating one's code and data
> model. Is that a worthwhile trade?
I'd say that in more than 99% of situations: NO.
More than that: if I haven't normalized my data as it should have been
normalized, I wouldn't be able to do complicated querying that I really,
really have to be able to do due to business logic. A few of my queries
have a few hundred lines each with many sub-queries and multiple
many-to-many joins: I *dread the thought* what would happen if I had to
reliably do it in a denormalized db and still ensure data integrity
across all the business logic contexts. And performance is still more
than good enough: so there's no point for me, as of the contexts I
normally work in, to denormalize data at all.
It's just interesting for me to see what happens in that <1% of situations.
> Depends on the application. As I
> said, sometimes the cure is worse than the disease.
> Don't worry about joins until you know they're a problem. As Knuth said,
> premature optimization is the root of all evil.
Sure -- the cost of joins is just interesting to me as a 'corner case'.
I don't have datasets large enough for this to matter in the first place
(and I probably won't have them that huge).
> PS - Looks like you're using Postgres -- excellent choice. I miss using it.
If you can, I'd recommend using SQLAlchemy layer on top of
Oracle/Mysql/Sqlite, if that's what you have to use: this *largely*
insulates you from the problems below and it does the job of translating
into a peculiar dialect very well. For my purposes, SQLAlchemy worked
wonderfully: it's very flexible, it has middle-level sql expression
language if normal querying is not flexible enough (and normal querying
is VERY flexible), it has a ton of nifty features like autoloading and
rarely fails bc of some lower-level DB quirk AND its high-level object
syntax is so similar to SQL that you quickly & intuitively grasp it.
(and if you have to/prefer writing some query in "low-level" SQL, as I
have done a few times, it's still easy to make SQLAlchemy slurp the
result into objects provided you ensure there are all of the necessary
columns in the query result)
More information about the Python-list