SQL Query Optimization Tips

SQL Query Optimization Tips

The most important part and parcel of a Database management system is the SQL. It will differ from product to product. Given below are some tips on how to write a well optimized query and avoid performance bottlenecks.

Primary verification of the SQL.

1) The SQL query will need to be verified by two parameters

1. The SQL returns the appropriate results.
2. The SQL adequately addresses the issues of optimization and efficiency.

SQL processing.

When we write a query and process the same, we are carrying out other overhead operations to finally fetch the appropriate result. A typical query undergoes the process of (a) Parsing (b) Actual execution (c) and Fetch. When we tune/ optimize the SQL query, we are in fact finetuning the performance of one of these aspects. The result is faster time to generate the result and hence optimization.

Parsing includes

- Verification of query syntax

- Verification of objects

Execution includes

- The process of actual reads, writes in order to achieve the desired result.

Fetch includes

- Retrieval of the result rows and final display (such as ordered).

A very good method to counter the parsing time is to make extensive and judicious mix of Procedures, packages, views, functions and so on. Most RDBMS optimizers work on the principle of reducing the query complexity by arriving at the optimized way of execution. With increase in query complexity, it is found that the optimizer too takes a longer time to process the query.

Certain tips to work towards an optimized query.

1) Practically it is always feasible to tune the query after initial verification of the validity and working of the query.

2) Ensure that the SQL statements are written in a uniform and identical manner throughout to aid re-use. The added advantage is the avoidance of re-parsing when the optimizer encounters a similar query.

3) When working with large tables, avoid the usage of SELECT * to increase efficiency. Make sure to use the necessary column names and query the table for these columns only.

1. E.g. IF there exists a table called EMPLOYEE, with around 36 fields, it is better to use the relevant columns in the SELECT clause instead of using SELECT *

SELECT emp_id, emp_name, emp_bonus

FROM employee

is faster than using

SELECT *

FROM employee

4) Make judicious usage of the GROUP BY and ORDER BY. Verify if we can have alternate solutions instead of using GROUP BY and ORDER BY.

5) Desist from making use of HAVING clause with the GROUP BY all the time. Use it only if it is unavoidable.

SELECT empname

FROM employee

WHERE empname = ‘Abhilash’

Is better than

SELECT empName

FROM employee

GROUP BY empname

HAVING empname = ‘Abhilash’

6) Let us compare the following two queries.

SELECT emp_ID, empname, salary

WHERE salary > 0

And

SELECT emp_ID, empname, salary

WHERE salary != 0

The query written as

SELECT emp_ID, empname, salary

WHERE salary > 0 is more optimized than the other and will return a low cost.

7) Usage of an appropriate join will be more advantages than using a sub-query due to parsing constraints.

8) Avoid queries with full table scan unless it is warranted for.

9) Judiciously use the Table JOINS, IN and EXISTS.

10) Using the IN in a subquery is faster than using other constructs

11) Whenever possible use a non-column expression. A non-column expression indicates having the FIELD COLUMN on one side of the equation/ operator and the values on the other side.

E.g

A condition to verify for the electric charge and its slab_rate can be written in the following form.

Using the expression

WHERE Elec_Charge < 750/(1 + slab_rate)

Will be always faster than using

WHERE Elec_Charge + (slab_rate * Elec_Charge) < 750

12) Using the IN with a subquery holds good while using EXISTS within the parent query works better.

13) Using IN within the parent query tends to be slow.

14) Using image/ objects within the table tends to slow down the query. Instead use the underlying file system to save these objects and refer to these objects by using a pointer stored in the database table.

15) Avoid having full table scans for very large tables.

16) Verify proper usage of the indexes in order to derive the best out of the database.

17) Using EXISTS instead of IN definitely works faster.

E.g.

SELECT title

FROM titles

WHERE EXISTS

(SELECT *

FROM publishers

WHERE pub_id = titles.pub_id

AND city LIKE ‘B%’)

is faster than using the query with the IN

SELECT title
FROM titles
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE city LIKE 'B%')

18) In cases when we encounter a NOT IN, try writing the same query using an OUTER JOIN. Definitely helps.

E.g.

SELECT Order_id from Order

WHERE Order_id NOT IN (SELECT Order_id from Sales)

Can be rewritten using

SELECT o.Order_id from Order o, sales s

WHERE o.Order_Id = s.Order_ID(+)

19) Avoid the usage of LIKE. Instead use the equality operation, which works faster.

Hope this holds good for you. Get back with your comments and suggestions. Thank you.

CMDN widgets - Photo Widgets

A useful and appreciable widget for cricket lovers to view their favorite crickets stars. C'mon. Download this widget and track your stars.

CMDN Live Scores Widget - CMDN.com

View live Cricket Scores from cmdn.com. A useful feature for Cricket Lovers.

HitWicket - An exclusive Cricket Alert addon

HitWicket :

An exclusive addon available in FireFox for Cricket lovers, developed by Jitto P Jose.

Features
- Shows realtime live cricket scores and alerts in your firefox browser
- Synchronized with CMDN to get you the live cricket scores

Rush and get your vital link to live cricket scores and see those popups appear to inform you at important juncture of your favourite cricket match.

