2

I have the following query below:

Select t1.[Column1]
From [dbo].[Table1]
Where(
    t1.[Column2] = @parameter0
AND
    t1.[Column3] IN (@parameter1, @parameter2, @parameter3...............)

and I have parameters up to @parameter1390.

C# application that is using this SQL query reports

An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

What can I do to optimize my query?

Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
Gerald
  • 1,033
  • 5
  • 20
  • 41
  • 1
    Where do these many parameters come from? – juergen d Jul 21 '14 at 13:27
  • 17
    Put the values into a table and then do a join on the table. In addition to better performance, it will be much more scalable. – Hambone Jul 21 '14 at 13:27
  • Since it's in C#, run a single parameter at a time in a loop and add the values to a variable. – George Daniel Jul 21 '14 at 13:29
  • 1
    Yeah look at using table valued parameters instead of 1300+ individual parameters. And why do have so many parameters? Seems there ought to be a much simpler way of doing whatever it is you are doing. – Sean Lange Jul 21 '14 at 13:30
  • So your question is related to do with c# and not SQL. – Rahul Jul 21 '14 at 13:32
  • 2
    Another (less optimal) solution is to split the long `IN` into several `IN`s `OR`ed together. However, putting it in a table is better. – Jonny Jul 21 '14 at 13:35
  • @RobertKoritnik, No it's not a duplicate. OP is not trying to limit that `IN` in SQL cause would have it been SQL then we probably don't need to restrict. OP rather, trying to pass that IN clause from C# where facing the issue. – Rahul Jul 21 '14 at 13:44
  • Thank you guys for the suggestions. I will try @Hambone's suggestion. – Gerald Jul 21 '14 at 13:47

2 Answers2

0

As you can use a temp table in order to keep your values, You can use union operator to reach goal.

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26
0

Try this cosmetic changes in your code

Select t1.[Column1]
From [dbo].[Table1]
Where(
    t1.[Column2] = @parameter0
AND
    t1.[Column3] IN 
(select a from (values(@parameter1), (@parameter2), (@parameter3),...) x(a))
msi77
  • 1,602
  • 1
  • 11
  • 10