Tips and Tricks to speed up an SQL [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Does the order of columns in a WHERE clause matter?

These are the basics SQL Function and Keywords.

Is there any tips or trick to speed up your SQL ?

For example; I have a query with a lot of keywords. (AND, GROUP BY, ORDER BY, IN, BETWEEN, LIKE... etc.)

Which Keyword should be on top in my query? How can i decide it?

Example;

Where NUMBER IN (156, 646)
AND DATE BETWEEN '01/01/2011' AND '01/02/2011'

OR

Where DA开发者_StackOverflow中文版TE BETWEEN '01/01/2011' AND '01/02/2011'
AND NUMBER IN (156, 646)

Which one is faster? Depends of what?

---

Don't use functions in the where clause. Because the query engine must execute the function for every single row.

---

There are no "tricks".

Given the competition between the database vendors about which one is "faster", any "trick" that is always true would be implemented in the database itself. (The tricks are implemented in the part of the database called "optimizer").

There are only things to be aware of, but they typically can't be reduced into:

• Use feature X
• Avoid feature Y
• Model like this
• Never model like that

Look at all the raging questions/discussions about indexes, index types, index strategies, clustering, single column keys, compound keys, referential integrity, access paths, joins, join mechanisms, storage engines, optimizer behaviour, datatypes, normalization, query transformations, denormalization, procedures, buffer cache, resultset cache, application cache, modeling, aggregation, functions, views, indexed views, set processing, procedural processing and the list goes on.

All of them was invented to attack a specific problem area. Variations on that problem make the "trick" more or less suitable. Very often the tricks have zero effect, and sometimes sometimes flat out horrible. Why? Because when we don't understand why something works, we are basically just throwing features at the problem until it goes away.

The key point here is that there is a reason why something makes a query go faster, and the understanding of what that something is, is crucial to the process of understanding why a different unrelated query is slow, and how to deal with it. And it is never a trick, nor magic.

We (humans) are lazy, and we want to be thrown that fish when what we really need is to learn how to catch it.

Now, what specific fish do YOU want to catch?

Edited for comments:
The placement of your predicates in the where clause makes no difference since the order in which they are processed is determined by the database. Some of the things which will affect that order (for your example) are :

• Whether or not the query can be rewritten against an indexed view
• What indexes are available that covers one or both of columns NUMBER and DATE and in what order they exist in that index
• The estimated selectivity of your predicates, which basically mean the estimated percentage of rows matched by your predicate. The lower % the more likely the optimizer is to use your index efficiently.
• The clustering factor (or whatever the name is in SQL Server) if SQL Server factors that into the query cost. This has to do with how the order of the index entries aligns with the physical order of the table rows. Better alignment = reduces cost for higher % of rows fetched via that index.

Now, if the only values you have in column NUMBER are 156, 646 and they are pretty much evenly spread, an index would be useless. A full scan would be a better alternative.
On the other hand, if those are unique order numbers (backed by a unique index), the optimizer will pick that index and drive the query from there. Similarily, if the rows having a DATE between the first and second of January 2011 make up a small enough % of the rows, an index leading with DATE will be considered.

Or if you include order by NUMBER, DATE another parameter comes into the equation; the cost of sorting. An index on (NUMBER, DATE) will now seem more attractive to the optimizer, because even though it might not be the most efficient way of aquiring the rows, the sorting (which is expensive) can be skipped.

Or, if your query included a join to another table (say customer) on customer_id and you also had a filter on customer.ssn, again the equation changes, because (since you did a good job with foreign keys and a backing index) you will now have a very efficient access path into your first table, without using the indexes in NUMBER or DATE. Unless you only have one customer and all of the 10 million orders where his...

---

Read about sargable queries (ones which can use the index vice ones which can't). Avoid correlated subqueries, functions in where clauses, cursors and while loops. Don't use select * especially if you have joins, never return more than the data you need.

Actually there are whole books written on performance tuning, get one and read it for the datbase you are using as the techniques vary from database to database.

---

Learn to use indexes properly.

http://Use-The-Index-Luke.com/