On Sun, Mar 14, 2010 at 12:17 AM, Krishnakant Mane <[email protected]> wrote: > Now the requirement is different. It is an on-line admission system which > is going to face heavy trafic and will almost always need to send complex > queries to the database and then calculate results, crunch values and > finally send the output. > 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. 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. There are several ways to structure your application to make it most efficient. I'm speaking in terms of SQLAlchemy, though the same principles would apply to any database library. 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.) If you have a stored procedure, you can invoke it via a SQL builder query and get back a low-level result set. This may be sufficient for your application. SQLAlchemy does not have any special support for stored procedures. You *may* be able to customize an ORM Query object to invoke the stored procedure, by adding custom result columns, but I'm not an expert on this. Aruynn Shaw has written an ORM specifically for PostgreSQL stored procedures, but I'm not sure if she's published it yet. See this thread: http://groups.google.com/group/pylons-discuss/browse_thread/thread/d3bf338ffc26f1b3/9e0943bbeb9c42b8?lnk=gst&q=Shaw#9e0943bbeb9c42b8 -- Mike Orr <[email protected]> -- 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.
