Menu
▾
▴
sqlobject-discuss
|
From: Oleg B. <ph...@ph...> - 2009-05-18 18:14:13
|
Hello! I'm pleased to announce version 0.10.6, a minor bugfix release of 0.10 branch of SQLObject. What is SQLObject ================= SQLObject is an object-relational mapper. Your database tables are described as classes, and rows are instances of those classes. SQLObject is meant to be easy to use and quick to get started with. SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite, Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB). Where is SQLObject ================== Site: http://sqlobject.org Development: http://sqlobject.org/devel/ Mailing list: https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss Archives: http://news.gmane.org/gmane.comp.python.sqlobject Download: http://cheeseshop.python.org/pypi/SQLObject/0.10.6 News and changes: http://sqlobject.org/News.html What's New ========== News since 0.10.5 ----------------- * Better support for Python 2.6: do not import the deprecated sets module. * Two bugs in SQLiteConnection.columnsFromSchema() were fixed: use sqlmeta.idName instead of 'id'; convert default 'NULL' to None. * Use sqlmeta.idName instead of 'id' in all connection classes. * Fixed a bug that prevented to override per class _connection if there is sqlhub.processConnection. For a more complete list, please see the news: http://sqlobject.org/News.html Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
|
From: Stef T. <st...@um...> - 2009-05-22 05:39:29
Attachments:
testSAObj.py
testSobj.py
|
Hey Oleg, Everyone,
Please don't take this as 'rude' but, I think that the thing
SQLObject development should -really- focus on is speed. Perhaps I am
doing something fundamentally wrong with the following code but, for the
life of me, I can't figure out why. I know your going to hate me saying
this but.. compare the SQLObject vs SQLAlchemy
Starting benchmarking with 100000 records (inserting and selecting)
.
Inserting 100000 records into SQLite(memory) with SQLAlchemy
Number of records selected: 100000
Time for SQLAlchemy with SQlite db in Memory: 2.71 seconds
Inserting 100000 records into SQLite(Disk) with SQLAlchemy
Number of records selected: 100000
Time for SQLAlchemy with SQlite db on Disk: 2.64 seconds
Versus
Starting benchmarking with 100000 records (inserting and selecting)
.
Inserting 100000 records into SQLite(Memory) with SQLObject
Number of records selected: 100000
Time for SQLObject with db in memory: 38.53 seconds
and when I change it to put the sqlite db onto the -exact- same disk as
SQLAlchemy used
Time for SQLObject with db on disk: 79.74 seconds
So, that's roughly 20 times slower for memory, and 40 times slower for
disk.
As I said, if I am doing something wrong in the example programs, please
do feel free to correct me, but, otherwise, the speed .. well ..
'sucks'. You may ask how 'likely' is 100k inserts, but the figures are
even -worse- for object instantiation from a database .. I mean a LOT
worse :( now, perhaps SQLObject does things like provide sqlmeta or ..
some such.. but.. 20 -times- slower is the trade off ? seems like a bad
idea to me :\
Ideas ? Thoughts ? Am I crazy ?
Regards
Stef
|
|
From: Sean D. <hal...@gm...> - 2009-05-22 05:58:32
|
Why are you subscribed to the list then?? What is it about SQLObject that makes you want the developers to speed it up? I'm a nobody that uses SQLObject for production work. What are you looking for? See below to learn something. Pronunciation: \ˈtakt\ Function:* noun*Etymology: French, sense of touch, from Latin *tactus,* from *tangere* to touch — more at tangent<http://www.merriam-webster.com/dictionary/tangent>Date: 17972 *:* a keen sense of what to do or say in order to maintain good relations with others or avoid offense On Thu, May 21, 2009 at 10:11 PM, Stef Telford <st...@um...> wrote: > Hey Oleg, Everyone, > Please don't take this as 'rude' but, I think that the thing SQLObject > development should -really- focus on is speed. Perhaps I am doing something > fundamentally wrong with the following code but, for the life of me, I can't > figure out why. I know your going to hate me saying this but.. compare the > SQLObject vs SQLAlchemy > > > Starting benchmarking with 100000 records (inserting and selecting) > . > Inserting 100000 records into SQLite(memory) with SQLAlchemy > Number of records selected: 100000 > > Time for SQLAlchemy with SQlite db in Memory: 2.71 seconds > > Inserting 100000 records into SQLite(Disk) with SQLAlchemy > Number of records selected: 100000 > Time for SQLAlchemy with SQlite db on Disk: 2.64 seconds > > > Versus > > > Starting benchmarking with 100000 records (inserting and selecting) > . > Inserting 100000 records into SQLite(Memory) with SQLObject > Number of records selected: 100000 > Time for SQLObject with db in memory: 38.53 seconds > > and when I change it to put the sqlite db onto the -exact- same disk as > SQLAlchemy used > > Time for SQLObject with db on disk: 79.74 seconds > > > > So, that's roughly 20 times slower for memory, and 40 times slower for > disk. > > As I said, if I am doing something wrong in the example programs, please do > feel free to correct me, but, otherwise, the speed .. well .. 'sucks'. You > may ask how 'likely' is 100k inserts, but the figures are even -worse- for > object instantiation from a database .. I mean a LOT worse :( now, perhaps > SQLObject does things like provide sqlmeta or .. some such.. but.. 20 > -times- slower is the trade off ? seems like a bad idea to me :\ > > Ideas ? Thoughts ? Am I crazy ? > > Regards > Stef > > > ------------------------------------------------------------------------------ > Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT > is a gathering of tech-side developers & brand creativity professionals. > Meet > the minds behind Google Creative Lab, Visual Complexity, Processing, & > iPhoneDevCamp asthey present alongside digital heavyweights like Barbarian > Group, R/GA, & Big Spaceship. http://www.creativitycat.com > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > |
|
From: Oleg B. <ph...@ph...> - 2009-05-22 09:46:47
|
On Fri, May 22, 2009 at 01:11:51AM -0400, Stef Telford wrote:
> SQLObject development should -really- focus on is speed.
There are many areas to improve SQLObject - code, tests, documentation,
development process, communication skills of the developers. Let's add
speed to the list.
But well, it is open source, developers scratch their itches, and speed
is not our biggest itch.
SQLObject certainly isn't optimized for mass insertion, this is a well
known fact. SQLObject does a lot of thing behind the scene. For mass
insertion it is better to use SQLBuilder lower-level API (underdocumented,
yes).
You are welcome to start a project to improve SQLObject. Or at least you
can help by explaining how SA optimizes mass insertion. On an INSERT
SQLObject does:
-- creates an instance;
-- sends INSERT query;
-- sends SELECT query to get the row back - this is necessary to get
back autogenerated fields - autoincremented IDs, timestamps, system
variables and such; this is what slows down SQLObject so much;
-- caches the row in its internal cache; this is what makes SQLObject to
eat memory; SQLObject periodically clears the cache thus spending
even more time.
What SA does and does not do?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Stef T. <st...@um...> - 2009-05-22 16:53:50
|
Hello again Oleg On 05/22/2009 05:44 AM, Oleg Broytmann wrote: > On Fri, May 22, 2009 at 01:11:51AM -0400, Stef Telford wrote: > >> SQLObject development should -really- focus on is speed. >> > There are many areas to improve SQLObject - code, tests, documentation, > development process, communication skills of the developers. Let's add > speed to the list. > But well, it is open source, developers scratch their itches, and speed > is not our biggest itch. > I know that everyone always see's -their- problem as the biggest. This is human nature, although I would argue that people will notice speed before anything else (except documentation perhaps), but I understand your points. A lot could be done, it merely needs the manpower. > SQLObject certainly isn't optimized for mass insertion, this is a well > known fact. SQLObject does a lot of thing behind the scene. For mass > insertion it is better to use SQLBuilder lower-level API (underdocumented, > yes). > actually, I am not entirely sure that the speed problem is due to not being optimised for mass inserts. I notice on most webpages which have a lot of objects (over 1k) that object instantiation is the 'slow' part. I do notice a fair amount of evals of lambda's that are going on in the guts of the system. Those are -never- fast. I am wondering if there is perhaps something else going on during an init that you could think may slow things down ? For what it's worth, I am unsure how a select from a database could be slowing things down when the database is entirely in memory. I would expect that to be the -last- thing to slow the system down. Perhaps that's my guessing but.. yes. Again, I know tone is often hard to convey, but I am not blaming nor attacking anyone. I -do- use SQLObject on a daily basis and thank you and Ian for all the work done. I am merely wondering where things 'slow down' so much and if there is anything we can do about it :( Regards Stef |
|
From: Stef T. <st...@um...> - 2009-07-22 15:46:25
|
Oleg Broytmann wrote:
> On Fri, May 22, 2009 at 12:52:52PM -0400, Stef Telford wrote:
>
>> I am merely wondering where things 'slow
>> down' so much and if there is anything we can do about it :(
>>
>
> Can you run your test program under the python profiler and find out the
> slow spot(s)?
>
> Oleg.
>
Hello Oleg, Everyone,
Sorry for the delay in replying.. "real life" sort of got in the
way. Deepest apologies for that.
When I benchmarked the SQLObject page of 7000+ objects (give or take
a few hundred), I got the 80-90 seconds. With plain python objects, I
got the 0.3 seconds (obviously a lot less going on, so, not surprising
there). However, when I took out the evals inside sqlobject/main.py, I
got the page speed to around 15-20 seconds.
So, this raises the question, is it perhaps better to -not- use
eval(..) and instead tinker with the objects __attributes__ or __dict__
? I am not -that- conversant/knowledgeable in pythons black magic of
object meta programming but.. I definitely think that if we can get away
from calling 'evals' when doing object instantiation that would be a
HUGE performance win.
And, in the process of putting my money where my mouth is, I am more
than willing to donate $200 bounty for the fix, either to the person or
charity of their choosing.
Seriously :)
Regards and thanks
Stef
(ps. I -do- have a large PNG of the system during a single 'page
impression'. It's about 7mb .. I can send it offlist if your interested ?)
|
|
From: Oleg B. <ph...@ph...> - 2009-07-22 16:49:30
|
Hi!
On Wed, Jul 22, 2009 at 11:19:36AM -0400, Stef Telford wrote:
> Hello Oleg, Everyone,
> Sorry for the delay in replying.. "real life" sort of got in the way.
> Deepest apologies for that.
No problem. I understand the issue and feel your pain, sure.
> When I benchmarked the SQLObject page of 7000+ objects (give or take
> a few hundred), I got the 80-90 seconds. With plain python objects, I
> got the 0.3 seconds (obviously a lot less going on, so, not surprising
> there). However, when I took out the evals inside sqlobject/main.py, I
> got the page speed to around 15-20 seconds.
Aha, that's the results! Thank you!
In what way have you "took out the evals"? You cannot just remove them so
you probably replaced them? with what?
> So, this raises the question, is it perhaps better to -not- use
> eval(..) and instead tinker with the objects __attributes__ or __dict__
> ? I am not -that- conversant/knowledgeable in pythons black magic of
> object meta programming but.. I definitely think that if we can get away
> from calling 'evals' when doing object instantiation that would be a
> HUGE performance win.
That requires some thinking and experimenting. Perhaps those evals
could be replaced with functools.partial - that requires python 2.5, so it
can be implemented after SQLObject 0.11 (which I'm gonna release RSN).
> And, in the process of putting my money where my mouth is, I am more
> than willing to donate $200 bounty for the fix, either to the person or
> charity of their choosing.
Wow, what a generous offer! Thank you very much!
> (ps. I -do- have a large PNG of the system during a single 'page
> impression'. It's about 7mb .. I can send it offlist if your interested
> ?)
A PNG of the system? What's that? I don't understand...
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Stef T. <st...@um...> - 2009-07-22 17:54:42
|
Hello again Oleg, Everyone Oleg Broytmann wrote: > Hi! > > On Wed, Jul 22, 2009 at 11:19:36AM -0400, Stef Telford wrote: > >> Hello Oleg, Everyone, >> Sorry for the delay in replying.. "real life" sort of got in the way. >> Deepest apologies for that. >> > > No problem. I understand the issue and feel your pain, sure. > > *chuckles* this is kind of the problem with OS projects. We use them almost 100% here at work but try to spend any -actual- time on improving them and.. well.. yes. >> When I benchmarked the SQLObject page of 7000+ objects (give or take >> a few hundred), I got the 80-90 seconds. With plain python objects, I >> got the 0.3 seconds (obviously a lot less going on, so, not surprising >> there). However, when I took out the evals inside sqlobject/main.py, I >> got the page speed to around 15-20 seconds. >> > > Aha, that's the results! Thank you! > > In what way have you "took out the evals"? You cannot just remove them so > you probably replaced them? with what? > > actually.. I -did- remark them out. As I said, all the evals do (I -believe-) is to add the lambda into the python dictionary with a specific name for getattr to find (I am probably horribly mangling pythonic terms here, and I apologise). I have read in numerous places that python's eval is hysterically slow, and I think that this is somewhat borne out. I know that (obviously) the code is missing so it's not a straight 1<->1 in terms of execution, but, yes. evals appear to be a 'bad' thing here :\ >> So, this raises the question, is it perhaps better to -not- use >> eval(..) and instead tinker with the objects __attributes__ or __dict__ >> ? I am not -that- conversant/knowledgeable in pythons black magic of >> object meta programming but.. I definitely think that if we can get away >> from calling 'evals' when doing object instantiation that would be a >> HUGE performance win. >> > > That requires some thinking and experimenting. Perhaps those evals > could be replaced with functools.partial - that requires python 2.5, so it > can be implemented after SQLObject 0.11 (which I'm gonna release RSN). > > functools partial ? isn't this another name for decorators ? I tend to get rather lost in python black magic as I said (sorry :). This said, stating python 2.5 as a 'baseline' is not a -bad- idea. It's been out in one form or another since sep 2006.. that's not exactly cutting edge ;) I am still wondering though, can't we simply look into the python object's dictionary and place functions into there ? if there is an existing _SO_get_Blah and a user defined _SO_get_Blah then can't python rename the existing one and then include a call at the end to the newly placed one onto the end ? I know, this is monkey patching but.. isn't that what we are doing with the evals anyway ? >> And, in the process of putting my money where my mouth is, I am more >> than willing to donate $200 bounty for the fix, either to the person or >> charity of their choosing. >> > > Wow, what a generous offer! Thank you very much! > > Not a problem. I -do- use SQLObject day to day, and I figure that since my python skills would probably offend everyone should I try, then the least I can do is put my money where my mouth is :) >> (ps. I -do- have a large PNG of the system during a single 'page >> impression'. It's about 7mb .. I can send it offlist if your interested >> ?) >> > > A PNG of the system? What's that? I don't understand... > > It's a PNG of the profiling of a single 'page'. I profiled it and then passed it through gprof2dot ( http://code.google.com/p/jrfonseca/wiki/Gprof2Dot ) let me know if you want it.. it -is- a VERY large PNG though :) > Oleg. > Regards Stef |
|
From: Oleg B. <ph...@ph...> - 2009-05-22 17:32:35
|
On Fri, May 22, 2009 at 12:52:52PM -0400, Stef Telford wrote:
> I am merely wondering where things 'slow
> down' so much and if there is anything we can do about it :(
Can you run your test program under the python profiler and find out the
slow spot(s)?
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Matthew W. <ma...@tp...> - 2009-05-22 18:22:23
|
On Fri 22 May 2009 05:44:24 AM EDT, Oleg Broytmann wrote:
> On Fri, May 22, 2009 at 01:11:51AM -0400, Stef Telford wrote:
>> SQLObject development should -really- focus on is speed.
>
> There are many areas to improve SQLObject - code, tests, documentation,
> development process, communication skills of the developers. Let's add
> speed to the list.
> But well, it is open source, developers scratch their itches, and speed
> is not our biggest itch.
> SQLObject certainly isn't optimized for mass insertion, this is a well
> known fact. SQLObject does a lot of thing behind the scene. For mass
> insertion it is better to use SQLBuilder lower-level API (underdocumented,
> yes).
> You are welcome to start a project to improve SQLObject. Or at least you
> can help by explaining how SA optimizes mass insertion. On an INSERT
> SQLObject does:
> -- creates an instance;
> -- sends INSERT query;
> -- sends SELECT query to get the row back - this is necessary to get
> back autogenerated fields - autoincremented IDs, timestamps, system
> variables and such; this is what slows down SQLObject so much;
> -- caches the row in its internal cache; this is what makes SQLObject to
> eat memory; SQLObject periodically clears the cache thus spending
> even more time.
> What SA does and does not do?
The original comparison isn't fair. There are two differences between
the SQLAlchemy (SA) approach and the SQLObject (SO) approach.
1. The SA code does one single insert statement and the SQLObject code
does a separate query for each object.
2. The SO code instantiates a class after inserting each row. The SA
code doesn't instantiate anything.
It is possible to do get SO to act like SA, but like Oleg says, it is
underdocumented.
Here's how I do a big insert with SO:
def bulky_insert(cls, insert_these):
"""
Does a single insert, cramming everything from insert_these,
which must be a list of dictionaries, and each dictionary must
be like:
d = dict(shift_template_id=sr.shift_templateID,
shift_time_id=new_st.id,
shift_type_id=sr.shift_typeID,
location_id=sr.locationID,
build_id=bld.id,
required_staff=sr.required_staff)
"""
cls._connection.query(cls.sqlrepr(
Insert(cls.sqlmeta.table, insert_these)))
The cls is a subclass of the typical SQLObject class. I build a list of
dictionaries, where each dictionary has a key named with the column name
and the value is (duhh...) the value for that column name.
This does a single big insert and doesn't return anything. No classes
are instantiated. This is *really* fast.
I find myself using SQLBuilder tools *a lot*. Maybe I should read the
code and see if I can help at least write some documentation for others,
because I've had to figure out most of this stuff through trial and
error.
Matt
|
|
From: Stef T. <st...@um...> - 2009-05-22 21:04:08
|
On 05/22/2009 02:21 PM, Matthew Wilson wrote:
> <snip>
> I find myself using SQLBuilder tools *a lot*. Maybe I should read the
> code and see if I can help at least write some documentation for others,
> because I've had to figure out most of this stuff through trial and
> error.
>
> Matt
>
Hello Matt, Everyone,
I am wondering.. do you notice any speed issue's when doing
instantiations of objects (complex or otherwise) ? I have a page that
instantiates about 7000 booking objects. When I replace them with mocks
(or pypo's) then I see the page render in about 0.3 seconds.. when I use
SQLObject it goes upto about 80-90 seconds ... curiously, replacing the
SQLObjects with SQLAlchemy objects I got the page rending in 2 seconds.
This is why I was thinking it was object instantiation rather than
mass inserts that was 'slow' .. perhaps my test doesn't display the
slowness I am seeing, although I maybe hard pressed to come up with a
test case that adequately replicates the slowdown :(
Regards
Stef
(ps. I am not 'knocking' SO in anyway, nor saying SA is "great".. merely
wondering if it's only me being "special" :D
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X