Optimizing an SQL Query

[Ravi Kiran Va]

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]

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]

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é]

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]

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]

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]

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]

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]

For Oracle you would generate an explain plan.

[Ireneusz Kordal]

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]

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

[Christophe Verré]

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]

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 .

[David O'Meara]

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]

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.

[Ravi Kiran Va]

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]

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]

Great , thanks very very much .

[PJ Crump]

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.