Can someone help explain this...

[Jonathan Schafer]

Here is my query, that is built dynamically and executed in a C# console
program using ADO.NET...

"SELECT IPCode, CCID " +

"FROM dbo.Retailer_040211_10109307 (NOLOCK) " +

"WHERE CCID IS NOT NULL AND IPCode NOT IN " +

"(SELECT IPCode FROM dbo.Retailer_040211_10109307_WorkArea (NOLOCK))";

When this query is submitted to Sql Server, after a few seconds, CPU
utilization jumps to 100% and the query just runs and runs and runs.



When I submit the same query in Query Analyzer, it runs just fine.

When I submit the query below in the same program, it runs fine...

"SELECT IPCode, CCID " +

"FROM dbo.Retailer_040211_10109307 (NOLOCK)" +

"WHERE CCID IS NOT NULL AND IPCode NOT IN " +

"(SELECT IPCode FROM dbo.Retailer_040211_10109307_WorkArea (NOLOCK))";



Looks identical don't they. Yes, except there is a space removed after the
first (NOLOCK) in the query that runs correctly and the query that doesn't.

This query also works correctly.

"SELECT IPCode, CCID " +

"FROM dbo.Retailer_040211_10109307 WITH (NOLOCK) " +

"WHERE CCID IS NOT NULL AND IPCode NOT IN " +

"(SELECT IPCode FROM dbo.Retailer_040211_10109307_WorkArea WITH (NOLOCK))";

Leaving the space in and adding the 'WITH' prior to the NOLOCK works fine.

The program uses the following ADO.NET classes

SqlConnection

SqlCommand

DataSet

SqlDataAdapter

It uses the SqlDataAdapter.Fill method, and then gets the results in Xml
format using the DataSet object.

The server is an 8-way, Sql Server 2000 running under Windows 2000
DataCenter.

Does anyone have any ideas? Obviously, I modified the program that
generates the SQL to use the WITH prior to NOLOCK since that is the standard
now, but this has me a bit baffled.

Thanks,

Jonathan Schafer

 

[whlatimer]

I'd try (Not CCID is Null)
and (Not ... in ( select ..))