Stored Procedures

[John Cox]

One more thing while I am thinking of it. I believe that all the
queries should be examined for stored procedure replacement in MySQL.
I am not sure how my people actually used the database abstraction to
begin with, and perhaps we should pull out adodb and replace it with
PDO and replace the queries with stored procedures to ensure proper
performance.

I have no problem only supporting MySQL, and requiring PHP 5 and MySQL
5. Seems to make sense to me, but lets discuss.

[Jo]

On Jun 7, 9:46 am, John Cox <john.cox...@gmail.com> wrote:
> One more thing while I am thinking of it.  I believe that all the
> queries should be examined for stored procedure replacement in MySQL.

Ok, sounds like a good objective.

> I am not sure how my people actually used the database abstraction to
> begin with, and perhaps we should pull out adodb and replace it with
> PDO and replace the queries with stored procedures to ensure proper
> performance.

Would we need to pull out adodb vs perhaps running PDO + ADODdb if
we're looking at performance gains? We could still use stored
procedures and it would add some gain again, and we also retain
multiple db compatibility. What about ADOdb + PDO and the ADOdb
extension (C)? Just another idea. While direct php calls for mysql
would add further performance gains i'm not sure the amount of gain is
worth it vs loosing cross db compatibility. Depends on overall product
objectives, expected user base and so on I guess.

> I have no problem only supporting MySQL, and requiring PHP 5 and MySQL
> 5.  Seems to make sense to me, but lets discuss.

I'd go for php 5.2 minimum and while we're discussing MySQL I agree v5
min seems sensible.

[Jo]

A few more thoughts on:

> I am not sure how my people actually used the database abstraction to
> begin with, and perhaps we should pull out adodb and replace it with
> PDO and replace the queries with stored procedures to ensure proper
> performance.

How well as we able to deal code maintenance and debugging issues
with stored procedures and MySQL? There has been a lot written about
the headaches of using stored procedures esp in a db like MySQL. Do
the performance gains here outweigh the downsides ? Would some of the
alternatives like using PDO or ADOdb + PDO + ADOdb extension be a
better (albeit slightly less performance gain) option?

[John Cox]

[John Cox]

On Jun 6, 11:07 pm, Jo <jojo...@gmail.com> wrote:
> A few more thoughts on:
>
>

> How well as we able to deal  code maintenance and debugging issues
> with stored procedures and MySQL? There has been a lot written about
> the headaches of using stored procedures esp in a db like MySQL. Do
> the performance gains here  outweigh the downsides ? Would some of the
> alternatives like using PDO or ADOdb + PDO + ADOdb extension be a
> better (albeit slightly less performance gain) option?

Not sure why gmail sent an empty message. I only have time this
morning to answer this one, but will add to the other ones tonight.

From my tests, stored proc. and triggers decrease query time around 10
- 15%. With the modular nature and the number of queries running,
that can be a bit of a boost. The problem is how to test right now,
as we don't have a profiler running. Perhaps that should be the first
thing to get going after the purge of the code base?

I am not sure if PDO or mysqli is the best to use here, or even adodb,
which I am not against. I am even not that concerned with the
performance outside of getting some of the queries from the last 8
years cleaned up to behave a bit better. Just a discussion point;)

I do think it makes sense to use a PHP library over adodb to reduce
the overhead and code maintenance, but that would be a lot of work for
a summer project;) I would really like to have the 1.0 release around
september, but if we can get improvement, with the ability of creating
standalone aps quicker, I'd take the extra time to do it right to
begin with.

[John Cox]

On Jun 6, 10:53 pm, Jo <jojo...@gmail.com> wrote:
> On Jun 7, 9:46 am, John Cox <john.cox...@gmail.com> wrote:
>
> > One more thing while I am thinking of it.  I believe that all the
> > queries should be examined for stored procedure replacement in MySQL.
>
> Ok, sounds like a good objective.
>
> > I am not sure how my people actually used the database abstraction to
> > begin with, and perhaps we should pull out adodb and replace it with
> > PDO and replace the queries with stored procedures to ensure proper
> > performance.
>
> Would we need to pull out adodb vs perhaps running PDO + ADODdb if
> we're looking at performance gains? We could still use stored
> procedures and it would add some gain again, and we also retain
> multiple db compatibility. What about ADOdb + PDO and the ADOdb
> extension (C)? Just another idea. While direct php calls for mysql
> would add further performance gains i'm not sure the amount of gain is
> worth it vs loosing cross db compatibility. Depends on overall product
> objectives, expected user base and so on I guess.
>

Have you run into a case where you needed to support PG, SL, Oracle,
or MSSQL? My understanding was only PG and SL worked, and that is
using the word "worked" loosely;)

