#30194 [Opn->Fbk]: Mysqli Prepared Statements work very slow when CLOB\BLOB is in result set

ID: 30194 Updated by: georg@php.net Reported By: phoeniks2k at mail dot ru -Status: Open +Status: Feedback Bug Type: MySQLi related Operating System: All PHP Version: 5.0.1 New Comment: Thank you for this bug report. To properly diagnose the problem, we need a short but complete example script to be able to reproduce this bug ourselves. A proper reproducing script starts with <?php and ends with ?>, is max. 10-20 lines long and does not require any external resources such as databases, etc. If possible, make the script source available online and provide an URL to it here. Try avoid embedding huge scripts into the report. Please read the comments more carefully. As I mentioned before, add a short reproducible testcase. Previous Comments: ------------------------------------------------------------------------ [2004-09-29 11:50:23] phoeniks2k at mail dot ru In another words reading clobs from statement results looks much slower then reading those from simple select results ------------------------------------------------------------------------ [2004-09-29 11:44:16] phoeniks2k at mail dot ru No, i ment another thing. Every query\stmt is executed multiple times (stmts are prepared only once) This statement SELECT id, title, proto, lnk4, mdf FROM objects_hier oh WHERE owner = ? works FASTER then query SELECT id, title, proto, lnk4, mdf FROM objects_hier WHERE owner = $owner_id But this statement (i marked clob field) SELECT id, title, proto, lnk4, mdf, xml_data <<<(clob) FROM objects_hier oh WHERE owner = ? works MUCH SLOWER then query SELECT id, title, proto, lnk4, mdf, xml_data <<<(clob) FROM objects_hier WHERE owner = $owner_id Every time ALL DATA is fetched from recordsets to script variables, everything is made as it was described in DOC and is identical by result except exec time ------------------------------------------------------------------------ [2004-09-29 11:16:07] georg@php.net Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php Retrieving only a single recordset with a prepared statement is of course slower. Why do you use mysqli_stmt_store_result? The first recordset is already sent to client after mysqli_stmt_execute. Also there is no reproducible testcase in your bug report. ------------------------------------------------------------------------ [2004-09-22 14:38:07] phoeniks2k at mail dot ru Sorry, some additional info: Mysql 4.1.4 Gamma and clobs are not empty ------------------------------------------------------------------------ [2004-09-22 14:27:20] phoeniks2k at mail dot ru Description: ------------ If result set has clob\blob fields, retrieving data is VERY slow. Query "SELECT SQL_NO_CACHE id, xml_clob FROM some_table WHERE id = 1" executes 1000 times for about 0.4 seconds on my server; But this query rewriten with statement execs 20 time for 6(!) seconds!!! Every time i call: exec, store result, fetch, free_result P.S. SQL_NO_CACHE is used for clean noncached result times Expected result: ---------------- STMT performance higher then normal query performance ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=30194&edit=1