none
SQL Query does not complete

    Question

  • Hello,


    first, I am using SQL Server Driver for PHP 1.0, SQL Manager 2008 Lite and SqlServer 2005.

    I am currently devoloping some scripts, that allow to check the contents of SAP Batch Input Files against some masterdata, before SAP processes them.

    The files are around 5,000 lines, and it took like nearly a minute when they were processed to read the data, 'cut out' the different fields in a line and insert them into a table.

    I therefore started to try out different approaches to increase the performance:

    1. Read a line, then send an Insert Statement (took the longest)
    2. Prepare an Insert Statement, Read a line and call the Prepared Statement with variables (was quicker but still quite long)
    3. Read a line, create the Insert Statement and add it to a string. When all lines were read, pass the complete string (consisting of 5.000 Inserts seperated by ; ) at once.

    The fastest one was option 3, but then I found out, that only ~325 rows were send to the table.

    I asked my colleagues who are in charge of our databases if there is some limitation in data I can send to the Server with one query or something similar, but they said no. The server should accept any size of query.

    I then changed my script to send 300 Insert Statement chunks, which works fine and still is the fastest method.

    As I send the same amount of data in total (still 5,000 lines) to the SQL Server, only in chunks, I have no idea which PHP setting may cut the Insert Statements, it cannot be the case that the script runs for to long.

     

    At a later stage some fields from the table are aggregated and inserted into a second table for statistical usage.

    For this purpose I wrote a Stored Procedure, which does what it was intended to, but only when I run it from the SQL editor in SQL Manager Lite. When I call the Stored Procedure from my PHP Script, only 48 entries can be found in the target table (instead of 1.929 created when executed from SQL Manager Lite).

     

    Can anybody explain this behaviour, why SQL statements are cut, or, as with the SP, not executed tothe end, when send from PHP? Any idea what I need to change to make them work properly?

     

    The solution with sending 300 Insert Statements does work, but as I do not know what the limiting factor is, it is possible, that with lines double as long as now, I will only get 150 Insert Statements through. If at all possible, I would like to be prepared for every possible query.

    I have not found a work-around for the SP though... 

    Thank you very much.

    • Edited by Matthiasql Friday, March 05, 2010 8:14 PM typos
    Friday, March 05, 2010 4:24 PM

Answers

  • Matthiasql,

    Robert's script works indeed because of the sqlsrv_next_result.  Each insert you are running in that result generates a resultset of a row count.  

    The other, and simpler workaround is to put "set nocount on" in the query before anything else, and this will prevent SQL Server from generating row count information for each of the 5000 inserts.

    The problem is that the results (row count) for each insert are choking the output, such that insertion won't proceed until the results are consumed.  This means that the second statement, which can execute because MARS is on, only sees that ~300 rows have been inserted because that's all that really have been inserted.

    mssql_query reads row counts before returning which is why it doesn't need "set nocount on" in the query.

    Jay

     


    Jay Kint MSFT
    Friday, June 18, 2010 5:50 PM

