Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

(IN) PREDICATE in sql

 
lekha phijo
Greenhorn
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 724
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the performance will be not good.
Try use BETWEEN 1 AND 500
if it is possible.
 
lekha phijo
Greenhorn
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I cannot use between coz the product id are selected by the user and are random.
 
Paul Misoni
Greenhorn
Posts: 16
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33696
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
lekha phijo
Greenhorn
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33696
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic