QUERY OPTIMIZATION

If you have any queries please leave a message here
Your Message
×


Query Optimization is based on the cost of the query. The query can use different paths based on indexes, constraints, sorting methods etc. This method mainly uses the statistics like record size, number of records, number of records per block, number of blocks, table size, whether whole table fits in a block, organization of tables, uniqueness of column values, size of columns etc.

EXAMPLE :

Considered the following tables :
Student (enrolment, s_name, s_age, p_code)
Programme (p_code, p_name,)

Suppose we have a query to retrieve the students with age 25 and studying in programme MCA. We can write this query in two different ways.

SELECT st.*
FROM Student AS st, Programme AS prog
WHERE st.p_code=prog.p_code AND st.s_age=25 AND prog.p_code='MCA';
SELECT st.*
FROM (SELECT * FROM Student WHERE s_age=25) AS st, (SELECT * FROM Programme WHERE p_code='MCA') AS prog
WHERE st.p_code=prog.p_code;

Here both the queries will return same result. But when we observe them closely we can see that first query will join the two tables first and then applies the filters. That means, it traverses whole table to join, hence the number of records involved is more. But the second query, applies the filters on each table first. This reduces the number of records on each table, hence the cost in this case is comparatively less.






TUNING OF SQL

SQL Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to SQL query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

EXAMPLES



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us