ansaurus

Question

Answer 1

+2 A:

In SQL Server 2008 you can declare a table variable and pass it to your query from the client or between the procedures.

Quassnoi 2010-04-15 14:11:39

The query i have is in HQL (hibernate query language) embedded in j2ee code. Also this needs to work with oracle 10.2 as well. Not sure this will work

2010-04-17 10:58:05

Answer 2

+2 A:

For a modest number of values I would not have thought an IN (SELECT ..) would be that expensive on any rdbms.

You could INNER JOIN you IDs table or just break down the INs:

WHERE X IN (123,456 ...)
   OR X IN (789,987 ...)
   ...

Alex K. 2010-04-15 14:12:23

I already have the variables. Using your exampleWHERE X IN (123,456 ...) OR X IN (789,987 ...) 123,456 etc are already in variables. But i cannot list all 12,000 of them as there is a limitation to this. I am trying to avoid making another database hit for a save and then a select

2010-04-17 10:56:13

12,000?! how are you passing them to an in clause(s)?

Alex K. 2010-04-17 11:13:33

Answer 3

A:

If you put it in atable why are you still using the in clause, why not just join to the table?

HLGEM 2010-04-15 14:21:03

Sure i could do that. The main thrust of my question is NOT to store ids in a table. I already have those Ids in variables, but since there is this limitation on number of variables one can pass into an IN query, i am forced to store the ids in a table (extra database hit) and retieve (using JOIN or IN) (another db hit

2010-04-17 10:53:41

Answer 4

+2 A:

Agree with Alex

Instead of

Select * From TableA Where ID IN (Select ID from IDTable)

Use

Select * From TableA
INNER JOIN IDTable ON TableA.ID = IDTable.ID

The join will automatically filter the IDs for you.

Jeremy 2010-04-15 14:23:06

http://explainextended.com/2009/06/16/in-vs-join-vs-exists/

Quassnoi 2010-04-16 09:09:52

@Quassnoi Thanks for the link to the in depth article. In the OPs case, they are storing the IDs in a table, so I was just pointing out that the JOIN way was going to be equivalent if all they are doing is filtering. Also, 9 times out of 10 if i have a choice I'll use the join method for readability and style reasons. It's also worth noting that an IN with comma separated values gets expanded to a bunch of ORs when it is actually processed.

Jeremy 2010-04-16 13:01:33

@Jemery: `IN` with comma separated values expands to a `CONSTANT SCAN` when a certain threshold of values is hit. The values are regarded as a table subject to normal set-based operations (hash join, merge join etc).

Quassnoi 2010-04-16 13:47:15

I wasn't aware of that! Do you have any idea what the threshold is, or does it vary according to the query? The reason I ask is because I've worked on projects where stored procedures receive a comma separated list of values from SSRS parameters. I went through the work to parse the string and put the IDs in a temp table in order to join to it. I'm thinking this was still a good idea, because the user could have chosen just 1, 2, or everything in the parm list. The other reason we wen through the trouble is that I think we would have had to use dynamic SQL if we just wanted to use the list.

Jeremy 2010-04-16 14:25:42

ansaurus

tags:

views:

answers:

problem with IN clause in SQL

related questions