0

I am facing the titled error. I searched for the issue on google found manmy people facing the same issue but non of them matches my scenario.

I have a simple query but there are 100,000 of conditions. query looks like.

SELECT TEXT,LANG,PLANTCODE FROM DROP_VALUE WHERE FIELDNAME='PROFIT_CTR' AND PLANTCODE IN ('953306843','469940529','293294653',......100,000 conditions)

I am using eclipse JUNO and (on the same local machine) sql server 2008 r2 enterprise edition.
Windows 7 professional
8 gb RAM
i3 processor.
3gb memory allocated for JVM.

Please suggest me possible causes.

Amit Sharma
  • 1,202
  • 11
  • 26
  • 1
    100K values is a huge number to put in an `IN` list, have you seen [this question](http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach)? Note that one of the answers describes getting a resource error with around 100K values. – Pondlife Apr 18 '13 at 16:58

1 Answers1

2

If you can, move the conditions to a table structure and JOIN to it.

Sam
  • 7,543
  • 7
  • 48
  • 62
  • Ha! I had another error with lots of items in an `IN` clause. I changed it to a `CREATE TABLE #temp(id int not null primary key) 300 000 odd INSERT VALUES statements; SELECT wanted fields FROM wantedTable wt JOIN #temp t ON wt.Id = t.Id` and I _now_ get this error, and due to it occurring when I had a syntax error in my `Primary Key` constraint, I know this error is actually occurring during the parsing of the whole statement set :-( – Mark Hurd Sep 30 '13 at 04:06
  • 1
    Looks like I'll have to restructure and use a table-value parameter. – Mark Hurd Sep 30 '13 at 04:10