MySQL / ADODB

[Ian.H]

Hi all,

I've taken on a massive project for a client recently and now I've hit
some bug fixes that are required (3rd party code). and was just wondering
about peoples opinions / personal findings.

This set of scripts for the section of the project use an ADODB class for
MySQL. I've never used this method before (I wrote a class a while back to
handle my MySQL core code which was mainly a wrapper for various mysql_*()
functions etc). The original query with the ADODB used code was hanging on
retrieving 19k rows. I managed to locate the bug here by introducing a
LIMIT of 1000 which then worked (although obviously a lot of data was
missing, it didn't hang and the results that were expected were
displayed). Removing the LIMIT again produces the hang situation again.

I stripped out that particular piece of ADODB class code and replaced it
with straight-forward mysql_*() calls wich retrieves all the data (all 19k
rows) with no issues whatsoever.

My query, is ADODB known to be slower / more resource intensive than
straight mysql_*() calls via a mysql_connect() connection? Version info
for this is:


* @version V1.20 25 June 2001 (c) 2000, 2001 John Lim
[email address removed]


Unfortunately, due to the bad coding style of this part of the project
(was "off the shelf" purchased code by the client) and the abominal use of
'global', I have a fair amount of work ahead of me I think as now I'm
pulling errors regarding functions of unknown members in other sections
of this code.

Anyways, just looking for opinions of people that may have experienced
this kind of behaviour personally before.



Regards,

Ian

--
Ian.H
digiServ Network
London, UK
http://digiserv.net/

[Andy Hassall]

Re: MySQL / ADODB

On Tue, 17 Aug 2004 20:42:09 GMT, "Ian.H" <ian@WINDOZEdig iserv.net> wrote:
[color=blue]
>My query, is ADODB known to be slower / more resource intensive than
>straight mysql_*() calls via a mysql_connect() connection?[/color]

I remember seeing a post with benchmarks, I think this is it:

http://groups.google.com/groups?selm=20011024180158.51630.qmail%40pb1.pair.com&output=gplain

ADODB by definition has to be at least a little slower than raw mysql calls,
doesn't it, since it's a wrapper around those calls so is simply running more
code in order to get you a more encapsulated/consistent interface?

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Ian.H]

Re: MySQL / ADODB

On Tue, 17 Aug 2004 22:01:42 +0100, Andy Hassall wrote:
[color=blue]
> On Tue, 17 Aug 2004 20:42:09 GMT, "Ian.H" <ian@WINDOZEdig iserv.net>
> wrote:
>[color=green]
>>My query, is ADODB known to be slower / more resource intensive than
>>straight mysql_*() calls via a mysql_connect() connection?[/color]
>
> I remember seeing a post with benchmarks, I think this is it:
>
> http://groups.google.com/groups?selm...&output=gplain[/color]


Thanks Andy, looks interesting =)

[color=blue]
> ADODB by definition has to be at least a little slower than raw mysql
> calls,
> doesn't it, since it's a wrapper around those calls so is simply running
> more code in order to get you a more encapsulated/consistent interface?[/color]


True; although was more thinking along the lines of why it was _so_ slow
that it caused hangs (had to hit 'stop' quick before it screwed over the
SQL process) whereas the wrapper I coded some time ago will happily fetch[color=blue]
>20k rows without question.[/color]

This part of the project is to be replaced soon but need to patch it for
the time being while other areas are sorted beforehand. Looks like fun and
games ahead fixing this!

Thanks again =)



Regards,

Ian
--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[Andy Hassall]

Re: MySQL / ADODB

On Tue, 17 Aug 2004 21:10:59 GMT, "Ian.H" <ian@WINDOZEdig iserv.net> wrote:
[color=blue]
>On Tue, 17 Aug 2004 22:01:42 +0100, Andy Hassall wrote:[color=green]
>> On Tue, 17 Aug 2004 20:42:09 GMT, "Ian.H" <ian@WINDOZEdig iserv.net> wrote:
>>[color=darkred]
>>>My query, is ADODB known to be slower / more resource intensive than
>>>straight mysql_*() calls via a mysql_connect() connection?[/color]
>>
>> I remember seeing a post with benchmarks, I think this is it:
>>
>> http://groups.google.com/groups?selm...&output=gplain[/color]
>
>Thanks Andy, looks interesting =)
>[color=green]
>> ADODB by definition has to be at least a little slower than raw mysql
>> calls, doesn't it, since it's a wrapper around those calls so is simply running
>> more code in order to get you a more encapsulated/consistent interface?[/color]
>
>True; although was more thinking along the lines of why it was _so_ slow
>that it caused hangs (had to hit 'stop' quick before it screwed over the
>SQL process) whereas the wrapper I coded some time ago will happily fetch[color=green]
>>20k rows without question.[/color][/color]

At least according to that benchmark ADODB fares quite well as far as
performance goes.

Been meaning to have a look at the PHP database abstraction layers as I'm soon
going to be starting to civilise an intranet at work, moving away from ASP+ADO
to PHP+OCI8, and a little bit of syntactic sugar around the OCI interface might
be nice. What's your opinion on ADODB vs. PEAR DB?

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Ian.H]

Re: MySQL / ADODB

On Tue, 17 Aug 2004 22:58:51 +0100, Andy Hassall wrote:
[color=blue][color=green]
>>True; although was more thinking along the lines of why it was _so_ slow
>>that it caused hangs (had to hit 'stop' quick before it screwed over the
>>SQL process) whereas the wrapper I coded some time ago will happily fetch[color=darkred]
>>>20k rows without question.[/color][/color]
>
> At least according to that benchmark ADODB fares quite well as far as
> performance goes.[/color]


I ened up stripping some of the ADODB code in favour of plain mysql_*()
calls which seems to have solved the issue for the time being. It's not
pretty, but it's not falling over anymore. Should survive until the recode
anyway.

[color=blue]
> Been meaning to have a look at the PHP database abstraction layers as I'm soon
> going to be starting to civilise an intranet at work, moving away from ASP+ADO
> to PHP+OCI8, and a little bit of syntactic sugar around the OCI interface might
> be nice. What's your opinion on ADODB vs. PEAR DB?[/color]


Couldn't really comment Andy tbh as I've not had much dealing with the
PEAR DB.. not in my own code anyway and not sure I've had much dealing
with it elsewhere either.. a lot of the code I deal with is custom coded.

There's not much I've made use of from PEAR in general.. apart from the
XML_Tree module which I tried recently as part of an experiment for
something (it was dog slow on a file > 5000 lines of _really_ simple XML)
I don't think I touched much else from the repository (wish it was more
like cpan).

Saying that, I have a feeling that the Horde framework and IMP use the
PEAR DB. If this is the case, I've never had an issue with Horde / IMP in
the 18 months I've been running it (used for about 100 accounts) over both
HTTP and HTTPS.



Regards,

Ian

--
Ian.H
digiServ Network
London, UK

http://digiserv.net/