• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Optimizing an SQL Query

 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 .
 
Lino Larios
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34686
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 40
Chrome Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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".
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For Oracle you would generate an explain plan.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
PostThis.jpeg
[Thumbnail for PostThis.jpeg]
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 .
PostThis.jpeg
[Thumbnail for PostThis.jpeg]
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Screenshot.png
[Thumbnail for Screenshot.png]
 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great , thanks very very much .

 
PJ Crump
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic