13

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 efficient queries and to optimize the queries I've already written?

SagarPPanchal
  • 9,839
  • 6
  • 34
  • 62
epotter
  • 7,631
  • 7
  • 63
  • 88
  • 1
    What's argumentative about this? – Bill the Lizard Sep 17 '10 at 13:13
  • 2
    Shouldn't this be Community wiki, as questions like "Which book is recomment for C?" – fuz Sep 17 '10 at 13:23
  • are you mainly interested in SQLServer query optimisation, or optimisation for other DBMSs, too? The techniques tend to vary between dialects of SQL. –  Sep 17 '10 at 14:10
  • I was looking for advice that is platfrom agnostic. I realize now that there are many optimizations that are platform specific, and I may need to look into the one for my specific platform. But at first, I just wanted learn how I can write well formed queries. – epotter Sep 17 '10 at 15:22

3 Answers3

22

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 the cardinality of your data affects index efficiency, what SARGable queries are, and 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/materialized views, full text indexing, and methods for paging and dealing with hierarchical data.
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • +1 that covers it fairly well. – KM. Sep 17 '10 at 13:31
  • 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 Sep 17 '10 at 16:26
1

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
  • 75,655
  • 22
  • 151
  • 221
0

use indexes where tables can use one, and also run Database Tuning Advisor on a query if you have SQL Server.

warsong
  • 1,008
  • 2
  • 12
  • 21