I know how to write SQL queries and can get the results I need. But sometimes my queries are slow and I don't fully understand why.
What are some good resources for learning to write efficiant queries and to optimze the quries I've allready written?
A:
use indexes where tables can use one, and also run Database Tuning Advisor on a query if you have SQL Server.
warsong
2010-09-17 13:09:18
+1
A:
An excellent book about the subject: Inside Microsoft SQL Server 2005: Query Tuning and Optimization
Giorgi
2010-09-17 13:09:49
Each major database vendor has some equivalent of this book, and yes performance tuning is a complex subject best learned from one of these big fat books. ANd performance tuning is very db specific what helps in SQl Server might harm in ORacle.
HLGEM
2010-09-17 13:28:57
Agree with HLGEM, I would hesitate to recommend a platform and version-specific book like this when no database platform has been specified.
RedFilter
2010-09-17 13:30:38
+2
A:
It's been awhile since I had to use it, but I found SQL Tuning to be quite useful at a job I had a few years ago.
Bill the Lizard
2010-09-17 13:12:56
+5
A:
I would say the main things are:
- understand the set-based nature of SQL by reading platform-neutral books such as Celko's; this will help you avoid making newbie mistakes like using cursors (or other iterative approaches) where they are not needed
- learn the basics of normalization, and when to denormalize; efficient queries start with well-organized data
- understand where indexes can be helpful and where they can't; e.g., grasp how cardinality of your data affects index efficiency, and what SARGable queries are; when to use multi-column indexes
- learn how to use EXPLAIN PLAN or its equivalent for your platform; by observing how your query is being compiled and the resources it is consuming, you will better understand the bottlenecks
- learn platform-specific methods such as indexed/materialzied views, full text indexing, methods for paging and dealing with hierarchical data
RedFilter
2010-09-17 13:26:46
To your point on indexes, i'd add learning what a covering index is. Functional and filtered/partial indexes might also be useful to know about, although they're rather obscure, and not widely supported.
Tom Anderson
2010-09-17 16:26:03
A:
For MySQL specific information, chapter 7 of the reference manual is all about optimization. § 7.3, in particular, covers MySQL statement optimization. The PostgreSQL wiki similarly documents optimizing specifically for that RDBMS. Microsoft's "How To: Optimize SQL Queries" is retired; I'm not certain if there's a more recent document. Oracle has a couple documents on query optimization for the various versions.
outis
2010-09-17 13:27:28