[Jo]

On Jun 7, 7:52 pm, John Cox <john.cox...@gmail.com> wrote:

> Have you run into a case where you needed to support PG, SL, Oracle,
> or MSSQL?  My understanding was only PG and SL worked, and that is
> using the word "worked" loosely;)

I guess my response was thinking about what we would like or need for
the product rather than what works now in xaraya. Specifically in
Xaraya i've had a need for Postgres, sqlite and mysql only. Those all
work now but some modules/areas do not support postgres or sqlite that
well or some not at all.

I could get by with just mysql support, just not sure what you think
we might want/need for the final sailfish product.

[Jo]

On Jun 7, 7:49 pm, John Cox <john.cox...@gmail.com> wrote:

> From my tests, stored proc. and triggers decrease query time around 10
> - 15%.  With the modular nature and the number of queries running,
> that can be a bit of a boost.  The problem is how to test right now,
> as we don't have a profiler running.  Perhaps that should be the first
> thing to get going after the purge of the code base?
>

If we are going to use stored procedures then having the profiler
would be a big plus.
One of the things that concerns me most about stored procedures in
such an app is lack of debugging and the error messages. Maybe we
could have php and stored procedures, or introduce as a second step
after cleaning out ADOdb and getting the profiler going.

> I am not sure if PDO or mysqli is the best to use here, or even adodb,
> which I am not against.  I am even not that concerned with the
> performance outside of getting some of the queries from the last 8
> years cleaned up to behave a bit better.  Just a discussion point;)

Yes, just discussion. I'm certainly not sure, and I think it does come
down to what you want the product to be, who uses it and for what.

> I do think it makes sense to use a PHP library over adodb to reduce
> the overhead and code maintenance, but that would be a lot of work for
> a summer project;)

Agree re the overhead and perhaps we don't even take advantage of
the richer querying in ADOdb right now. If we did go php/PDO over
adodb (which is probably my current preference), then perhaps php 5.2
and mysql 5.1 would be a good minimum to go with.

>  I would really like to have the 1.0 release around
> september,

Oh - we better get the whip out ;)

[John Cox]

Right, and I am unsure to be honest.  If I am writing a dedicated application, I chose the DB, Language and tools.  I don't expect a ton of modules to written and shared.

For instance, if I was to write a standalone forum application, it's not a module, but an application.  Do I need to support all the DBs that might possibly be used?  If I write a business ap not for distribution, then it's pretty easy to answer.

In the end, I am not sure.  That said, if we are are going to use abstraction, then if we can't just write a wrapper for PDO, then we might as well stick to ADODB.  Seems like a lot of work on hindsight for not much gain.

[John Cox]

On Mon, Jun 7, 2010 at 1:39 PM, Jo <joj...@gmail.com> wrote:

On Jun 7, 7:49 pm, John Cox <john.cox...@gmail.com> wrote:

> From my tests, stored proc. and triggers decrease query time around 10
> - 15%.  With the modular nature and the number of queries running,
> that can be a bit of a boost.  The problem is how to test right now,
> as we don't have a profiler running.  Perhaps that should be the first
> thing to get going after the purge of the code base?
>

If we are going to use stored procedures then having the profiler
would be a big plus.
One of the things that concerns me most about stored procedures in
such an app is lack of debugging and the error messages. Maybe we
could have php and stored procedures, or introduce as a second step
after cleaning out  ADOdb and getting the profiler going.

Sounds like a good plan.  Profiler first, then make a decision, correct?

 

> I am not sure if PDO or mysqli is the best to use here, or even adodb,
> which I am not against.  I am even not that concerned with the
> performance outside of getting some of the queries from the last 8
> years cleaned up to behave a bit better.  Just a discussion point;)

Yes, just discussion. I'm certainly not sure, and I think it does come
down to what you want the product to be, who uses it and for what.

I am really thinking keeping the focus on stand alone business / logic application, and not necessarily on how we can extend functionality left and right.  Honestly, I would just be writing business applications, but I can also see someone using it for a jump point for something like a forum or cart, etc, which would make sense to have a modular program without the messy additions.

 

> I do think it makes sense to use a PHP library over adodb to reduce
> the overhead and code maintenance, but that would be a lot of work for
> a summer project;)

Agree re the overhead and  perhaps we  don't even take advantage of
the richer querying in ADOdb right now. If we did go php/PDO over
adodb (which is probably my current preference), then perhaps php 5.2
and  mysql 5.1 would be a good minimum to go with.

>  I would really like to have the 1.0 release around
> september,

Oh - we better get the whip out ;)

heh, I've never made a deadline in my life;)