Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Performance Tuning SQL - How?

How does one performance tune a SQL Query?

  • What tricks/tools/concepts can be used to change the performance of a SQL Query?
  • How can the benefits be Quantified?
  • What does one need to be careful of?


What tricks/tools/concepts can be used to change the performance of a SQL Query?

  • Using Indexes? How do they work in practice?
  • Normalised vs Denormalised Data? What are the performance vs design/maintenance trade offs?
  • Pre-processed intermediate tables? Created with triggers or batch jobs?
  • Restructure the query to use Temp Tables, Sub Queries, etc?
  • Separate complex queries into multiples and UNION the results?
  • Anything else?


How can performance be Quantified?

  • Reads?
  • CPU Time?
  • "% Query Cost" when different versions run together?
  • Anything else?


What does one need to be careful of?

  • Time to generate Execution Plans? (Stored Procs vs Inline Queries)
  • Stored Procs being forced to recompile
  • Testing on small data sets (Do the queries scale linearly, or square law, etc?)
  • Results of previous runs being cached
  • Optimising "normal case", but harming "worst case"
  • What is "Parameter Sniffing"?
  • Anything else?


Note to moderators: This is a huge question, should I have split it up in to multiple questions?

Note To Responders: Because this is a huge question please reference other questions/answers/articles rather than writing lengthy explanations.

like image 295
MatBailie Avatar asked Jan 21 '09 00:01

MatBailie


2 Answers

Here some basic steps that need to follow:

  1. Define business requirements first
  2. SELECT fields instead of using SELECT *
  3. Avoid SELECT DISTINCT
  4. Create joins with INNER JOIN (not WHERE)
  5. Use WHERE instead of HAVING to define filters
  6. Proper indexing
like image 164
Ahmed Ansari Avatar answered Oct 20 '22 18:10

Ahmed Ansari


I really like the book "Professional SQL Server 2005 Performance Tuning" to answer this. It's Wiley/Wrox, and no, I'm not an author, heh. But it explains a lot of the things you ask for here, plus hardware issues.

But yes, this question is way, way beyond the scope of something that can be answered in a comment box like this one.

like image 41
Brent Ozar Avatar answered Oct 20 '22 18:10

Brent Ozar