|
|
Posted by Erwin Moller on 11/24/06 12:56
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
Navigation:
[Reply to this message]
|