You can find it at https://addons.mozilla.org/en-US/firefox/addon/10830
(You will need a free registration at https://addons.mozilla.org/en-US/firefox/users/register to avail of the download.)

Note : Please leave your reviews on Mozilla for this handy addon.

Good Bug Reporting

Good Bug Reporting

A typical software development process consists of
- Business Analysis team
- Engineering team
- QA team
- Management

Business analysis team
- Concerned with generation of business requirements.
- Are more aware of business aspects than technical aspects.

Engineering team
- Technically oriented
- They consider that the project is their “BABY”.
- Often runs into the QA team on minor misunderstandings.

QA team
- Test to break
- Communicates bugs to the Engineering team.
- Most often runs into confrontation with the Engineering team.
- Are the most misunderstood of the lot for their BREAKING work

Improving Software work products
- Basic objective of the software development process is to develop a quality product for the customer

How is it achieved?
- By developing and testing repeatedly until the desired objectives are met.

Dependencies
- Having a good cycle time between development and testing
- Proper synchronizing between the teams.
- Minimum discrepancies in report which translates down to efficient work cycle.

Bug Report
- The mainstay of the QA team.
- Communicates entire scenarios to the Engineering team for faster resolution and subsequent closure of the bug, hence the bug report must be GOOD and EASY TO UNDERSTAND.

Why Good Bug Report is needed?
- Effective communication on perceived problems/ issues to the Engineering team.
- A good bug report commands respect.
- An effective bug report ensures that nearly all the bugs are fixed/ resolved.

Why do we write bug reports?
- During the testing process, we encounter number of deviations.
- These deviations are put down and communicated to the developer.
- The developer tries to recreate the issue.
- If found, it is resolved/ fixed.

Ineffective report.
- On attempt to recreate, the issue does not crop up.
- The developer finds it difficult to understand and recreate.
- The bug details are ambiguous.

Effect
- The bugs are not fixed.
- What is the tester’s job?
- Report the bug in its true form.
- Put in elaborate details for understandability.
- Ensure that the bug is reproducible.

Good Bug Report – Features
# Your bug detail should have a unique bug ID.
- Easy for traceability
- Easy to automate , if required
- Uniquely identifies an issue to facilitate retrieval and action.

# Your bug should be reproducible
- A reproducible bug ensures resolution
- Every detail to be included in the steps to recreate
- Enables the developer to trace the steps and fix the bug accordingly.

# Be specific in reporting
- A bug detail should include details about a specific issue only.
- The detail should not combine and try to explain different scenarios
- Summary to be included within 100 characters and should clearly indicate what the bug is about.

Bug Report – Format
A typical bug report should contain
- Bug ID
- Reporter
- Product
- Version
- Component
- Platform
- Operating system
- Priority
- Severity
- Status
- Assign to
- URL
- Summary
- Description
- Steps to recreate
- Expected result
- Actual result
- Snapshots (if any)
- Attachments


Additional Tips
# Report the problem immediately
- Will ensure that you do not forget the steps

# Attempt to reproduce the bug atleast thrice before reporting
- Will ensure that the bug is clear, concise and easily understood by the developer.

# Test the bug across similar modules within the application.
- Chances are high to have the bug in other modules too.

# Good bug summary
- A typical summary is written in 100 characters, should comprise of only one sentence.
- Enables the reader to immediately get the gist of the bug.
- Facilitates manual search when the user wants to search for the bug.

# Do a self review before confirming the bug
- A self review will ensure that discrepancies are removed and you have the bug details put in clearly.
- Remove ambiguous sentence from the report.
- Keep off misleading words.

# Avoid rough, abusive language.
- Do not use the bug report to underestimate, criticize or attack the developer.
- It should command respect from the other teams.

# Do not submit proprietary code in the bug report.
- For proprietary code, create a sample to illustrate.

Bug Report – An Example

Bug ID : 101
Reporter : Abhilash Gopi
Product : School Management (SMgmt)
Version : 1.2.7
Component : TimeTable Creation (TIMETABLE_CREATION.ASP)
Platform : Desktop machine (PII)
Operating system: Windows 2K, Windows XP
Priority : High
Severity : High
Status : New
Assign to : Abu
URL : http://SMgmt/TimeTable_creation.asp
Summary : Creation process for timetable takes an unusually long time (as high as 2 hours).
Description :
Tested Date : Feb.14, 2008.
Pre-requisite : Ensure that there are data available for atleast one class before timetable generation can be scheduled.
When the user selects the option "Schedule >> Time Table Generation", it is found to take an unusally long time to generate the time table.

Steps to recreate :
1) Invoke the School management application in the browser.
2) Login with appropriate username/ password (admin/admin)
3) Go to Schedule >> Time Table Generation"
4) Note that the generation process takes around 2 hours and more to generate the timetable.

Expected result:
The timetable generation process takes a long time to complete.

Actual result:
Time table generation being an important part of the application should be completed in a fairly less time.

Snapshots (if any):
Please find attached the image snapshot for your reference.
Attachments

Web Application Scalability

Scalability for Web applications

What is scalability?

In simple terms, the server should be able to meet the increased demand for HITS per SECOND/ Transactions per Second over a period of time in increasing user ratio, without failure.

If the Hits per second decreases at some point of time, then it is indicative of decreased performance. Typically a decrease in scalability can be traced to database indexing problem, a full data cache or a saturated network connection.

Therefore it becomes necessary to test the disk usage, memory, or network components while stress testing.

What is perfect scalability?


The chart shown above is an example of perfect scalability.

With increased number of users (X axis), the Hits per Second parameter also keeps on rising proportionally. This indicates perfect tuning between various components of the application.

This trend will continue until we reach a point where further increase in user load will see decreased activity in terms of “hits per second” and “transactions”.

Happy New Year, 2009.

New Year, 2009

2008, the year ended, with economic slowdown
And to the ground, great economies, bogged down
To us, in a way, has affected our upward flight
With the stock market, plunging in its downward flight.



No matter, what the end of this year
To you all, I wish a HAPPY NEW YEAR
Hoping, that it erases all our pains
To bring us rewards and gains





Wish you all a very HAPPY and PROSPEROUS NEW YEAR, 2009.