File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Problem with SQL where IN clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with SQL where IN clause" Watch "Problem with SQL where IN clause" New topic
Author

Problem with SQL where IN clause

Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Hi Everyone

I know this issue is not related to JDBC. Its more an sql question.

I'm using ORACLE database. I have a query that looks something like this.

select distinct sp.first_nm,
sp.last_nm,
psp1.prfl_value_txt
from person sp,
prfl_person psp1,
acct asi
where sp.src_person_id = psp1.src_person_id
and psp1.prfl_value_id=6
and asi.acct_id in (200328,204659,237316,237592,238507,240121,300014,309084,330073,330614,340083)

The problem is the acct_id's is a huge list of nearly 2500 accounts. And I just learnt that the maximum number of expressions in a list can only be 1000.

Is there any kind of a work around this. Did anyone else encounter this problem and how did u solve it.

Any help would be appreciated.
Thanks
Gublooo
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41508
    
  53
You can keep the account numbers in a separate table, and include that table in your join. It's much easier to maintain that way, too, than to change your query every time a new account needs to be added or deleted.


Ping & DNS - my free Android networking tools app
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Hi

Thanks for your reply. Actually these acct numbers are from a table. But this table is in a different database. And if I do a join on these two tables from different databases - the query takes a very long time to execute.

So I run the first query from a different database which returns the acct numbers and I store them in a comma seperated String and pass it to this query which performs the operations on a different database.

Like I said, i've tried joining the tables, but the response time is very slow. Generally if the query for 1000 accts takes 25 sec, but if I join the tables, it takes over 4 mins.

I thought of creating a temp table and storing the accts in that table first and then doing a join, but the DBA would not give permission for these temp tables.

Thanks
Gublooo
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
Since you are doing some processing in between (storing data as comma separated string), you can do the following:

Instead of storing all acct numbers in single string, add logic to your code to create new string for every 1000 acct numbers
Then add logic to create a dynamic query which would say
' acct_id in (string1) or (string2) or (string3) 'and so on

For example, you could use an ArrayList to store these strings and append each element in the ArrayList to the query separated by "or" clause for the number of elements in the ArrayList

Padma
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Yes, Padma's solution is great but i will add to it. From performance point of view create a substring, append the values which are comma seperated and then get the string out of the substring.
This will surely increase the performance.

What u feel padma?. Let me know if i am wrong

Regards
Makarand Parab
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41508
    
  53

I thought of creating a temp table and storing the accts in that table first and then doing a join, but the DBA would not give permission for these temp tables.

He would need to have very good reasons for this. There are a number of situations where temp tables make life much simpler, and reducing the complexity of joins is a prime example. Maybe you can talk to him again. You're both doing your jobs, after all.

Then add logic to create a dynamic query which would say
' acct_id in (string1) or (string2) or (string3) 'and so on

While this may work, I'm almost positive that using OR clauses will be quite bad for performance.
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Hi

Padma and Makarand - thanks for the solution. Right now i'm actually doing the way you have suggested. But it was my last resort. But like Dittmer pointed out - performance wise its not good.

Dittmer - As far as the DBA is concerned - I've never actually met the guy. I Communicate with him only thru emails. And they are so hesitant about making any changes to the database not even funny.

They want you to somehow do all the arrangements in your code but dont talk abt database changes. I know its weird.

To tell you something funny - we made a request for 2 new tables to be added for a project that is going to be released in December. Last week - we came across a situation in which it was decided to alter the tables and add an extra column. We requested the change and it was denied. They are like its late to request changes. Hello the release date is in December - but what do I know - I'm a contractor here and i'll be gone.

I guess if the DBA doesnt cooperate - I have no choice but to go with Padma's solution.

Thanks for all your inputs. Really appreciate it.
Thanks
Gublooo
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1125

Gublooo,
Oracle has several tools for improving the performance of slow-running queries. Are you aware of them? Have you tried them?

Oracle also have a Performance Tuing Guide which is part of their documentation and which you can access from:

http://tahiti.oracle.com

If the other database -- the one with the table holding the account numbers -- is on a separate machine, then the slowness could be due to network problems.

Of-course an IN list can be the result of a query, for example:

Maybe PL/SQL could be a viable option? Perhaps you could use a PL/SQL collection?

Good Luck,
Avi.
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Thanks Avi

Yes the other database is on a different machine in a different state. I think the slowness is due to network issues probably.

Initially the in list was part of a select query. But even that was very slow. Like for 1000 accounts if I pass them as a comma seperated list - it executes in 10 secs, but when I give it as a sub query



it takes almost 3 mins.

I did'nt get what u meant by using PL/SQL collection. How would that help me.
Thanks
Gubloo
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with SQL where IN clause