"infinite" sql statement?

Question

Hi there,

I've got a very strange sql-related problem.

I'm accessing a MSSQL Server 2005 with PHP (odbc), when I profile the sql statement the following is executed:

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT  fieldA,  fieldB, fieldC, fieldD, fieldE FROM mytable WHERE fieldB IS NULL',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

exec sp_cursorfetch 180150003,2,1,1

On my own server it's working fine, on the customer-server the sp_cursorfetch reads infinite rows and loads the full cpu. When I try to execute the statement itself

SELECT  fieldA,  fieldB, fieldC, fieldD, fieldE FROM mytable WHERE fieldB IS NULL

in SQL Server Management Studio it works fine (under 1sec).

Any ideas?

Edit: the main difference between the servers is that my server is a x86 (Win2003) and the server of the customer is a x64 (Win2008).

Edit2: Added Where-Clause

Answer 1

There's no WHERE clause in that SELECT statement, so you'll be doing a table scan over every row in that table. If your customer has many more rows than your local server, that would explain the time discrepancy.

Answer 2

Your cursor is declared as DYNAMIC (@p3 =2). In the Management Studio, try to declare it as FAST FORWARD (@p3=16) and see if it helps.

Paste the profiler output you posted into Management Studio, replacing the parameter in sp_cursorfecth:

exec sp_cursorfetch @p1, 2, 1, 1

and see if the problem persists.

Answer 3

I don't know about SQL server, but Oracle doesn't even index NULL values. Even if it does, field IS NULL is not a selective criterion, so you might get a full table scan, which might take a long time if your customer has a lot of data.

Answer 4

The larger the dataset the longer it takes. You will want to limit your result set with a where clause. Don't make your application server do all of the work. Adding an index to the column that you are filtering on will allow the database server to only give you what you want so you don't have to loop thru it later.

Answer 5

Well, I wasn't sure if I'm allowed (by my company) to put complete sql-statements here.

This is what I'm actually executing in PHP:

SELECT 
A.id, A.empl, A.valid_from,
A.salutation, A.account
FROM persons A
LEFT JOIN persons_comp B 
ON
A.id = B.id 
AND A.empl = B.empl 
AND A.valid_from = B.valid_from 
AND A.salutation = B.salutation 
AND A.account = B.account 
WHERE 
B.empl IS NULL