Internal Query Processor Error ... query too long :( 2

[orangefluffy]

Hi All,

I am getting the following error in SQL 2000.
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.

My query gets way too long, maybe in-efficient and now, it's giving me an error.

---- Here is the background detail. ----
table: tblNodeApp [nodeAppID, nodeID, appID]
Users select nodeIDs (nodeID 203, 95, and 91) in this case.

* nodeid 203 can have value appid 1, 2, 3, 4, 5
* nodeid 95 can have value appid 3, 4, 5
* nodeid 91 can have value appid 1, 2, 3

The result should be appid 3. application 3 is intalled on all three nodeid(servers). I want to list applications that are mutual/common to the selected NodeIds (servers).

select appid from tblNodeApp
where nodeId = 203 and
appid in (select appid from tblNodeApp where nodeId = 95) and
na.appid in (select appid from tblNodeApp where nodeId = 91)
--------------

When users select numerous nodeIDs (could be 120+ nodeIDs), I get this error. Otherwise, it works fine.Is there another way to get the result I want?

Thank you very much in advance.

 

[SQLSister]

I take you are creating the query dynamically? This would be one of the reasons why dynamic SQl is a bad idea since it is hard to test for all possible values. I would suggest you take the input values (look in the FAQS for how to split out a string of input values) and put them in a temp table then join to the table. This would be both more efficient and shorter code than using a series of subselects.



"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

tblNodeApp [nodeAppID, nodeID, appID]
Users select nodeIDs (nodeID 203, 95, and 91) in this case.

CREATE PROCEDURE NodeAppList(@NodeIDList varchar(8000))
AS
SELECT
   appID
FROM
   NodeApp A
   INNER JOIN dbo.Split(@NodeIDList, ',') L ON A.nodeID = L.Value
GROUP BY
   appID
HAVING
   Count(*) = (SELECT Count(*) FROM dbo.Split(@NodeIDList, ',') L)

For the Split function see the FAQs in this forum, "passing a list of values to a stored procedure" parts 2 and 3.

I think you'll be surprised at the performance of this compared to your old way. For what it's worth, a very common mistake I see T-SQL beginners make is heavy overuse of:

where value in (select value from table)

In general, joins are far superior to this construction and a beginner should think in terms of joins almost exclusively. There are times when this construction is best, but you have to keep in mind that it expands to a series of OR statements, one for each value.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[orangefluffy]

Thank you everyone. It's working and working better now.

Yes, the nodeId list is created dynamically.

I tried ESquared's solution. dbo.Split isn't available in SQL server 2000. I ended up inserting data into a temp table to store the nodeIDs.
=============================================
CREATE PROCEDURE sp_GetAppList

@nodelist VARCHAR(8000)

AS
DECLARE @spot SMALLINT, @nodeId_m VARCHAR(8000)

-- a) create a temp table
CREATE TABLE #yaks (YakID int)

-- b) take each node id and insert them into a temp table
WHILE @nodelist <> ''
BEGIN
SET @spot = CHARINDEX(',', @nodelist)
IF @spot>0
BEGIN
SET @nodeId_m = CAST(LEFT(@nodelist, @spot-1) AS INT)
SET @nodelist = RIGHT(@nodelist, LEN(@nodelist)-@spot)
END
ELSE
BEGIN
SET @nodeId_m = CAST(@nodelist AS INT)
SET @nodelist = ''
END

insert into #yaks values(@nodeId_m)

END
-- b) end of b

select appid, appname from tblapps where appid in(
select appid
from tblnodeapp
where nodeid in ((select YakID from #yaks))
group by appid
having count(appid) = (select count(distinct YakID) from #yaks)
) order by appname

drop table #yaks
GO
===========================================================

 

[gmmastros]

For the Split function see the FAQs in this forum, "passing a list of values to a stored procedure" parts 2 and 3.

faq183-5207
faq183-6684



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

orangefluffy,

Please do those who spend their time and effort to help you the service of actually reading their posts carefully. See George's post just above this one.

where nodeid in ((select YakID from #yaks))

For what it's worth, a very common mistake I see T-SQL beginners make is heavy overuse of:

where value in (select value from table)

In general, joins are far superior to this construction and a beginner should think in terms of joins almost exclusively.

Anyway, how much better is it working?




[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[orangefluffy]

It's working way faster! My old SQL is so elementary. It's embarrassing.

Thanks Esquared for pointing out George's statement regarding Split. I am new on this forum. I feel a bit lost.

 

[ESquared]

Thanks Esquared for pointing out George's statement regarding Split.

I didn't point out George's statement regarding Split. I pointed out him pointing out my statement regarding Split.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]