PDO speed

[Acs]

Has anyone benchmarked pdo query to pdo prepared statements? I thought if I used prepared statements it would be faster (don't know why but I think I read it somewhere) than using the query object with one big query. I tested a script I have with ab and I must say that pdo statements were slower and consumed more ram than the query method. I got the speed value and memory using xdebug. Anyone made similar tests?

 

I have attached the result files.

The values are

Date Time;Speed in milliseconds; memory consumption

 

[attachment deleted by admin]

[Acs]

bump

[Acs]

bump again - Maybe this should be moved to another section!!

[discomatt]

It's really situation dependent. May I see the code you used to benchmark?

[Acs]

It's part of my implementation of the activerecord pattern so it might not make sense.

 

  $query = "INSERT INTO " . $this->_tablename . "(" . $this->_fieldsNames . ") VALUES(";
             foreach ($this->_insert_data as $k => $data) {   //This->_insert_data is an array containing (fieldname => "value"
             	$bigquery .= $query;
             	
             	$values = array();
             	foreach ($data as $value) 
             		$values[] = (is_string($value)) ? "'$value'" : $value;					
             		
             	$bigquery .= implode(",",$values);
                    
                $bigquery .= ");";    	
            
				             	
		 }    			
		 $this->_insert_data = null;
		 try {					 	
		 	if ($this->conn->exec($bigquery))
		 		return true;
		 	else {
		 		print_r($this->conn->errorInfo());
			}
		 }
             catch (PDOException $e) {
             	throw new acs_exception($e);
                exit();   
             }

 

This uses the prepared statements:

try {
        		$prepared_query = "INSERT INTO " . $this->_tablename . "(" . $this->_fieldsNames . ") VALUES(";        		
                	        		
            $prepared_query_values = str_replace(",",",:",":" . $this->_fieldsNames);
            $prepared_query .= $prepared_query_values . ")";
        		
        		//echo $prepared_query , "<br>";    
                
                $pdo_preObj = $this->conn->prepare($prepared_query);                 
                                
                foreach ($this->_insert_data as $k => $data) {                	
                    foreach ($data as $fieldtoaddName => $fieldtoaddValue) {
                    	//echo "var name - " , $fieldtoaddName , " -- var value - " , $fieldtoaddValue , "<br>";
                        $$fieldtoaddName = $fieldtoaddValue;
                        //if (!$pdo_preObj->bindValue(':' . $fieldtoaddName,$fieldtoaddValue))
                        if (!$pdo_preObj->bindParam(':' . $fieldtoaddName,$fieldtoaddValue))
                        	throw new acs_exception("No bind");                        	
				}
                    
                    //echo "<pre>";                 
                    if ($pdo_preObj->execute())         
                    	echo "yuupi";
				else 
					print_r($pdo_preObj->errorInfo());
                        
                   
                    
                }                             
                
            }
            catch (PDOException $e) {
                throw new acs_exception($e);
                exit();   
            }

 

Here is the data in the array:

 

Array
(
    [0] => Array
        (
            [texto] => uuuuuu
            [contador] => 111
        )

    [1] => Array
        (
            [texto] => uuuuuu2
            [contador] => 2
        )

    [2] => Array
        (
            [texto] => uuuuuu3
            [contador] => 3
        )

    [3] => Array
        (
            [texto] => uuuuuu4
            [contador] => 4
        )

    [4] => Array
        (
            [texto] => uuuuuu5
            [contador] => 5
        )

    [5] => Array
        (
            [texto] => uuuuuu6
            [contador] => 6
        )

    [6] => Array
        (
            [texto] => uuuuuu7
            [contador] => 7
        )

    [7] => Array
        (
            [texto] => uuuuuu8
            [contador] => 8
        )

)

[discomatt]

Did you loop the query, or are your numbers based on a single execution?

[Acs]

For the pdo statements I have to loop every item in the array and because it's an array of array's I have to loop that too. Ok maybe the problem is there, so many loops, but there is no other way to do this.

 

After re-reading your question: I used ab to load the page 100 times and concurrency of 50

 

[discomatt]

In this situation, prepared statements may not be the best solution... as I said before, anyone who says 'prepared is better than queries' is wrong. It really depends on the situation Your best bet is to run these benchmarks on the production system, as there are many variables that determine your best overall solution.

[Acs]

Well.. it says it the manual:

"Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application"

 

But I guess it really depends on the situation, because I just create all the insert statements in the same var and do a query with that

[discomatt]

Mhm... performing multiple commands in a single query is much different than performing multiple queries.

 

Keep in mind PDO prepared does a lot of things in the background that your script doesn't. Yours is a great situation-specific optimization, and in this case, will run better than PDO prepared statements.

[Acs]

I am going to try to eliminate some of the loops and I will then check the speeds. This might be just about the loops I have in.

[Acs]

Indeed it was my code! I removed a loop and the pdo statements were a lot faster, just take a look.

 

These are 10 executions of my framework using pdo statements to insert data on a mysql table:

Date Time;Milliseconds;Memory (in bytes)

2008/10/14 21:50:50;0.0235240459442;143960

2008/10/14 21:50:50;0.0313630104065;143960

2008/10/14 21:50:50;0.0313642024994;143960

2008/10/14 21:50:50;0.0235631465912;143960

2008/10/14 21:50:50;0.0219089984894;143960

2008/10/14 21:50:50;0.0220711231232;143960

2008/10/14 21:50:50;0.0247020721436;143960

2008/10/14 21:50:50;0.022469997406;143960

2008/10/14 21:50:50;0.0220470428467;143960

2008/10/14 21:50:50;0.0223350524902;143960

 

 

These are 10 executions of my framework using pdo with the query method and one big query:

Date Time;Milliseconds;Memory (in bytes)

2008/10/14 22:01:20;0.0519180297852;143464

2008/10/14 22:01:21;0.0859501361847;143464

2008/10/14 22:01:21;0.060604095459;143464

2008/10/14 22:01:21;0.0527191162109;143464

2008/10/14 22:01:21;0.0556530952454;143464

2008/10/14 22:01:21;0.0505831241608;143464

2008/10/14 22:01:21;0.128275156021;143464

2008/10/14 22:01:21;0.0642030239105;143464

2008/10/14 22:01:21;0.0600869655609;143464

2008/10/14 22:01:21;0.0702891349792;143464

 

I used again ab with -100 (I just used -n because -c seems to produce a strange behavior) but I just show the 10 first of each and you can see quite clearly the pdo statements, after I refined the code, is the fastest

 

Hope this can help someone decide with either going with pdo statements or queries.

The only downside now is it, is using a bit more memory than the query method, but I will try to improve on that.

 

Note: I am not using bindParam anymore. I just passed the values to be replaced in the execute method.

 

Going to try the beginTransaction method to see how it handles 100 executions