Chaitanya Lakshmi
  • About Me
  • Job Duties
  • Knowledge Blog
  • Applications
  • Contact Us
    • Linked In
    • Facebook
  • About Me
  • Job Duties
  • Knowledge Blog
  • Applications
  • Contact Us
    • Linked In
    • Facebook

Different query optimization techniques?

2/17/2012

0 Comments

 
  1. The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
  2. HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. 
  3. Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. 
  4. Use operator EXISTS, IN and table joins appropriately in your query.                                                                     a) Usually IN has the slowest performance.
    b) IN is efficient when most of the filter criteria is in the sub-query.
    c) EXISTS is efficient when most of the filter criteria is in the main query
  5. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
  6. Try to use UNION ALL in place of UNION. 
  7. Be careful while using conditions in WHERE clause. 
  8. Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause. 
  9. To store large binary objects, first place them in the file system and add the file path in the database.
  10. To write queries which provide efficient performance follow the general SQL standard rules.
        a) Use single case for all SQL verbs
        b) Begin all SQL verbs on a new line
        c) Separate all words with a single space
        d) Right or left aligning verbs within the initial SQL verb

Refer: http://beginner-sql-tutorial.com/sql-query-tuning.htm
0 Comments



Leave a Reply.

    Archives

    April 2012
    March 2012
    February 2012

    Categor

    All
    Ajax
    Api
    Cross Site Scripting
    Css
    Curl
    Design Patterns
    Drupal
    Exception Handling
    Htaccess
    Html
    Javascript
    Jquery
    Json
    Linux
    Mysql
    Oops
    Php
    Regular Expressions
    Web 2.0
    Webservices
    Wordpress
    Xhtml

    RSS Feed

Powered by Create your own unique website with customizable templates.