aspose file tools*
The moose likes JDBC and the fly likes (IN) PREDICATE in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "(IN) PREDICATE in sql" Watch "(IN) PREDICATE in sql" New topic
Author

(IN) PREDICATE in sql

lekha phijo
Greenhorn

Joined: Oct 09, 2003
Posts: 29
I have a SQL statement which uses in predicate .
In the in clause I am having around 500 products list

like:

select * fro Product where prodict_id in (1,2,3,4....)

The list of product_id are selected from list on jsp page.

Can you suggest the performance issues related to using a query with in predicate and 500 items in the in clause,

Can you suggest an alternative
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
I think the performance will be not good.
Try use BETWEEN 1 AND 500
if it is possible.


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
lekha phijo
Greenhorn

Joined: Oct 09, 2003
Posts: 29
I cannot use between coz the product id are selected by the user and are random.
Paul Misoni
Greenhorn

Joined: Jan 02, 2001
Posts: 16
Rather than include all of those values explicitly within the SQL statement, you may wish, instead, to place them in their own table and join to it.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29274
    
140

Lekha,
First thing is to make sure you have an index on the product_id column.

As for the in clause, try experimenting with using batches (of say 50) and running the query multiple times. In my experience, this is often faster. One reason is that the statement only needs to be prepared once rather than each time (since there won't always be the same number of ids.) Another reason is that large queries sometimes take up too much memory to prepare efficiently.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
lekha phijo
Greenhorn

Joined: Oct 09, 2003
Posts: 29
Jeanne thats sounds good.
If I hav around 9000 products and I fire the query from front end in batches of 50 , The no of times the query will be executed will be 180 times .Will this hamper performance.

Also will it be a good idea to have a procedure which creates a temporary table with all product list and the do a join to fetch the data.

Thanks in advance
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
no, i think you will get the result to show the user everytime you query out, right. so it means a better performance. you are showing the results 180 times. and if you do batch of 9000 once then your user would have to wait a long, isn't it, and then after getting result browse for desired result in 9000 records.

couldn't get your second idea about temporary table and join and stuff.

thanks.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29274
    
140

Originally posted by lekha phijo:
Jeanne thats sounds good.
If I hav around 9000 products and I fire the query from front end in batches of 50 , The no of times the query will be executed will be 180 times .Will this hamper performance.

In my experience, it is faster to do the batching. However, it depends on various factors such as your network speed and the amount of memory available. I would suggest trying it both ways and seeing which one is faster. Of course as Adeel pointed out, you may not need all 9000 records in the first shot.

Also will it be a good idea to have a procedure which creates a temporary table with all product list and the do a join to fetch the data.

I don't like this approach. It introduces a join (which is slower), without having a really good reason.
lekha phijo
Greenhorn

Joined: Oct 09, 2003
Posts: 29
Thanks.
Processing in batch is faster.
Ofcourse if memory and network traffic taken care of
Thanks again
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: (IN) PREDICATE in sql
 
Similar Threads
Get the first x number of nodes in a set
sql server to oracle(sql plus)
combining LEFT OUTER JOIN and WHERE
English Grammar Question
How to update a data in database from jsp