|
|
Posted by Sandman on 11/24/06 15:39
In article <4566ebf8$0$336$e4fe514c@news.xs4all.nl>,
Erwin Moller
<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
wrote:
> Sandman wrote:
>
> > In article <4566c535$0$335$e4fe514c@news.xs4all.nl>,
> > Erwin Moller
> > <since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
> > wrote:
> >
> >> Hi Sandman,
> >>
> >> One last idea: stored procedures.
> >> I don't know if mySQL has support for them, but they can speed things up
> >> hugely. (I am a Postgres-guy)
> >>
> >> You can move the logic from PHP to the stored procedure.
> >> The SP will fetch the needed info from the tables based on what it found
> >> in earlier queries (in the same SP).
> >>
> >> I don't like SP because they hide the logic away from the language to the
> >> database, are hard to debug, are hard to update, but they ARE fast.
> >>
> >> The advantage is also that you do not have to place/parse/check the
> >> recordset from MySQL memoryspace to PHP memoryspace.
> >>
> >> The SP will simply deliver what PHP needs to know.
> >
> > Ok, you lost me. How does it do that? How can a SP store information
> > about arbitrary member id and give it to PHP at will?
> >
> >> I once made a SP excactly for this reason, it delivered ready-to-go HTML
> >> and removed all the logic from the language (Java in this case) to
> >> Postgres, but then 3 years later I had to update it.... Really hard.
> >> I avoid SP as hell since then. Maybe people with more experience than I
> >> have with SP can do better jobs.
> >>
> >> But I am quite sure SP CAN solve your speedproblem.
> >
> > Ok, I'd be happy to hear just how it works before nosediving into some
> > docs about it, to see if we understand each other correctly.
> >
> >
>
> Hey Sandman,
>
> A Stored procedure is a piece of code that is run by the database itself (in
> the same memoryspace as the database too).
> You can use decisions (if/then/else), loop over resultsets from former
> queries, etc.
> So it is a programming language on its own INSIDE the database.
> And fast as hell since no overhead is needed (eg pumping resultsets through
> a pipe from the database process to php process).
>
> I am not sure if that will help in your current situation, but this is the
> idea:
> - You write a stored procedure that will actually fetch all needed tuples
> based on the same information your PHP receives (eg, userid, topicid, etc).
> - This SP will do the same things your script would do (except of course
> produce HTML) and you remember the needed info inside the SP.
> - When the SP is finished it returns the resultset, which you use to load
> above your script the same way as you do now, but now you only receive the
> needed info.
>
> For this setup to work you must of course be able to know what your script
> does, so you can program that into the SP.
> It will be double work (once in the SP, and once again in your scripts) but
> it will remove the massive databaseresults import you have now.
>
> I cannot judge if this will help you.
> But it is a possibility to put some time into I think, maybe when studying
> SP you see a few other shortcuts to reduce your resultset.
>
> If you need advise on SP with MySQL, I cannot help.(never looked into it)
>
> Good luck.
>
> Regards,
> Erwin Moller
Ok, thanks for the info. I will think about it. :)
--
Sandman[.net]
Navigation:
[Reply to this message]
|