php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #30194 Mysqli Prepared Statements work very slow when CLOB\BLOB is in result set
Submitted: 2004-09-22 14:27 UTC Modified: 2005-02-06 16:20 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: phoeniks2k at mail dot ru Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.0.1 OS: All
Private report: No CVE-ID: None
 [2004-09-22 14:27 UTC] 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


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-09-22 14:38 UTC] phoeniks2k at mail dot ru
Sorry, some additional info:

Mysql 4.1.4 Gamma and clobs are not empty
 [2004-09-29 11:16 UTC] 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-29 11:44 UTC] 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:50 UTC] 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 12:10 UTC] georg@php.net
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. 
 [2004-09-29 12:35 UTC] phoeniks2k at mail dot ru
<?php
function getmicrotime() {
  list($usec, $sec) = explode(" ", microtime());
  return ((double)$usec + (double)$sec);
}

$host = 'localhost';
$login = 'parallax';
$password = 'parallax';
$db_name = 'parallax_kernel';

$link = mysqli_init();
mysqli_real_connect($link, $host, $login, $password, $db_name);

$st   = mysqli_prepare($link, "SELECT id, title, proto, lnk4, mdf, xml FROM objects_hier oh WHERE owner = ?");
$info = array();
$owner = 0;
mysqli_stmt_bind_param($st, 'i', $owner);

/*---------------------------------------------------------*/
$t = getmicrotime();

for ($owner = 0; $owner < 20; $owner++) {
  mysqli_stmt_execute($st);
  mysqli_stmt_bind_result($st, $info['id'], $info['title'], $info['proto'], $info['lnk4'], $info['mdf'], $info['xml']);

  while (mysqli_fetch($st)) {
    // some data manipulations that are equiv. those in query
  }
  mysqli_stmt_free_result($st);
}
echo (getmicrotime() - $t).'<br>';

/*--------------------------------------------------------*/

$t = getmicrotime();
for ($owner = 0; $owner < 20; $owner++) {
  $recordset = mysqli_query($link, "SELECT id, title, proto, lnk4, mdf, xml FROM objects_hier oh WHERE owner = $owner");

  while ($row = mysqli_fetch_assoc($recordset)) {
    // some data manipulations that are equiv. those in stmt
  }
  mysqli_free_result($recordset);
}
echo (getmicrotime() - $t).'<br>';
?>
 [2005-02-06 16:20 UTC] georg@php.net
Can't reproduce 
 
OS: Linux 2.6.8 kernel 
MySQL (Client + Server): MySQL 4.1.10-debug running on the 
same machine.  
PHP: PHP 5.0.4-dev (cgi) (built: Feb  6 2005 15:01:27) 
 
1.000.000 rows fetched (10.000 iterations, 100 rows in 
table, blob length=60k) 
 
Execution times: 
Prepared statements: 218.26 seconds 
Queries (non cached): 349.83 seconds 
 
-> not a bug 
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 13:01:31 2024 UTC