SQL SELECT Tip 1
SQL SELECT only the columns needed, avoid using SELECT *. First, for each column that you do not need every SQL Server performs additional work to retrieve and return them to the client, and the second volume of data exchanged between the client and SQL Server increases unnecessary. Reference: SQL SELECT Statement page.
SQL SELECT Tip 2
SQL SELECT only the rows needed. The less rows retrieved, the faster the SQL query will run.
SQL SELECT Tip 3
Prune SQL SELECT lists. Every column that is SELECTed consumes resources for processing. There are several areas that can be examined to determine if column selection is really necessary.
Example:
WHERE (COL8 = ‘X’)
If a SQL SELECT contains a predicate where a column is equal to one value, that column should not have to be retrieved for each row, the value will always be ‘X’.
SQL TABLE Tip 4
When you create a new table always create a unique clustered index belong to it, possibly it is a numeric type.
SQL JOIN Tip 5
Use SQL JOIN instead of subqueries. As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:
SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post FROM authors a
Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.
SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
ON (a.id = p.author_id)
GROUP BY a.id
SQL OR Tip
The following example use the OR statement to get the result:
SELECT * FROM a, b WHERE a.p = b.q OR a.x = b.y;
The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster
SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;
SQL Clustered Index Tip
Keep your clustered index small. One thing you need to consider when determining where to put your clustered index is how big the key for that index will be. The problem here is that the key to the clustered index is also used as the key for every non-clustered index in the table. So if you have a large clustered index on a table with a decent number of rows, the size could blow out significantly. In the case where there is no clustered index on a table, this could be just as bad, because it will use the row pointer, which is 8 bytes per row.
SQL Optimizing Tip
- Avoid cursors. A bit of a no-brainer. Cursors are less performing because every FETCH statement executed is equivalent to another SQL SELECT Statement execution that returns a single row. The optimizer can’t optimize a CURSOR statement, instead optimizing the queries within each execution of the cursor loop, which is undesirable. Given that most CURSOR statements can be re-written using set logic, they should generally be avoided.
SQL Computer Columns Tip
- Use computed columns Computed columns are derived from other columns in a table. By creating and indexing a computed column, you can turn what would otherwise be a scan into a seek. For example, if you needed to calculate SalesPrice and you had a Quantity and UnitPrice column, multiplying them in the SQL inline would cause a table scan as it multiplied the two columns together for every single row. Create a computed column called SalesPrice, then index it, and the query optimiser will no longer need to retrieve the UnitPrice and Quantity data and do a calculation – it’s already done.
SQL Tip
- Pattern matching: there is always the nasty LIKE and NOT LIKE, but Postgres supports regular expressions too.
SQL Tip: ORDER BY
- ORDER BY USING: lets you specify an arbitrary sort function. Not quite sure how to use this yet? You can also do stuff like ORDER BY col1 + col2, which is cool.
SELECT col1, col2 FROM table_test
ORDER BY col1 + col2;
