[sqlalchemy] Re: Optimizing a slow query

[EricHolmberg]

It looks like your sub-select (before the joins) is processing up to
140,015 records, so that will slow things down since the database may
not optimize that sub-selection based upon your outer joins.  As a
quick check, try reducing the 140,000 offset to 0 (I know this won't
work for your application, but do this as a sanity check) in your
limit statement.  If that works, then try refactoring your database
(potentially with a timestamp like I explain later).

subselect:  (... WHERE forum_post.topic_id = 9250 ORDER BY
forum_post.id   LIMIT 14, 15)

Other improvements would include (as previously stated by Michael)
would be to make sure you have indexed all of the items in your WHERE,
ORDER BY, and ON clauses.  Also, check to see if you can do arrange
your web page such that you can reduce the amount of information
required initially which will reduce your query time.

Really, 198,398 records is pretty small, so even with a filesort, I
wouldn't expect this to take very long.  Have you double-checked that
your server's file system is running okay, that your DB tables aren't
fragemented, etc?  You can handle most of this optimization by doing
an OPTIMIZE table_name; query in MySQL.

If you're still out of ideas, consider putting a timestamp on the
records and indexing the timestamp.  You can then do a query based
upon a time range which I know is fast since I do it all the time on a
750-million record stock database.

-Eric
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Transactions and read-only operations

[Tomer]

If I am just issuing a read-only query, and I want to make sure that
nothing will change in the middle, do I need a transaction? If so,
what do I do with it when I'm done - commit or rollback?

For example, I want to do this:

for user in Session.query(User):
print user.name

Should I use a transaction like this:

Session.begin()
for user in Session.query(User):
print user.name
Session.commit()

If not, how would I deal with this if my session (created with
scoped_session) is transactional?

Thanks,
Tomer
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Transactions and read-only operations

[az]

On Saturday 07 June 2008 21:35:35 Tomer wrote:
 If I am just issuing a read-only query, and I want to make sure
 that nothing will change in the middle, do I need a transaction? If
 so, what do I do with it when I'm done - commit or rollback?

 For example, I want to do this:

 for user in Session.query(User):
 print user.name

 Should I use a transaction like this:

 Session.begin()
 for user in Session.query(User):
 print user.name
 Session.commit()
 If not, how would I deal with this if my session (created with
 scoped_session) is transactional?
if the __str__ happens to self-change (well, shit happens), then u 
should not do commit nrt flush. the safest is to rollback - 
regardless what u do. or maybe close() or clear()?
i think there was some way to make the session readonly alltogether -- 
but i'm not sure.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Transactions and read-only operations

[Tomer]

Thanks. What would happen if I didn't do anything (I've seen lots of
examples online that will just issue a query like
Session.query(User).all() and that's all). Will that query start a
transaction if it's a transactional session?

On Jun 7, 11:58 am, [EMAIL PROTECTED] wrote:
 On Saturday 07 June 2008 21:35:35 Tomer wrote:

  If I am just issuing a read-only query, and I want to make sure
  that nothing will change in the middle, do I need a transaction? If
  so, what do I do with it when I'm done - commit or rollback?

  For example, I want to do this:

  for user in Session.query(User):
      print user.name

  Should I use a transaction like this:

  Session.begin()
  for user in Session.query(User):
      print user.name
  Session.commit()
  If not, how would I deal with this if my session (created with
  scoped_session) is transactional?

 if the __str__ happens to self-change (well, shit happens), then u
 should not do commit nrt flush. the safest is to rollback -
 regardless what u do. or maybe close() or clear()?
 i think there was some way to make the session readonly alltogether --
 but i'm not sure.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Transactions and read-only operations

[az]

On Saturday 07 June 2008 22:50:36 Tomer wrote:
 Thanks. What would happen if I didn't do anything (I've seen lots
 of examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?
no idea, never used those, see docs.
as long as u know your objects, these things should be fine. But if 
it's not _your_ objects and/or u expect anything from those or u're 
paranoic about the db safety... open it with readonly access?

 On Jun 7, 11:58 am, [EMAIL PROTECTED] wrote:
  On Saturday 07 June 2008 21:35:35 Tomer wrote:
   If I am just issuing a read-only query, and I want to make sure
   that nothing will change in the middle, do I need a
   transaction? If so, what do I do with it when I'm done - commit
   or rollback?
  
   For example, I want to do this:
  
   for user in Session.query(User):
       print user.name
  
   Should I use a transaction like this:
  
   Session.begin()
   for user in Session.query(User):
       print user.name
   Session.commit()
   If not, how would I deal with this if my session (created with
   scoped_session) is transactional?
 
  if the __str__ happens to self-change (well, shit happens), then
  u should not do commit nrt flush. the safest is to rollback -
  regardless what u do. or maybe close() or clear()?
  i think there was some way to make the session readonly
  alltogether -- but i'm not sure.

 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Transactions and read-only operations

