Contact Us
  
 
 
    SearchSearch   RegisterRegister   Log inLog in 

Optimization of SQL queries for better performance

Optimization of SQL queries for better performance

 
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems
View previous topic :: View next topic  
Author Message
admin
Site Admin


Joined: 24 Aug 2006
Posts: 30

PostPosted: Sat Jun 28, 2008 6:03 am    Post subject: Optimization of SQL queries for better performance Reply with quote






Optimization comes in mind when our database or application get slow. Most of people never care about optimization of SQL query. They never thing about future of database can become bigger than they started before.
Optimization of SQL task always difficult to everyone in database. This happens due to inexperience of programming and database handling. Priority of everyone is to complete their basic work first insert and fetch data records on application. When database grow, it gets slower and slower.

We can easily optimize our queries before writing, just we have to thing about future, how many users can grow and access at simultaneously.

Let’s start optimizing tips

1. Fetch less number of records by limiting if you have records more than 2000

// mysql
Code:
select field from tableName limit 20;


// Oracle
Code:
select field from tableName where rownum<=20;


// Microsoft SQL
Code:
select top 20 field from tableName;


2. Limits columns while fetching records

Instead of using * in select use column name and get only column which you really needs

Code:
SELECT column1, column2 FROM statemaster s


Loading extra data will slow down your query


3. Write simple query

Don’t need like this query
Code:
select * from (SELECT column1, column2 FROM statemaster) s


4. Use indexed key in table
Indexing is storing address of SQL data records insert time. It takes address of data from indexed key, and fetches records. It is very usefully when having lots of records.

5. Assign Proper data type to column

Suppose we have a column in our table, where save only phone numbers. We know that these phone numbers is in integer. If we store as string, it will type cast to integer or type cast to string. Result in consume time for processing.

6. Avoid SQL calculation or function
Function like avg, sum, Date_format should be avoided, if you have option. If you can write these functions in your business logic code, is good option. Execute only important query data on SQL server. More logic, consume more time in query.

7. Join optimization in SQL

A JOIN Query in SQL can run slow.
Always think before joining table.

Suppose you need only those rows which are found in both table. If you use left or right join, result in slow down query.

Why? See this

This can be shown by example
Table 1 CityMaster

Code:
SELECT iCityID, iStateID, sCityName FROM citymaster c


Code:

+---------+----------+------------+
| iCityID | iStateID | sCityName  |
+---------+----------+------------+
|       3 |        5 | Chandigarh |
|       4 |        5 | Hissar     |
|       5 |        8 | Jabalpur   |
|       6 |        6 | jammu      |
|       8 |        0 | New York   |
+---------+----------+------------+


Table 2 StateMaster

Code:
SELECT iStateID, sStateName FROM statemaster s



Code:

+----------+------------------+
| iStateID | sStateName       |
+----------+------------------+
|        2 | Haryana          |
|        7 | Himachal Pradesh |
|       10 | Indiana          |
|        6 | Jammu & Kashmir  |
|        3 | New Delhi        |
|        5 | Punjab           |
|        8 | Uttar Pradesh    |
|        9 | west bengal      |
+----------+------------------+


Left join query e.g

Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c left join statemaster s on s.iStateID=c.iStateID


output

Code:

+---------+----------+------------+-----------------+
| iCityID | iStateID | sCityName  | sStateName      |
+---------+----------+------------+-----------------+
|       3 |        5 | Chandigarh | Punjab          |
|       4 |        5 | Hissar     | Punjab          |
|       5 |        8 | Jabalpur   | Uttar Pradesh   |
|       6 |        6 | jammu      | Jammu & Kashmir |
|       8 |        0 | New York   | NULL            |
+---------+----------+------------+-----------------+


But desired output we want only matched rows, here we got one row extra, by left join. We have to do filter again it by modifying in our query.


Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c
 left join statemaster s on s.iStateID=c.iStateID where s.sStateName is not null


Desired Output

Code:

+---------+----------+------------+-----------------+
| iCityID | iStateID | sCityName  | sStateName      |
+---------+----------+------------+-----------------+
|       3 |        5 | Chandigarh | Punjab          |
|       4 |        5 | Hissar     | Punjab          |
|       5 |        8 | Jabalpur   | Uttar Pradesh   |
|       6 |        6 | jammu      | Jammu & Kashmir |
+---------+----------+------------+-----------------+



Instead of Using left join we should had to use inner join here.

Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c inner join statemaster s on s.iStateID=c.iStateID


Desired Output

Code:

+---------+----------+------------+-----------------+
| iCityID | iStateID | sCityName  | sStateName      |
+---------+----------+------------+-----------------+
|       3 |        5 | Chandigarh | Punjab          |
|       4 |        5 | Hissar     | Punjab          |
|       5 |        8 | Jabalpur   | Uttar Pradesh   |
|       6 |        6 | jammu      | Jammu & Kashmir |
+---------+----------+------------+-----------------+
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems All times are GMT
Page 1 of 1

 
Jump to:  
  Quick Post a Reply Topic
     
  Name (required)  
     
  Email Address (will not be published) (required)  
     
  Subject of topic (required)  
     
   
     
   
     
  Advertisement  
     
 
     


  More Links  
 
JSP Introduction
JSP Setup
Simple JSP Example
JSP Variable
Array Declaration in JSP
JSP Forms and User Input
Form Validation
JSP Cookies
JSP Session Object
Loop, content collection, iterator, conditional check in JSP
JSP Application Object
JSP Include
JSP Response Object
JSP Request Object
JSP Error Handling
JSP File Handling Object
JSP Action Tag
JSP Implicit Objects
JSP Declaration
JSP Directive
JDBC Introduction
JDBC Driver
JDBC Connection
JDBC Prepared/Statement
JDBC ResultSet
JDBC Close Connection object
JDBC Insert
JDBC Display Records
JDBC Update
JDBC Delete
JDBC Sorting
JDBC Query
JDBC Exception
JDBC Add Batch
JDBC Transaction/RollBack