Question

I am tasked with the following: The customer wants to read a number (possibly thousands) of values from a csv file. I have to use these values in an SQL select statement like this:

SELECT * FROM myTable WHERE myTable.Value IN (cvsValue1, csvValue 2, ..., csvValueN)

The question is: Will this work for an arbitrary number of csv values, and will it perform well for large number of values?

I will need to save the SQL as a string internally in my C# application for later use. (if that makes a difference for alternative solutions)

Answer 1

你真的不想这样做。最好将这些数值列入一个指数化表格,并作为子顺序使用IN(通常实施() 相对于阵列(通常作为系列操作实施)。

http://msdn.microsoft.com/en-us/library/ms177682.aspx“rel=”_BOL:


  Including an extremely large number of values (many thousands) in an
  IN clause can consume resources and return errors 8623 or 8632. To
  work around this problem, store the items in the IN list in a table.
  
  Error 8623:
  
  The query processor ran out of internal resources and could not
  produce a query plan. This is a rare event and only expected for
  extremely complex queries or queries that reference a very large
  number of tables or partitions. Please simplify the query. If you
  believe you have received this message in error, contact Customer
  Support Services for more information.
  
  Error 8632:
  
  Internal error: An expression services limit has been reached. Please
  look for potentially complex expressions in your query, and try to
  simplify them.

Answer 2

本国运营商的业绩确实很差,而且有许多价值(有时,你的请求没有这么做和坠毁)。

我也这样做。

“......

SELECT cast(Items as int) as id INTO #table_temp
FROM dbo.split(@str_ids_comma, , )

SELECT * 
FROM myTable 
WHERE EXISTS(
SELECT *
FROM  #table_temp
WHERE id = myTable.Value
)

“...... you can find a definition of split here : http://www.sqlservercentral.com/articles/Tally+Table/72993/

Answer 3

I guess that max allowed number of elements in the list for IN operator depends on database. I read somewhere I think, because I had similar problem, that OracleXE has a limitation of 1000 list elements. You should look into this for your specific database.
As for the alternative solution, you could split this query into several batches maybe, where list contains smaller number of elements.

友情链接