On Sunday 14 March 2010 11:08 PM, Mike Orr wrote:
On Sun, Mar 14, 2010 at 12:17 AM, Krishnakant Mane<[email protected]> wrote:
Now since database is going to be hit by so many queries, having an ORM
tested and tryed to that extent is very crutial.
SQLAlchemy can handle it because the person who wrote SQLAlchemy
builds those kind of applications for a living. But an ORM may not be
the best approach. The more an application is calculation-intensive
and dealing with complex queries, the more it makes sense to push the
entire queries into the database behind stored procedure, and
input/output only the minimum necessary data. The database can do
calculations much faster than Python can, and there's overhead in
bringing values out of the database, and even more overhead to convert
them to ORM instances. All that overhead is lost if you just use the
values for further queries.
Agreed, but there is a chance that we might make this application RDBMS
independent. I would surely be releasing the web application as free
software and making it depend on One database software is not really
free in terms of freedom.
And as you know many people have there database choices and I don't want
to make my software a reason for another flame war.
So I don't want to use stored procedures specific to any RDBMS.
But since sqlalchemy as I can see from some research is used on pritty
heavy applications, they might be doing some thing similar like what you
mentioned about the sql builder.
SQLAlchemy has a SQL builder level for composing queries directly, and
a low-impact result object that's less overhead than creating ORM
instances. This is the way to go for bulk processing lots of data;
e.g., to insert thousands of records at once, or to iterate through a
large result set. Most ORMs do not have this lower level, which is one
reason why SQLAlchemy is so popular. The ORM level is really only
suited for dealing with a few records at a time.
IC, so to keep the software db neutral and still have good performance,
using the sql builder will be the way to go.
The first is to try to do only a few queries per request. If you're
performing a dozen queries or more, try to combine them into fewer
queries because each query adds overhead. If you need a result from
several related tables and you've set up the relations in SQLAlchemy's
ORM, you can use SQLAlchemy's "eager join" option to get the whole
thing with one join query rather than issuing a separate query for
each subtable as it's needed. (But if you *don't* need the data in
the subtables, leave eagerjoin off so that it only queries the main
table.)
I am confused on this.
So should I not have relations setup in the ORM or should I set up those
relations in the ORM and then use the query builder?
And yes, I will have to compromise on stored procedures for making my
application DB Neutral.
SO I think I must do a bit more reading on the SQLAlchemy docs.
correct me if I am wrong, but my conclusion right now is to use a
combination of ORM based mapped classes and low level query builder in
the right places.
happy hacking.
Krishnakant.
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
