Sql query: use where in or foreach?

Question

Hi,

I'm using query, where the piece is:

...where code in ('va1','var2'...')

I have about 50k of this codes.

It was working when I has 30k codes, but know I get:

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 partition

I think that problem is related with IN...

So now I'm planning use foreach(string code in codes) ...where code =code

Is it good Idea ??

Answer 1

I suggest you create a temporary table containing all the codes you want to match, and join against that instead.

However, we haven't really seen enough of your code to comment for sure.

Answer 2

I'm not sure where you got those 50k values, but if it is from another query, just JOIN to this table and get all the data at one time from one query.

Answer 3

First, create a temp table, call it #tmp0, with just one column. Then:

SqlConnection conn = new SqlConnexion(connection_string);
SqlCommand cmd = new SqlCommand("INSERT INTO #tmp0 (code) VALUE (@code)", conn);
conn.Open();

foreach (string s in bigListOfCodes)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@code", s);
    cmd.ExecuteNonQuery();
}
cmd = new SqlCommand("SELECT * FROM tbl " + 
          "WHERE code IN (SELECT CODE FROM #tmp0)", conn);

SqlDataReader rs = cmd.ExecuteReader();

while (rs.Read())
{
    /* do stuff */
}

cmd = new SqlCommand("DROP TABLE #tmp0", conn);
cmd.ExecuteNonQuery();
conn.Close();

I know this seems like a lot of work for the server, but it's really pretty fast.