[Lukasz Szybalski]

On Sat, Jun 7, 2008 at 2:50 PM, Tomer [EMAIL PROTECTED] wrote:

 Thanks. What would happen if I didn't do anything (I've seen lots of
 examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?


Currently that is what I use to do to get readonly. Since I am the one
that manages sa code as long as I don't say,

User.somefiled= 123, I should have no worries about any changes being made.

User.query.filter.is just a select statement.

Unless proven/shown otherwise.

Lucas

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Transactions and read-only operations

[Michael Bayer]

On Jun 7, 2008, at 3:50 PM, Tomer wrote:


 Thanks. What would happen if I didn't do anything (I've seen lots of
 examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?

when you use the DBAPI (which every SQLA driver does), anytime you get  
a hold of a connection, there is a transaction in progress.  Depending  
on what database backend you're using, this might have different  
meaning; such as if you're using MySQL with ISAM tables, there isnt  
really much transactional.   But generally, selecting from tables  
implies that those rows might be pulled into the transaction such that  
changes made external to the trans aren't visible (depending on  
isolation behavior).  But this doesn't actually write anything to  
the database.  Only INSERT/UPDATE/DELETE has that effect.   When using  
the ORM, these operations are encapsulated entirely wihtin a flush(),  
so if your transaction does not flush(), then nothing is written.

Doing a rollback() will ensure completely that nothing is written,  
even if flushes have taken place.  With SQLA 0.4, rollback() has the  
caveat that the Session is not really usable after a rollback() until  
it is clear()'ed.  With 0.5 this restriction is lifted.



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: out of range / locate column / lost connection

[braydon fuller]

braydon fuller wrote:
 That's the part I don't get is that it shouldn't be empty... I may
 have fixed this by using 'try', and then returning 'None' if it fails.

 [EMAIL PROTECTED] wrote:
 first two mean the result of the query is empty. 

 On Friday 06 June 2008 01:49:23 braydon fuller wrote:
   
 I am having some database problems (attached below), any
 recommendations to either recover from or fix these from happening?
 I am not sure why it would work OK at first, and then at some point
 run bad... Any recommendations on a technique to debug this would
 be much appreciated.

 Full source code of the project can be browsed here:
 http://git.braydon.com/gitweb.cgi?p=sparrow;a=tree;h=refs/heads/mas
 ter;hb=refs/heads/master

 For an idea of the project (screencast... although slightly old):
 http://interfce.com/videos/sparrow.html

 The site this is coming from (live):
 http://mochilla.com/




 [05/Jun/2008:22:16:15] HTTP Traceback (most recent call last):
   File /var/lib/python-support/python2.5/cherrypy/_cprequest.py,
 line 550, in respond
 cherrypy.response.body = self.handler()
   File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py,
 line 24, in __call__
 return self.callable(*self.args, **self.kwargs)
   File /var/local/mochilla/sparrow/http.py, line 38, in default
 return render(args, kwargs, location_st)
   File /var/local/mochilla/sparrow/templates.py, line 363, in
 render return render_skeleton(location, location_st, kwargs)
   File /var/local/mochilla/sparrow/templates.py, line 266, in
 render_skeleton
 body, cmds_array = compile_templates(location, kwargs)
   File /var/local/mochilla/sparrow/templates.py, line 192, in
 compile_templates
 doc = get_uri(location)
   File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri
 selected =
 db.session.query(TemplateRelation).filter_by(location=uri)[0] File
 /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line
 844, in __getitem__
 return list(self[item:item+1])[0]
 IndexError: list index out of range




 127.0.0.1 - - [05/Jun/2008:22:15:44] GET / HTTP/1.0 500 3293 
  [05/Jun/2008:22:15:59] HTTP Traceback (most recent call last):
 File /var/lib/python-support/python2.5/cherrypy/_cprequest.py,
 line 550, in respond
 cherrypy.response.body = self.handler()
   File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py,
 line 24, in __call__
 return self.callable(*self.args, **self.kwargs)
   File /var/local/mochilla/sparrow/http.py, line 38, in default
 return render(args, kwargs, location_st)
   File /var/local/mochilla/sparrow/templates.py, line 363, in
 render return render_skeleton(location, location_st, kwargs)
   File /var/local/mochilla/sparrow/templates.py, line 266, in
 render_skeleton
 body, cmds_array = compile_templates(location, kwargs)
   File /var/local/mochilla/sparrow/templates.py, line 252, in
 compile_templates
 data = search_list(doc.oid, doc.objects, location=location,
 locations=locations, cmds_array=doc.cmds_array, kwargs=kwargs)
   File /var/local/mochilla/sparrow/templates.py, line 172, in
 search_list objects = object_children(object_oid)
   File /var/local/mochilla/sparrow/objects.py, line 39, in
 object_children parent = get_object(oid)
   File /var/local/mochilla/sparrow/objects.py, line 31, in
 get_object selected =
 db.session.query(Objects).filter_by(oid=oid).one() File
 /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line
 927, in one
 raise exceptions.InvalidRequestError('No rows returned for
 one()') InvalidRequestError: No rows returned for one()




 127.0.0.1 - - [05/Jun/2008:18:15:08] GET / HTTP/1.0 200 24400 
  [05/Jun/2008:18:15:09] HTTP Traceback (most recent call last):
 File /var/lib/python-support/python2.5/cherrypy/_cprequest.py,
 line 550, in respond
 cherrypy.response.body = self.handler()
   File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py,
 line 24, in __call__
 return self.callable(*self.args, **self.kwargs)
   File /var/lib/python-support/python2.5/cherrypy/_cptools.py,
 line 140, in handle_func
 handled = self.callable(*args, **self._merged_args(kwargs))
 TypeError: staticfile() got multiple values for keyword argument
 'filename'




 class 'sqlalchemy.exceptions.NoSuchColumnError'
 [05/Jun/2008:21:42:22] HTTP Traceback (most recent call last):
   File /var/lib/python-support/python2.5/cherrypy/_cprequest.py,
 line 550, in respond
 cherrypy.response.body = self.handler()
   File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py,
 line 24, in __call__
 return self.callable(*self.args, **self.kwargs)
   File /var/local/mochilla/sparrow/http.py, line 38, in default
 return render(args, kwargs, location_st)
   File /var/local/mochilla/sparrow/templates.py, line 346, in
 render get_uri(location_id)
   File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri
 selected =
 db.session.query(TemplateRelation).filter_by(location=uri)[0] File
 

[sqlalchemy] Re: Transactions and read-only operations

[Tomer]

Thanks for the detailed response.

I have two follow-up questions:

1) From what I understand, if I read an object (eg, Student) from
the database, modify that object or other objects, and then commit, I
have no guarantee that the object didn't change between the time I
read it and the time I committed. For example:

if len(Session.query(Student).filter_by(Student.name ==
'Bill').all())  0: # are there any students named 'Bill'?
school = Session.query(School).one() # assume there is one
school in the database
school.hasStudentNamedBill = True
Session.commit()

When the commit is issued, I might end up with an inconsistent
database, because a different transaction (in another thread, for
example) may have changed the name of the student after I checked if
there is a student named Bill, but before I committed. From the last
answer it seems like databases that support transactions might not
suffer from this problem (if they take locks on SELECTed rows), but I
tried in SQLite (which supports transactions) and it didn't help.
Would a different database help solve the problem?

2) You mentioned that the recommended approach to roll back in SA 0.4
is to call rollback() and then clear(). Can I just call close() on the
session instead? From the documentation it seems that close() is
essentially a rollback combined with a clear.

Thanks!

On Jun 7, 9:29 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 7, 2008, at 3:50 PM, Tomer wrote:



  Thanks. What would happen if I didn't do anything (I've seen lots of
  examples online that will just issue a query like
  Session.query(User).all() and that's all). Will that query start a
  transaction if it's a transactional session?

 when you use the DBAPI (which every SQLA driver does), anytime you get  
 a hold of a connection, there is a transaction in progress.  Depending  
 on what database backend you're using, this might have different  
 meaning; such as if you're using MySQL with ISAM tables, there isnt  
 really much transactional.   But generally, selecting from tables  
 implies that those rows might be pulled into the transaction such that  
 changes made external to the trans aren't visible (depending on  
 isolation behavior).  But this doesn't actually write anything to  
 the database.  Only INSERT/UPDATE/DELETE has that effect.   When using  
 the ORM, these operations are encapsulated entirely wihtin a flush(),  
 so if your transaction does not flush(), then nothing is written.

 Doing a rollback() will ensure completely that nothing is written,  
 even if flushes have taken place.  With SQLA 0.4, rollback() has the  
 caveat that the Session is not really usable after a rollback() until  
 it is clear()'ed.  With 0.5 this restriction is lifted.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---