Database Optimization Techniques

SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;

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.
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

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.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
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.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';
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

Page Life Cycle in ASP.NET

Page Life Cycle

Page Request

The page request occurs before the page life cycle begins. When a user requests the page, ASP.NET determines whether the page needs to be parsed and compiled (therefore beginning the life of a page), or whether a cached version of the page can be sent in response without running the page.

Start

In the start step, page properties such as Request and Response are set. At this stage, the page also determines whether the request is a postback or a new request and sets the IsPostBack property. Additionally, during the start step, the page's UICulture property is set.

Page Initialization

During page initialization, controls on the page are available and each control's UniqueID are generated but not their properties. Any themes are also applied to the page.

Developers have access to the Init, InitComplete and PreLoad methods in this stage. The methods are as follows:

• Init: This event is raised after all controls have been initialized and any skin settings have been applied. This event is used to read or initialize control properties.
• InitComplete: The Page object raises this event. This event is used for processing tasks that require completion of all initialization.
• PreLoad: Use this event if you need to perform processing on your page or control before the Load event. After the Page raises this event, it loads view state for itself and all controls, and then processes any postback data included with the Request instance.

Load

During load, if the current request is a postback, control properties are loaded with information recovered from view state and control state. The OnLoad event method is fired during this stage.

This is where you will want to set properties for all of the server controls on your page, request query strings, and establish database connections.

Validation

During validation, the Validate method of all validator controls is called, which sets the IsValid property of individual validator controls and of the page.

PostBack Event Handling

If the request is a postback, any event handlers are called. The event handling for server controls occurs during this stage.

Render

During rendering, view state is saved to the page and then the page calls on each control to contribute its rendered output to the OutputStream of the page's Response property. Render is not really an event. The HTML of the page and all controls are sent to the browser for rendering.

Unload

Unload is called when the page has been fully rendered, sent to the client, and is ready to be discarded. At this point, page properties such as Response and Request are unloaded and any cleanup is performed. The cleanup includes routines such as closing database connections and file streams, or, event logging and other tasks.

Conclusion

When a Web page is requested, the server creates objects associated with the page and all of its child controls objects and uses these to render the page to the browser. Once the final stage is complete, the web server destroys these objects, to free up resource to handle additional request.

Application Lifr Cycle

 
Step 1:- The user sends a request to IIS. IIS first checks which ISAPI extension can serve this request. Depending on file extension the request is processed. For instance if the page is an '.ASPX page' then it will be passed to 'aspnet_isapi.dll' for processing.

Step 2:- If this the first request to the website then a class called as 'ApplicationManager' creates an application domain where the website can run. As we all know application domain creates isolation between two web applications hosted on the same IIS. So in case there is issue in one app domain it does not affect the other app domain.

Step 3:- The newly created application domain creates hosting environment i.e. the 'HttpRuntime' object. Once the hosting environment is created necessary core ASP.NET objects like 'HttpContext' , 'HttpRequest' and 'HttpResponse' objects are created.

Step 4:- Once all the core ASP.NET objects are created 'HttpApplication' object is created to serve the request. In case you have a 'global.asax' file in your system then object of the 'global.asax' file will be created. Please note 'global.asax' file inherits from 'HttpApplication' class.
Note: The first time an ASP.NET page is attached to an application, a new instance of 'HttpApplication' is created. Said and done to maximize performance, 'HttpApplication' instances might be reused for multiple requests.

Step 5:- The 'HttpApplication' object is then assigned to the core ASP.NET objects to process the page.

Step 6:- 'HttpApplication' then starts processing the request by http module events , handlers and page events. It fires the MHPM event for request processing.

Joinings in Sql

SQL EQUI JOIN

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.
You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.

Syntax 

T agents.agent_name,customer.cust_name,  customer.cust_city  FROM agents,customer  

WHERE agents.working_area=customer.cust_city; 

SQL NON EQUI JOIN

The SQL NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions.

Syntax

SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area   FROM orders a,customer b   WHERE a.ord_amount BETWEEN b.opening_amt and b.opening_amt;

SQL INNER JOIN

 

The INNER JOIN will select all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

SELECT foods.item_name,foods.item_unit,   company.company_name,company.company_city   FROM foods   INNER JOIN company   ON foods.company_id =company.company_id;  

SQL NATURAL JOIN

The SQL NATURAL JOIN is a type of equi-join and is structured in such a way that, columns with same name of associate tables will appear once only.

Syntax

SELECT * FROM foods NATURAL JOIN company;

SQL CROSS JOIN

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.
If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause. 

SQL OUTER JOIN

The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

The subtypes of SQL OUTER JOIN

• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN

Syntax

SELECT  company.company_name,company.company_id,foods.company_id foods.item_name,foods.item_unit FROM company, foods WHERE company.company_id = foods.company_id(+);

SQL LEFT JOIN

The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company LEFT OUTER JOIN foods ON company.company_id = foods.company_id;

SQL RIGHT JOIN

The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table written after the JOIN clause.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company RIGHT OUTER JOIN foods ON company.company_id = foods.company_id;

 

SQL FULL OUTER JOIN

The FULL OUTER JOIN will return all rows, as long as there's matching data in one of the tables. It includes all the rows from both the participating tables and does not select either the LEFT or RIGHT table from the JOIN key word.
The FULL OUTER JOIN combines the results of both left and right outer joins. When no matching rows exist for rows on the left side of the JOIN key word, NULL values will be returned from the result set on the right. On the other hand , when no matching rows exist for rows on the right side of the JOIN key word, NULL values will be returned from the result set on the left.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company FULL OUTER JOIN foods

ON company.company_id = foods.company_id;

SQL SELF JOIN

A SELF JOIN is another type of join in sql which is used to join a table to itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

SELECT a.company_name,b.company_name,a.company_city FROM company a, company b

WHERE a.company_city=b.company_city AND a.company_name<>b.company_name;