Query processing and Optimization

Hi! my dear friends. So we again meet with another post. Today we are going to discuss about very interesting topic. That is about query processing and optimization. Databases are all about queries. So many queries work on a single database. Query processing and optimization take important role in database management systems. This is also a very easy topic. We will learn it together. So let’s start the lesson.

Today the sub topics we are going to discuss is what is query optimization. What is query processing. The steps in query processing. What are query optimization strategies. What are query processing steps. You can learn all these things if you stay with us. Keep reading.

To give a database result firstly query is processing. In query processing we are talking how query processing happen in the databases. Before queries are executing they are being optimizing. In optimizing we are going to discuss about optimizing techniques. The aim of query optimizing is to speed up the query processing. Because the queries are not going to execute the same way we write the queries. If so database management system will take long time to process and users have to wait long time to retrieve results. Firstly we will discuss about query processing. Then learn how a query is processing. It makes us easy to understand the optimization process.

What is Query Processing

Query processing starts with SQL query. Firstly raw, user query will enter to the following events. Those are scanning, parsing and validating. The scanners will identifies the language tokens. Examples for language tokens are SQL keywords, attributes and relations names. After scanner identifies the SQL tokens. Then parser will do his work. That is the query is correct. Query is written according to the SQL rules. After that validation is happened. In validation what happen is validating the query. There it will check the given attributes and table names are valid/correct names. After completing this step a query tree will created. Query tree is a graph data structure.

After query tree was built it will go through query optimizer. When it goes through the optimizer it will chose the correct execution strategy. The queries have so many execution strategies to execute a query and retrieve answers. For the query processing we need a strategy. Query optimizer will choose a suitable one for the query. This will known as query optimization. Under query optimization we will learn those strategies.

Then after that it will create the execution plan. According to the new execution plan a new query tree will be drawn. After query tree build database management system can’t execute the query tree. Since it is a data structure not a something written in SQL. There fore after creating the execution it will go to the code generator. From code generator it will create the corresponding SQL query statements for the new execution plan.

Once new SQL code generated it will go through runtime query processor. From this users can get the results. It will give the SQL results. If run time error occurs it will show the error. These are the things happen in query processing. These are the things you need to know about query processing. Then we will look at query optimization.

What is Query Optimization

You already know what happens in the query optimization. As I told earlier finding the right execution plan is the aim of optimizing. Then it could raise the efficiency. There are two types of optimization strategies we use. They are the heuristic query optimization and cost – based optimization. Most important for our studies is the heuristic optimization strategy. There is some rules for heuristic optimization. We will learn them next. There is main heuristic rule also. That is apply select and project rules first before apply join or other binary operations. The effectiveness of doing select and project operations first you will learn when we do the questions later. Let’s look at the other rules.

These are the heuristic rules. Shall we look at an example and discussed more. Because I hope by looking at a question it will help you to understand the heuristic rules. From this topic you might get a question to draw the query tree with applying heuristic rules. There what you have to do is first do the project and select then do the join operations. For these things you need to learn few Boolean algebra. Because project, select these things are Boolean algebra. First let’s learn them first.

Boolean algebra in SQL

Project means the getting SQL results from a table vertically. In simply it is cutting table in a vertical way. Retrieving attributes as a result is projecting a table. That means a vertical cut. Not a physical cut. In logically. In Boolean algebra project is representing from letter pi. π. Select in SQL operation do the project task. From SELECT keyword what we get is table attributes as results.

Boolean select operation do the horizontal cut. That means filtering the results. In SQL what exactly do by the WHERE clause. Join is the same thing. Joining the two or more tables together.

How to draw a query tree using heuristic rules

First you have to draw the query tree with starting the select and project options. Then use join operations. You know the reason for doing this. We will look at exam questions later. In exam questions you might get a question to draw heuristic query tree as well as the normal query tree. Then what you have to do is in heuristic tree you have to draw the query tree with using select and project operations first. Then for other tree you can use join operation first.

Conclusion

As always we have come to the end of the post. Today we learn about query processing and optimization. We will discuss question with another post. If you have doubts please write us. Please share this post with your friends then they also can learn. Please check our other database posts as well as. You can find them in the related posts section below. What about a suggestion. The suggestion is database triggers. See you in a next topic. Good bye then.