wood burning stoves 2.0*
The moose likes JDBC and the fly likes Optimizing an SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimizing an SQL Query" Watch "Optimizing an SQL Query" New topic
Author

Optimizing an SQL Query

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi ,

Assume that i am having a SQL Query , for example "Select * from emp where Deptno=20"

Now please tell me , how can i optimize or fine tune this SQL Query , so that i can assume taht this is the best way to write an SQL Query .

Thanks . please saher your views on this .


Save India From Corruption - Anna Hazare.
Lino Larios
Greenhorn

Joined: Jun 27, 2008
Posts: 28
Hi , the query looks too simple but, may be if you need just some fields from the tables , filter those fields that are required , create a index for your table.



regards.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Ravi,
Adding an index on deptno would be best. Also, list out the columns you need returned instead of "select *". Assuming you don't need them all, this will save on network traffic.


[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
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

How many records are there in the emp table ? You probably don't need anything if that table is small (which would be the case for an employee table)


[My Blog]
All roads lead to JavaRanch
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

How many records are there in the emp table ? You probably don't need anything if that table is small (which would be the case for an employee table)


You were right , i did only posted a sample general query , but what if the queryas in case of our Application consisting of lakhs of Records , how can i optimize it ??

Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

Hi Ravi,

You were right , i did only posted a sample general query , but what if the queryas in case of our Application consisting of lakhs of Records , how can i optimize it ??


"Lakhs of Records" is an indian term. In English, it means "hundreds of thousands records".

-Abhishek
I came to this world on a Learner's License
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Thanks for clearing that up Abhishek.


but what if the queryas in case of our Application consisting of lakhs of Records , how can i optimize it ??

Typically, don't return 100,000s of records. The only applications that tend to need this amount of data are bulk loading tools. For applications that are intended for humans to use if your ResultSet is much bigger than a few hundred records your probably just returning unhelpful noise.

Some databases provide mechanisms to page ResultSets, though not all, so its usually a good idea to be a bit more specific in your query.

Something no one has mentioned yet (so I will) its usually a bad idea to guess how a query engine might perform a query, especially when there are tools to show you how it will behave. Run your query through your database's analyzer tool (whatever that is) and see what it says. For example, adding an index to the table if the table is small or the number of different values in the field are few, is probably pointless since a decent database query engine will correctly spot a table scan as much the same efficiency, and the overhead of maintaining an index will slow your application down not speed it up.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Paul for the explanation .

Run your query through your database's analyzer tool

Currently , i am having SQL Developer with me , is it possible to use Orqcles SQLDeveloper for this purpose ??

Thanks .
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

For Oracle you would generate an explain plan.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
If you are using Oracle, start with this guide, it covers basics:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/part4.htm#g996847
especially chapters 11, 12, 14, 16, 17 and 21
This guide is for Oralce 11, but there are similar guides for earlier versions,
search for "Performance tuning guide" in your database documentation.
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Paul ,

Please find the attached screen shot attached with thsi Thread

When i used execute Plan by selecting the Query inside , Oracle SQL Developer IDE , and excuted it using F6 (Excute Explain Plan) it showed me this data , i am not sure what does this mean


[Thumbnail for PostThis.jpeg]

Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

Your plan result shows that your query uses a FULL TABLE ACCESS. Which is obvious since There is no condition in your query !
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Christophe ,

The query has been modified ,but still i didn't understand the results that wer shown by SQLDeveloper .

Please see the screen shot attached , any help on this .


[Thumbnail for PostThis.jpeg]

David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

it says
1) you read the whole table - typically you want to avoid this in queries, but you haven't provided any search criteria
2) you sort the results.

There should also be a 'cost' involved IMO it should be kept to a single figure if possible but I've also had rarely run reporting queries that were off the scale.
Your example is still pretty simple, it gets more interesting when you have multiple joins, sub queries, matching, indices and the rest of the database features.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

The attached screenshot is from a MySQL database viewed through SquirrelSQL. I hid some names to protect the guilty ;)

The second two lines show two table joins and shows that it uses the primary key and only looks at a single row each time - regardless of how many records are in in the table.
The first line is for the string match, and it is forced to look at all 65 rows on the table (same as 'FULL' in your example). Not a huge problem but gets bad if the table grows.
If an index is created in that column the value drops to a single figure where it only access the exact rows required. Similar to a hash value, the search now only needs to look at rows that are a possible match and can guess these matches before actually needing to execute the query.


[Thumbnail for Screenshot.png]

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks , i cant get better explanation can you calrify one thing, when i did a operation involving joins , it gave me a cost Operation as 3967 , what does that mean exactly ??
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

typically it means it needs to look at 3000 rows to find a match and can't simplify that join.
To improve it, try to join on numeric fields rather than text, always join by primary keys, and always index foreign keys.
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Great , thanks very very much .

PJ Crump
Ranch Hand

Joined: Feb 06, 2002
Posts: 51
Also you would want to learn how to run an EXPLAIN on your query. Explains can aid you in determining how efficient the query is.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Optimizing an SQL Query