• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL subquery performs too slowly

 
Ranch Hand
Posts: 433
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have the following scenario: I need to find all customers who bought at least two certain books. As an example I want to find all users who bought "Sql Book" and "Java Book".
For that I've got the following query:



This works fine and delivers the expected results. But: it's too slowly. I have a quite huge amount of data, so that this query needs more than 200 seconds.
Could anybody help me out, to rewrite this query so that it performs faster?

PS: I have no influence on the database itself, so I can't set any index or modify the database in any other way.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Joachim,
What database are you using? In particular, is it one that supports the INTERSECTION keyword? If so, it would likely be faster to do two separate queries and use that operator.

Also, are you calling this query via JDBC. If so, you could just run the two queries (matching SQL book or matching Java book) separately and do the intersection yourself in Java.

The best way to find out where the time is going is really to run a database "explain" command on your query. If it is doing a nested loop, my suggestions will help. If it is spending the time doing a table scan (which sounds more likely from your description), the only thing that will speed things up is an index on boughtBooks. If you can't change the database, you'll need to either talk to the people who can change it or the people who gave you the performance requirement.
 
Joachim Rohde
Ranch Hand
Posts: 433
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your answer Jeanne. The database is a MySql database which does not seem to support the intersect keyword.

I executed the query directly against the database (with the MySql Monitor), so no JDBC or anything.

I indexed the field on my local database and the query finished within less than one second. I guess that should be good enough to demonstrate the responsible person that an index is needed ;-)

Thanks again.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This will perform better for you.



The reason why is your query:



Requires a full table scan for each row (the inner query must return a result set that reads all rows in the table before the outer query's where clause can be evaluated)... unless boughtBooks is an indexed column... which is not very likely.

[ November 14, 2008: Message edited by: Paul Campbell ]
[ November 14, 2008: Message edited by: Paul Campbell ]
 
Joachim Rohde
Ranch Hand
Posts: 433
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your answer Paul. Your query is ca. 10% faster. In the meanwhile I talked to the responsible person and I was told it should not be a problem to set an index.
I've got a question though: what exactly does "select 1" means in your query?
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Joachim Rohde:
I've got a question though: what exactly does "select 1" means in your query?


Don't look at any of the columns when returning rather than anything in the table. See this page for detailst
 
Joachim Rohde
Ranch Hand
Posts: 433
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the explanation.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Joachim Rohde:
Thanks for your answer Paul. Your query is ca. 10% faster. In the meanwhile I talked to the responsible person and I was told it should not be a problem to set an index.
I've got a question though: what exactly does "select 1" means in your query?



Hi Joachim,

Well... there are two answers here. The short answer is the sub-query returns a value of 1 for each row that is true... you could really use anything you like... the general practice is to "Select 1" with an Exists clause.

The more important concept is that the EXISTS clause is a boolean True/False for the sub-query (you can also use NOT EXISTS in a sub-query). The sub-query will evaluate to TRUE if any rows are returned for the sub-query (and TRUE if no rows are returned for NOT EXISTS).

Using a correlated sub-query (a query that has reference to the outer query) is usually faster when you use it in this manner than a non-correlated sub-query. A non-correlated sub-query must also perform a sort when the comparison operator is IN, NOT IN, = ANY, <> ANY, = ALL, or <> ALL.

I can clarify if I rushed through this too quickly... its a nice diversion from what I'm working on right now.
[ November 15, 2008: Message edited by: Paul Campbell ]
 
Joachim Rohde
Ranch Hand
Posts: 433
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Paul Campbell:
I can clarify if I rushed through this too quickly... its a nice diversion from what I'm working on right now.



No, your explanation was perfectly clear. So I'm sorry I didn't distract you any further from your work ;-)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic