Login | Register
My pages Projects Community openCollabNet

Discussions > dev > Re: [propel-dev] Query counting

propel
Discussion topic

Back to topic list

Re: [propel-dev] Query counting

Reply

Author Oliver Schonrock <oliver at realtsp dot com>
Full name Oliver Schonrock <oliver at realtsp dot com>
Date 2007-07-13 09:32:08 PDT
Message Cameron Brunner wrote:
> A - We need to store the entire query in memory if we are to do
> duplicate query detection.

true, however (connected with what you say in D) the
PDOStatement->queryString property only gives us the prepared statement
sql not the final, "all params substituted" sql. This is a limitation in
PDO and probably for good reason since the param substitution is
supposed to happen in the DB server for some backends (not mysql due to
query cache limitations).

This means that we do not, and will not for some time, have access to
the final sql string. Does "duplicate detection" make sense in this
case? Is it a valid feature if we are only looking at prepared
statements? There will be many "duplicated" queiries for
retrieveByPk(1|2|3) for example....

> B - If we dont, we incur quite an overhead to do func_get_args and
> call_user_func etc so if we dont we pay a penalty even in production
> hence the way i did that

hmm..good point. i did some simple tests over 1000 calls:

plain call a(1,2,3,4): 1.5ms
plain call $a->a(1,2,3,4): 1.6ms
call_user_func_array('a', array(1,2,3,4)): 3.6ms
call_user_func_array(array($a, 'a'), array(1,2,3,4)): 4.6ms

big percentage increase, still low given that every db query takes ~1ms
no matter how trivial. But worth considering when we think about how to
implement these calls. I will revisit this and rewrite as a native call.

> C - true, specially if you have unbuffered queries enabled, its just a
> bit of a guide on what returns a lot of rows. We could also add a
> memory usage check onto the dtor, that would be about when all rows
> are hydrated... we could also add the true flag to the memory usage
> functions, i dont know the difference tho

i am not sure the memory_usage thing is very useful due to these
limitations. Should perhaps be dealt with at a profiling level further
out than PDO or even outside Propel.

> D - We can still add these into PropelPDO and since DebugPropelPDO
> extends it that will run automatically still... nice and seamless and
> easy... i have had propel's name cursed at work since we couldnt see
> the final sql that was being ran easily (having to replace the ?'s by
> hand)

I am trying to clarify what features we can realistically hope to
achieve and where they should sit. In my opinion, while duplicate query
detection and memory consumption are desirable, they are not really
possible given where we are measuring. That then leaves query timing and
counting.

As far as I can see these can be easily added to the existing
PropelPDO.php while adding a PropelPDOStatement.php. Making sure that we
use efficient call mechanisms for the extensions.

Counting could be driven by a $debug switch (although I would prefer a
setter rather than a public property).

Timing could be switched on if logging is enabled and $debug=true. The
time written to the log with the sql string since these belong together
and there doesn't seem to be a case for keeping the sql in memory.

Moving logging to exec time rather than prepare poses several problems
with backward compat and persistent connections. I will prepare a new
patch which considers these options and suggests a solution, bearing
performance in mind.

> Always plenty of options, i have been wanting a way to automatically
> profile an application in development and when a 'slow' or 'expensive'
> (memory wise) query is ran then to display it. I was considering a
> Smarty debug style popup to be able to be enabled...

we have profiling information automatically at bottom of every
non-production page, just in the html. it works well.

hope we are getting closer.

Oliver
Attachments

« Previous message in topic | 18 of 36 | Next message in topic »

Messages

Show all messages in topic

translation cysio Krzysio 2007-07-10 09:28:18 PDT
     Re: [propel-dev] translation hlellelid Hans Lellelid 2007-07-10 09:52:07 PDT
         Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 05:38:54 PDT
             Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 05:43:28 PDT
                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 06:40:03 PDT
                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 11:25:28 PDT
                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 14:27:16 PDT
                         Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 14:29:11 PDT
                             Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 14:32:11 PDT
                                 Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 14:34:25 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 15:02:17 PDT
                                         Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 15:04:01 PDT
                         Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-13 01:47:24 PDT
                             Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-13 04:03:01 PDT
                                 Re: [propel-dev] Query counting Pieter Nobels <pieter at opengate dot be> Pieter Nobels <pieter at opengate dot be> 2007-07-13 04:10:27 PDT
                                 Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-13 06:55:53 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 01:26:36 PDT
                                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-13 09:32:08 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 01:40:29 PDT
                                         Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 04:22:35 PDT
                                             Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 04:36:04 PDT
                                                 Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-16 15:48:32 PDT
                                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-17 18:53:07 PDT
                                                     Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-19 04:40:28 PDT
                                                         Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-19 06:42:12 PDT
Page: of 2 « Previous | Next »
Messages per page: