How to speed up the insert action in MS SQL SERVER with php5.3?

Question

I'm using apache2.2 ,php 5.4 , with php_sqlsrv_53_nts_vc6.dll , to do large insert with MS SQL SERVER.

I'm stuck with the very-low-speed of the insert action: Insert 100 rows ,cost 28 seconds! Even when I use

sqlsrv_begin_transaction( $conn ) sqlsrv_commit( $conn )

It still cost 7 to 11 seconds.

Then I tried the Navicat with MS SQL SERVER , use BEGIN TRANSACTION; COMMIT; I can insert 8000 rows in 6 seconds.

So, I guess it's not the MS SQL SERVER's problem , it's PHP and php_sqlsrv_53_nts_vc6.dll 's problem.

Can anyone tell me how to speed up the insert action in php with MS SQL? or show me some codes ?

Here's my codes:

in tools.php:

public static $dbtype = FALSE;
public static function query($sql,$conn){
    if(self::$dbtype==FALSE){
        self::$dbtype = tools::getConfigItem("DB_TYPE");
    }
    $res = FALSE;
    if(self::$dbtype=="mysql"){
        $res = mysql_query($sql,$conn);
    }
    if(self::$dbtype=="mssql"){
        $sql = iconv('UTF-8','GBK',$sql);
        $res = sqlsrv_query($conn,$sql);
    }           
    return $res;
}

public static function transaction($conn){
    if(self::$dbtype=="mysql"){
        mysql_query('START TRANSACTION;',$conn);
    }
    if(self::$dbtype=="mssql"){
        if ( sqlsrv_begin_transaction( $conn ) === false ) {
             die( print_r( sqlsrv_errors(), true ));
        }
    }
}

public static function commit($conn){
    if(self::$dbtype=="mysql"){
        mysql_query('COMMIT;',$conn);
    }
    if(self::$dbtype=="mssql"){
        sqlsrv_commit( $conn );
    }
}

in install.php:

public static function step4_2(){
    $t_return = array("status"=>"2","msg"=>"");
    $sqls = json_decode2($_REQUEST['sqls'],TRUE);
    if(count($sqls)==0){
        return array(
            'status'=>'2'
            ,'msg'=>'wrong request:'.$_REQUEST['sqls']
        );
    }
    $conn = tools::getConn();
    tools::transaction($conn);
    for($i=0;$i<count($sqls);$i++){
        $sqls[$i] = strtolower($sqls[$i]);
        $res = tools::query($sqls[$i],$conn);
    }
    tools::commit($conn);
    $t_return = array("status"=>"1","msg"=>count($sqls)." sql executed ");
    return $t_return;
}

Thank you in advance

user3001003 · Accepted Answer · 2014-01-04 08:38:17Z

1

OK， I found my solution by myself.

    tools::transaction($conn);
    if(tools::$dbtype=="mssql"){
        $str = implode(";",$sqls);
        tools::query($str,$conn);
    }
    else{
        for($i=0;$i<count($sqls);$i++){
            tools::query($sqls[$i], $conn);
        }
    }   
    tools::commit($conn);

send all the sql , split by ";" send all the sql by once , to speed it up

answered Jan 4, 2014 at 8:38

user3001003

112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to speed up the insert action in MS SQL SERVER with php5.3?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related