All replies

  • Can you check the setting for the memory_limit directive in your php.ini file? (Look at the Resource Limits section...more info here: http://www.php.net/manual/en/ini.core.php). I'm not sure if this setting is causing your issue, but it's an easy one to check.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Friday, March 05, 2010 8:48 PM
  • Hello Brian,

    I checked that setting. It is memory_limit = 128M. I changed it to memory_limit = 1280M which did not change the results, the queries still do not get executed correctly. Just to be sure I added some more zeros, but that did not help either.

    Any more ideas?

    Kind Regards

    Matthias
    Monday, March 08, 2010 9:02 AM
  • There is a limit on the query length, as specified here: http://msdn.microsoft.com/en-us/library/ms143432.aspx , and you can see the 'Batch Length' defaults to 256MB.

    Regarding php mem limit, make sure you restart your web server after changing php.ini settings.  If you are moving around large strings, you will need at least double the amount allocated in php, because you need space for the file you are reading, and space to store the query, and anything else.

    Either way though, I doubt that the ~325 rows that succeed are anywhere near these limits.

    Does the php log reveal anything, and what does sqlsrv_errors() return after the query executes?  It's unusual not to get an error.

    Monday, March 08, 2010 12:39 PM
  • I think that the enclosed php code demonstrates the issue that the "SQL Query does not complete" poster is having. We had to switch back to mssql because of this issue.

    I am running PHP 5.2.13 with Apache 2.2.14 on Server 2003 with "Microsoft SQL Server 2008 Native Client" and Version 1.1 SQL Server Driver for PHP installed.

    PHP has both the sqlsrv and the mssql dll's installed
    (extension=php_sqlsrv_52_ts_vc6.dll and extension=php_mssql.dll).

    I am running queries against SQL Server 2008 running on Server 2008 64 bit.
    (By the way, it took a couple of days to figure out that "named pipes" had to be enabled on the server to get mssql to connect).

    When I run the php code that follows, this is what I get:

      5000 rows attempted with sqlsrv_query, actual rows created = 521
      5000 rows attempted with mssql_query, actual rows created = 5000

    <?php
      define('SQLSVR','YOURSERVER');
      define('USERID','your id');
      define('PWD','your password');
      define('DB','yourdb');
      define('CONNINFO','return array("UID"=>'.USERID.',"PWD"=>'.PWD.');');

      $qry_create =
    "
    use ".DB."
    select a1='1234567890',a2='0987654321',a3='1234567890',a4='0987654321',a5='1234567890',a6='0987654321'
    into #test_tmp

    declare @i int
    set @i=1
    WHILE @i < 5000
    BEGIN
      insert #test_tmp
      select a1='1234567890',a2='0987654321',a3='1234567890',a4='0987654321',a5='1234567890',a6='0987654321'
      set @i = @i + 1
    END
    ";

      $cnx = sqlsrv_connect(SQLSVR,eval(CONNINFO));
      if ( !$cnx ) die(print_r(sqlsrv_errors()));
      $cur = sqlsrv_query($cnx,$qry_create);
      if ( !$cur ) die(print_r(sqlsrv_errors()));
      $qry_select = 'select cnt=count(*) from #test_tmp';
      $cur = sqlsrv_query($cnx,$qry_select);
      $row = sqlsrv_fetch_array($cur);
      print("<br>5000 rows attempted with sqlsrv_query, actual rows created = ".$row[0]);
      $qry_drop = 'drop table #test_tmp';
      $cur = sqlsrv_query($cnx,$qry_drop);

      $cnx = mssql_connect(SQLSVR,USERID,PWD);
      if ( !$cnx ) die(mssql_get_last_message());
      $cur = mssql_query($qry_create,$cnx);
      if ( !$cnx ) die(mssql_get_last_message());
      $qry = 'select cnt=count(*) from #test_tmp';
      $cur = mssql_query($qry,$cnx);
      $row = mssql_fetch_row($cur);
      print("<br>5000 rows attempted with mssql_query, actual rows created = ".$row[0]);
      $qry = 'drop table #test_tmp';
      $cur = mssql_query($qry,$cnx);
      exit;
    ?>

    Monday, March 08, 2010 10:03 PM
  • Thank you very much Kenny. I installed the mssql extension for PHP and both the 5,000 Insert Statements at once as well as the execution of the SP worked fine without any further modification to my php.ini.


    Any idea what the problem with the sql_server extension is?


    As you used v1.1 it would be no benefit for me to upgrade from 1.0. Seems like a major bug in the sql_server extension...
    Tuesday, March 09, 2010 9:13 AM

  • Does the php log reveal anything, and what does sqlsrv_errors() return after the query executes?  It's unusual not to get an error.


    No, I did not get any message. That is why I had no idea where to search for a solution. Everything seemed as if it worked out fine.
    Tuesday, March 09, 2010 9:16 AM
  • You are welcome, Matthiasql!  I have even made the transact sql code a procedure, executed it with sqlsrv_query, and it still produces the same number of rows (521) instead of the 5000 as expected.  Go figure.  I anxiously await an explanation from Microsoft.
    Tuesday, March 09, 2010 1:03 PM
  • I tried the trick with the SP as well... I would be very interested in the explanation... I do not want to change all my php code from sqlsrv to mssql...
    Tuesday, March 09, 2010 3:37 PM
  • Thanks for reporting this issue and the code to repro the issue. We are looking into it and will respond back when we understand the issue.


    Ashay Chaudhary - MSFT
    Program Manager, SQL Server Driver for PHP
    Wednesday, March 10, 2010 8:48 PM
  • I was intrigued by this so I ran a version of your sample code, and it all worked as expected. 

    Here is what I ran, maybe the key difference is to call 'sqlsrv_next_result()' until there are no more results?....

    define('SQLServer', 'mySqlServer');
    define('SQLDatabase', 'myDatabase');
    $SQLConnectionInfo = array('Database' => SQLDatabase, 'LoginTimeout'=>10, 'UID'=>'myUID', 'PWD'=>'myPassword');
    $conn = sqlsrv_connect(SQLServer, $SQLConnectionInfo);

    $tsql = <<<SQL
    select a1='1234567890',a2='0987654321',a3='1234567890',a4='0987654321',a5='1234567890',a6='0987654321'
    into #test_tmp;
    declare @i int
    set @i=1
    WHILE @i < 5000
    BEGIN
      insert #test_tmp
      select a1='1234567890',a2='0987654321',a3='1234567890',a4='0987654321',a5='1234567890',a6='0987654321'
      set @i = @i + 1
    END;
    select @i 'MyCount', count(*) 'TableRows' from #test_tmp;
    drop table #test_tmp;
    SQL;

    echo "<pre>Start:\r\n";
    if ( ($res = sqlsrv_query($conn, $tsql)) )
    {
        do
        {
            $errs = sqlsrv_errors();
            if ( $errs )
            {
                echo 'Errors    : ' . print_r(sqlsrv_errors(),1);
            }
           
            while ( ($row=sqlsrv_fetch_array($res)) )
            {
                print_r($row);
                echo "\r\n";
            }
            $next = sqlsrv_next_result($res);
        } while ( $next ) ;   
        sqlsrv_free_stmt($res);
    }
    else
    {
        echo 'Errors    : ' . print_r(sqlsrv_errors(),1);       
    }
       
    echo "End!</pre>";
    sqlsrv_close($conn);

     


     

    Result:

    Start:
    Array
    (
    [0] => 5000
    [MyCount] => 5000
    [1] => 5000
    [TableRows] => 5000
    )

    End!

     

    Monday, March 29, 2010 3:54 PM
  • Matthiasql,

    Robert's script works indeed because of the sqlsrv_next_result.  Each insert you are running in that result generates a resultset of a row count.  

    The other, and simpler workaround is to put "set nocount on" in the query before anything else, and this will prevent SQL Server from generating row count information for each of the 5000 inserts.

    The problem is that the results (row count) for each insert are choking the output, such that insertion won't proceed until the results are consumed.  This means that the second statement, which can execute because MARS is on, only sees that ~300 rows have been inserted because that's all that really have been inserted.

    mssql_query reads row counts before returning which is why it doesn't need "set nocount on" in the query.

    Jay

     


    Jay Kint MSFT
    Friday, June 18, 2010 5:50 PM
  • Hello !

     

    I got really weird things here too with Apache 2.2.14 / PHP 5.2.13 / SQL Server Driver for PHP 1.1 (same thing with 2.0 CTP2) / SQL Server 2008 R2.

    You can reproduce it in an easy way, just create two simple tables :

    CREATE TABLE [dbo].[TEST_IN](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[ID_TABLE_OUT] [int] NULL
    ) ON [PRIMARY]
    
    
    CREATE TABLE [dbo].[TEST_OUT](
    	[ID] [int] NULL
    ) ON [PRIMARY]

    Then, fill table "TEST_OUT" with 200 inserts.

    Now, create a little procedure :

    CREATE PROCEDURE [dbo].[TEST_TABLE_IN_OUT]
    AS
    BEGIN
    
    	DECLARE @ID_OUT INTEGER;
    	DECLARE @c1 CURSOR;
    
    	SET @c1 = CURSOR LOCAL FAST_FORWARD FOR
    		SELECT id
    		FROM test_out
    		
    	OPEN @c1;
    	FETCH @c1 INTO @ID_OUT;
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    
    		insert into TEST_IN values (@ID_OUT);    
    		insert into TEST_IN values (@ID_OUT);    
    		insert into TEST_IN values (@ID_OUT);   
    
    	FETCH @c1 INTO @ID_OUT;
    	END
    	CLOSE @c1;
    END

     

    Funny things here, from a PHP script :

    1) using the php_mssql.dll (bundled with PHP binaries), run this code

    $link = mssql_connect($DBserver, $DBuser, $DBpasswd);
    if (!$link || !mssql_select_db($DBname, $link))
    	die('Unable to connect or select database!');
    $query = mssql_query('EXEC TEST_TABLE_IN_OUT');

    Table "TEST_IN" got 600 rows --> OK

     

    2) using the php_sqlsrv_52_ts_vc6.dll (from SQL Server Driver for PHP 1.1), run this code

    $SQLConnectionInfo = array('Database' => $DBname, 'LoginTimeout'=>10, 'UID'=>$DBuser, 'PWD'=>$DBpasswd);
    $conn = sqlsrv_connect($DBserver, $SQLConnectionInfo);
    $tsql = 'EXEC TEST_TABLE_IN_OUT';
    $query = sqlsrv_query($conn, $tsql);
    sqlsrv_free_stmt($query);
    Table "TEST_IN" never got over 200 rows (results vary from time to time)

     

    Maybe this simple example can help you to help me ... ^_^

    Thursday, June 24, 2010 2:54 PM
  • Hello PY-D,

    I tested your example with PHP532, SQLServer2005, IIS7 and SQLSRV1.1, and the same effect occurs (only 188, 189, 190, 188 ... rows in TEST_IN). Really strange!

    Regards,

    Klaus

    Monday, September 06, 2010 3:05 PM
  • Hello PY-D,

    I observed that, after putting a "commit transaction" after each of the three loop insert's in your example SP, only about 16 instead of about 190 rows will be inserted into table TEST_IN. Maybe a problem of limited resources?

    In the meanwhile, I tried it with SQLSRV2.0 and SQLServer 2008 too, but without success.

    Regards,

    Klaus

    P.S.: Adding "set nocount on" to your SP, as suggested above, let your script run correctly.

    Tuesday, September 07, 2010 9:23 AM
  • Hello Klaus, you're right about the "set nocount on", we got an answer from tech support few days ago, you've just posted it before me :p

    Thanks for your reply.

    Tuesday, September 07, 2010 12:26 PM