Feature #27372

Epic #55070: Workpackages

Epic #55065: WP: Overall System Performance (Backend and Frontend)

Task #55179: Optimize SQL Performance

Execute native prepared queries in t3lib_DB

Added by Steffen Gebert about 4 years ago. Updated over 1 year ago.

Status:Resolved Start date:2011-06-11
Priority:Should have Due date:
Assigned To:Xavier Perseguers % Done:

100%
Category:Database API Spent time: -
Target version:6.2.0
PHP Version: Sprint Focus:
Complexity:hard

Description

Currently, t3lib_DB does not execute prepared queries, even when the prepared functions are used. All queries are transformed to "normal" MySQL queries. Only DBAL takes advantage of using them ATM.

This should be fixed for 4.6 I think.

prepared_statements_wip.patch Magnifier (9.5 kB) Benjamin Mack, 2014-01-14 17:10

Related issues

related to Core - Feature #23374: Add support for prepared queries Resolved 2010-08-14

Associated revisions

Revision 5487900c
Added by Xavier Perseguers over 1 year ago

[FEATURE] Execute native prepared queries

As TYPO3 is now using mysqli, prepared queries are natively supported
and should be used.

DBAL, however, does not yet actually use native prepared queries but
falls back to standard queries by replacing placeholders and executing
the underlying SQL query.

Change-Id: If50da6e6d27af89e01c0439bcb9d39a85615a75d
Resolves: #27372
Releases: 6.2
Reviewed-on: https://review.typo3.org/28231
Reviewed-by: Andreas Fernandez
Tested-by: Andreas Fernandez
Tested-by: Wouter Wolters
Reviewed-by: Markus Klein
Reviewed-by: Stefan Neufeind
Reviewed-by: Jigal van Hemert
Tested-by: Jigal van Hemert

History

#1 Updated by Benjamin Mack over 1 year ago

wip patch attached. Xavier will work on this in the next weeks.

#2 Updated by Ernesto Baschny over 1 year ago

  • Status changed from New to Accepted
  • Target version set to 6.2.0

+1 on that, thanks!

#3 Updated by Felix Oertel over 1 year ago

Do we have current status? ;)

#4 Updated by Ingo Schmitt over 1 year ago

  • Parent task set to #55179

#5 Updated by Markus Klein over 1 year ago

  • Target version deleted (6.2.0)
  • Complexity set to hard

I took a closer look here.

To sum it up: quite hard

  1. Our DB API supports named parameters (:placeholder), but MySQL does not.
  2. The patch from Benni does not take DBAL into account at all. So this is a no go.
  3. The mysqli_stmt::get_result() (buffered result) method is only available with mysqlnd driver according to php docs.

We'd need to:

  1. Get DBAL straight for 6.2 (#50752)
  2. Implement a translation layer from named to positioned parameters, which would involve some sort of query parsing again.
  3. Make the PreparedStatement class independent of any database link, as the exact link used is different for every driver used in DBAL.
  4. Emulate a buffered result, if mysqlnd is not available, to make the PreparedStatement::seek() function work

So IMHO this is a topic for 6.3+

#6 Updated by Felix Oertel over 1 year ago

  • Assigned To set to Xavier Perseguers
  • Target version set to 6.2.0
  • TYPO3 Version changed from 4.6 to 6.2

Xavier will work on this and tries to push a WIP patch this evening. In the morning I will have a look and possibly pick up ...

#7 Updated by Gerrit Code Review over 1 year ago

  • Status changed from Accepted to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#8 Updated by Gerrit Code Review over 1 year ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#9 Updated by Gerrit Code Review over 1 year ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#10 Updated by Gerrit Code Review over 1 year ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#11 Updated by Gerrit Code Review over 1 year ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#12 Updated by Gerrit Code Review over 1 year ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#13 Updated by Gerrit Code Review over 1 year ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#14 Updated by Gerrit Code Review over 1 year ago

Patch set 8 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#15 Updated by Gerrit Code Review over 1 year ago

Patch set 9 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28231

#16 Updated by Xavier Perseguers over 1 year ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

Also available in: Atom PDF