top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

can anyone tell me about SQL tuning tips?

+1 vote
76 views
can anyone tell me about SQL tuning tips?
posted Nov 7, 2014 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

  1. 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

  1. 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

  1. Pattern matching: there is always the nasty LIKE and NOT LIKE, but Postgres supports regular expressions too.

SQL Tip: ORDER BY

  1. 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;

answer Nov 7, 2014 by Arun Gowda
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...