Home » SQL ServerRSS

SQL Query does not complete

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:

Read a line, then send an Insert Statement (took the longest) Prepare an Insert Statement, Read a line and call the Prepared Statement with variables (was quicker but still quite long) 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.

 

16 Answers Found

 

Answer 1

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

 

Answer 2

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
 

Answer 3

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.

 

Answer 4

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;
?>

 

Answer 5

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...
 

Answer 6


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.
 

Answer 7

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.
 

Answer 8

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...
 

Answer 9

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
 

Answer 10

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!

 

 

Answer 11

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.

As for why exactly it is happening, I'm not sure.  I have a wild guess:  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.

Jay

 

 

Answer 12

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 ... ^_^

 

Answer 13

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 &qut;AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

SOoooo what can I do? You're right, it is really the cause, Thanks for your reply!
 

Answer 14

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

 

Answer 15

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.

 

Answer 16

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.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter