3

I have two queries visiting two different SQL Server to avoid Link Server Communication. First query get a List of OrderIDs, I use c# to organize them into a string that passes to the second query, like the following format:

Select .....
From .....
Where OrderID in (1,2,3,4,5,6)

-- 1,2,3,4,5,6 is the process from the result of first query.

I found if there are a lot orderIDs retrieved from the first query, the second query will timeout.

Is there any practice how many elements can pass to "IN" Clause?

DOK
  • 32,337
  • 7
  • 60
  • 92
HanYi Zhang
  • 331
  • 1
  • 8
  • 21
  • 1
    Do you have a good index on the OrderID column? – Anthony Pegram May 16 '13 at 14:55
  • Looks like folks did some testing of this over at: http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach – dnord May 16 '13 at 14:56
  • http://stackoverflow.com/questions/1069415/t-sql-where-col-in – bummi May 16 '13 at 14:57
  • Clearly whatever the practice is, you're past it if you're to the point where the query fails to execute. Knowing what you should pass doesn't seem like it will help you at this point. Perhaps you want to ask how else to get the desired result in a manor that will scale to such a large number of items? Or do you already know an appropriate solution and want to know if you should be using it; if so, the answer is yes. – Servy May 16 '13 at 14:58

3 Answers3

3

If you need to pass so many elements that this becomes a problem, you should consider a stored procedure with a used defined table valued parameter as a parameter (which should contain your list).

edit: see http://blogs.msdn.com/b/felixmar/archive/2010/10/27/how-to-create-and-execute-a-stored-procedure-using-a-table-as-a-parameter.aspx

David S.
  • 5,965
  • 2
  • 40
  • 77
  • The ultimate purpose is not to use Link Server communication. So I have two connection strings to get the final result. A table valued parameter has to store something in the database however where does not have access from either query One or query Two – HanYi Zhang May 16 '13 at 15:57
  • @HanYiZhang The servers doesn't have to know about each other. My point is that you can construct the table valued parameter in c# with values from the first query. The first server doesn't need to know about it. – David S. May 16 '13 at 17:07
  • David, Thank you! this answer is accurate enough for use. From the reference you provided, the table value parameter does not require creating table in either database prior to using it. It's very neat and useful. – HanYi Zhang May 16 '13 at 19:01
1

There is a hard limit, but it's very high. If you reach that limit though it won't time out but rather refuse to let you do the query at all.

In your case the query is simply taking too long to run. But the more items in your 'in' array, the longer your query will take to execute since it has more to do. If your query takes too long, it will time out as you found.

This isn't about limits and rather about tolerances.

What you should possibly try doing is paginating your queries so you're limiting it to 100 or so rows at a time and doing multiple queries.

You may also want to consider using an ORM like Entity Framework (you've got the C# tag, but there's no C# here so I'm assuming that's what you're in) - then it'd look something like this:

Orders.Where(o => OrderIDs.Contains(o.OrderID)).Skip(100).Take(100);

That's my personal bias though so pinch of salt as to whether or not it'd be good for you to do.

PhonicUK
  • 13,486
  • 4
  • 43
  • 62
-1

It's 2100 elements. See the documentation.

Axarydax
  • 16,353
  • 21
  • 92
  • 151