[TYPO3-dev] Questions about exec_PREPAREquery

[Stefan Frömken]

Hello Mathias,

ich have changed my extension-class into three additional methods. So 
now it's easier to change the different modes. You can realize that 
MySQL-way is always the fastest. So...we all can hope of the cool 
integration of MySQL-Prepare-Statements into TYPO3-Core in future.

If you change TYPO3-way to top it is slower than the old-school-way.
If you change old-school-way to top it is slower than the TYPO3-way.

So you're right, that there must be some additional caches regarding the 
query management.

Stefan

Am 07.06.2011 09:13, schrieb Mathias Schreiber [wmdb]:
> Am 06.06.11 20:55, schrieb Stefan Frömken:
>> I'm wondering that TYPO3-Statements are faster than the old way
>> allthough TYPO3-Statements are using the same functions like the old
>> way. And it has to load an additional class.
>>
>> I don't understand this?!?! Why is it faster?
>
> Let me take you into the insides of a database.
> You say "give me row 14 of table pages".
> MySQL will then parse that query and check it for errors.
> After that it will be recoded into binary data (if using a remote host)
> and sent to the DB server.
>
> Now imagine a typical TYPO3 scenario with hundreds of queries that look
> almost the same.
> MySQL will parse the query once (!), check it for syntax errors once (!)
> and recode it to binary once (!).
>
> Additionally there was some talk about query-caching prep-statements as
> well, but I never really checked if it became part of the mySQL code.
>
> On the other hand, there is one downside (like always in life :)):
> Say you have a remote DB host that is either very far away from your
> webserver or under heavy load or whatever slows the machine down.
> Using prep-statements always include 2 messages to the server rather
> than one.
> If you have long trip times (> 40ms) to your mysql host, prep-statements
> might work out worse than normal statements.
>
> THis is why I did not opt for "prep-statements all throughout the TYPO3
> core" because under certain consitions it makes things worse :/
> Unfourtunately, in 99% of all use-cases, they are better in every respect.
>
> Hope this clears things up a bit.
> cheers
> Mathias
>