Can someone provide me the exact difference between Database Tuning and Database Query Optimization?
I have read the following links on Wikipedia:
http://en.wikipedia.org/wiki/Query_optimization
http://en.wikipedia.org/wiki/Database_tuning
As per my understanding it appears that database tuning is nothing else but it is database query optimization. I am still confused
What exactly is the difference between the two?
On
I understand that query optimization is related to the query itself, so for example, you can improve a poorly implemented join, remove fields that are not necessary and so on. Things tha would affect the query being analysed only.
Database tuning involves your server as a whole, since indexing, query load, even things related to IO like partitioning or splitting your database into different file groups in different disks for example.
On
Database tuning refers to tuning the database itself. Its access to storage and memory. Organization of various buffers and runtime parameters. As well as optimizing schemas using indexes, (de)normalization, storage parameters on schema objects and many more.
Query optimization is only concerned with a single statement at a time. It might involve restructuring the statement, creating indexes and materialized views, adding hints and updating statistics.
While there is an overlap between the two (when the database performs better a single statement is likely to be faster as well) but often they have contradicting aims. If you tune one statement at a time you are likely to create lots of indexes, but each index put a little extra load on the database for writes to that table, so the overall performance might actually suffer.
I would say that query optimization is a subset of database tuning. If you look at any general book on performance tuning you will generally only see one chapter related to tuning the queries. Other chapter have to do with partioning, indexing, load testing, locking and blocking, hardware, memory, schema, etc.