Hello,
Tahnks to you and Mike for detaild insight, My questions follow
in-line.On Friday 07 August 2015 08:48 PM, Claudio Freire wrote:
On Fri, Aug 7, 2015 at 12:05 PM, kk <krm...@gmail.com> wrote:
On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote:
Hello.
ORM is certainly slower. How much depends A LOT on your workload. For
example
bulk operations with ORM are an order of magnitude slower than raw SQL. On
the
other hand, SQLAlchemy Core let's you write generative SQL queries without
ORM features which are as performant as raw SQL.
I am going to be mostly with Postgresql for now so I don't wish database
independent queries. Perhaps we will shift to NoSql in near future
(more on that in some time ).
So if I were to write core queries then I could as well do directly with
psycopg2. What advantage I will then get by using SQLAlchemy?
So is is there some kind of a method to have some prepared sql statements in
SQLAlchemy itself?
I have seen that prepared statements in other languages like Java do a great
job.
Depending on the dialect and driver, you can use executemany.
Not all drivers implement executemany with prepared statements though,
check your case.
Ok, so is this good for some kind of bulk inserts and Updates? Or does
it have any other not so obvious performance bennifit?
Overall SQLAlchemy is an excellent
library to work with!
So you mean performance will really get hit when pure ORM is used.
So shold I use a mixture?
For inserts let's say orm and for bulk select queries some prepared
statement like thing (if it exists )?
It really depends on each use case.
If your concern is whether ORM queries will be efficient, don't worry,
SQLAlchemy is powerful enough that you can make almost any kind of
query with the ORM. Almost all the optimizations you could do to plain
SQL are doable at the ORM level.
I see, So can you give me some example of this kind of optimization
which I should particularly look at? I have seen docs on lazy joine
etc. Are there some other efficiency and performance tricks I can do?
I have many queries which get data from views and some times have to do
joins, such data is really bulk, in the magnitude of 50000 records and
is repeated 2 or 3 times at a go.
Inserts are not very very frequent and even if they do, the records in
one insert would be hardly one in master and 2 in the detail table from
a single user.
And not more than 25 or 30 users are inserting at one time.
If your concern is CPU overhead on the application side, yes, the ORM
does induce quite an overhead, but whether it's a problem or not
greatly depends on your use case, the number of objects your
transactions will be handling, the complexity of the mapping, your
latency and thoughput constraints, etc. I've convinced myself over
time that a little overhead is fine in exchange for the benefits the
ORM gives you, in ease of coding mostly, but also robustness (the ORM
solves some issues that are hard to handle correctly and robustly with
raw SQL), and SQLAlchemy is flexible enough that you can usually
escape to raw sql if/when you need to. You shouldn't optimize
prematurely, the ORM won't be a death trap as it happens with other
ORMs.
We have good server and I don't think CPU overhead is to much of a
concern given the use case which I have already mentioned.
There is fair bit of complicated calculations going on big result sets
but not as complicated as scientific ones. These are mostly financial
calculations such as those in preparing a ledger statement in a book
keeping software.
So, we're saying we need more information if we're to give a meaningful answer.
So I have provided enough information I guess.
By the way we are thinking of moving to either couch db or the
postgresql's jsonb datatype for most of our work in near future.
Any thing you can specially advice me as far as using SQLAlchemy on such
data?
Happy hacking.
Krishnakant.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
