6

i am working on PHP on OOP's concept, this is the class called 'connect' which connects to database and also inserts the request in database_

class connect(){
        public function insert($column, $value)
            {
            $insert = mysqli_query($this->con, "INSERT INTO $tablename ($column)
    VALUES ('$value')");
            }/* end of insert method */
}/* end of class 'connect' */

all i just want to know that if i insert each time by calling 'insert' method for each request, will it be a waste of time or wasting more server time?, or should i just make only one method for inserting all the request at a time?;

$call = new connect("localhost", "root", "", "facebook","fb1");
$call->insert("username", "testInsert141");
$call->insert("username", "testInsert141");
$call->insert("username2", "testInsert142");
$call->insert("username3", "testInsert143");
GAURAV MAHALE
  • 1,032
  • 10
  • 18
  • 1
    you can insert multiple row at a time. So you have to get each set of values at a time and run your insert query just once. Ref : [link](http://stackoverflow.com/questions/7977172/mysql-multiple-insert-performance) – Mithun Sen Jun 07 '13 at 12:32
  • 2
    I'm confused by your term `request`. Are they AJAX requests in the same page? Are they HTTP get requests on new served pages? Regarding to the SQL execution, it doesn't make much difference if you're using one insert execution or many (except if are hundred of thousands, wich I believe, is not the case). What matter most is the connection, that takes some time to be created and destroyed. You could use a connection pool. That would save you server time, but again, only if the number of transactions is relevant. – aldux Jun 07 '13 at 12:36
  • i know i can insert multiple, but if i use above code; would it slow or would take more time to process????? – GAURAV MAHALE Jun 07 '13 at 12:37
  • 1
    create separate function to connect with your database. And if you call those insert function one after another then it will take some time than a single insert query. – Mithun Sen Jun 07 '13 at 12:46
  • 1
    how about sql transactions? – tomexsans Jun 07 '13 at 12:50
  • -->tomexsans sql transaction? you mean this above code is good for transaction? – GAURAV MAHALE Jun 07 '13 at 14:35
  • 1
    If you are OK that some queries fail while others succeed, then you don't need a transaction. Otherwise, if you want all of them to succeed or none if something goes wrong - then you use a transaction. – N.B. Jun 07 '13 at 15:06

2 Answers2

6

I assume you're referring to the fact that calling a function can be "costly", compared to dumping all commands in a huge file.

Basically, yes - it will take more time (in theory). Should you worry about this time? No.

What you should worry about is the parts of your code that actually has an impact of time consumption. In your example, the mysql initiation is likely to take 90% of your processing time, if not more. You should therefor make sure you only connect to mysql once.

Also, the insert query may be faster on the mysql side, when sending one single query to do all inserts. However, this is also likely to be negligible. The best way to determine this is to test, and profile your code.

Bottom line - you should worry about making your code readable and maintainable first and foremost. Then do profiling after to detect real bottlenecks.

Jon Skarpeteig
  • 4,118
  • 7
  • 34
  • 53
3

Your way will be slower against one query.

see this test http://blog.cnizz.com/2010/05/31/optimize-mysql-queries-fast-mutliple-row-inserts/

UPDATE:

mysqli test:

<?php
$s = microtime(true);
$mysqli = new mysqli("127.0.0.1", "root", "pass", "test", 3306);
for($i=0;$i<1000;$i++){
    $mysqli->query("INSERT INTO admin SET name='hello world'");
}
$e = microtime(true);
echo $e-$s;
?>

28.007468938828 - INNODB

0.19577789306641 - MYISAM

<?php
$s = microtime(true);
$mysqli = new mysqli("127.0.0.1", "root", "pass", "test", 3306);
$sql = "INSERT INTO admin (`name`) VALUES ";
for($i=0;$i<1000;$i++){
    $sql.= "('hello world'),";
}
$sql = substr($sql,0,-1);
$mysqli->query($sql);

$e = microtime(true);

echo $e-$s;
?>

0.06469202041626 - INNODB

0.052706003189087 - MYISAM

(Tested on Athlon X2 2.7MHz)

and on Intel I3 2.4MHz about the same score

Dr Casper Black
  • 7,350
  • 1
  • 26
  • 33
  • Ouch.. that article is so misleading and completely incorrect that it hurts. And also, one big mystery is revealed - why people are still using `mysql_` functions - it's because of crap articles and everyone's right to have a blog. – N.B. Jun 07 '13 at 13:21
  • No, the whole _point_ of the article is hugely misleading. Yes, one big query will outperform x small ones. Why? Because mysql will lex the query once, opposed to lexing it 10 times. That's why smart people invented prepared statements. The other thing is testing with MyISAM - while MyISAM has its purposes, it's still not offering transactions. Doing inserts by parsing csv and inserting it is HUGELY more efficient if you wrap 100+ queries into a single transaction. That basically makes MySQL perform 100 writes in a single I/O. There are way too many wrong things in that "benchmark". – N.B. Jun 07 '13 at 14:13
  • If you're doing benchmarks like that, you should cover all scenarios because someone will think that's the way to go - when it totally isn't. Run your benchmark by creating 100 inserts wrapped in a single transaction (with InnoDB) and use prepared statements. You'll see vastly different results. And that's the scenario you haven't covered. And that's the scenario that people use when inflating databases with lots of data. – N.B. Jun 07 '13 at 14:14
  • -->N.B ; so do you think inserting data as "insert("username", "testInsert141");" for multiple insertion is good in INNODB transaction – GAURAV MAHALE Jun 07 '13 at 14:33
  • 1
    @GAURAVMAHALE - you use a prepared statement and then you just fire INSERT statements. It is: 1) much easier to understand 2) much easier to use 3) safer, because prepared statements use named parameters and existing libraries for PHP implement sanitization of input 4) it's much faster when used properly. – N.B. Jun 07 '13 at 14:44