• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

How to make very slow query faster?

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The below query is very slow

select * from employee where emp_city = 'chennai' and emp_key NOT in (select emp_key from emp_details)

This query is slow when the data is very huge

Instead of 2 select queries,

1. how to create a query using join for the above
2. will join query will be much faster then 2 select queries.

Please advise
 
Master Rancher
Posts: 4208
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT e.*
FROM employee e OUTER JOIN emp_details ed ON e.emp_key = ed.emp_key
WHERE e.emp_city = 'chennai'
AND ed.emp-key IS NULL

Something along those lines.

On top of that, ensure you have suitable indexes.
And index on emp_city, for example, would prevent a full table scan of the employee table.

I assume you have indexes on emp_key on both tables.
 
Bartender
Posts: 20980
128
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most relational DBMS's have an EXPLAIN feature. Since it's not a standard part of SQL, it works differently, depending on which DBMS you're using (and in some cases can be really annoying to set up).

What Explain does is break down a query into its primitive operations and determine how much time was spent in each of the primitive operations. That allows you to tune the query.

Often, it's just a matter of adding some indexes, but sometimes re-ordering the query subexpressions can be a big boost, too.
 
What's that smell? Hey, sniff this tiny ad:
create, convert, edit or print DOC and DOCX in Java
https://products.aspose.com/words/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!