Hello guys, a good understanding of how the index works and how to use them to improve your SQL query performance is very important while working in a database and SQL and that's why you will find many questions based upon indexes on Programming Job interviews. One of such frequently asked SQL question is the real difference between table scan, index scan, an index seek? which one is faster and why? How does the database chooses which scan or seek to use? and How you can optimize the performance of your SQL SELECT queries by using this knowledge. In general, there are only two ways in which your query engine retrieves the data, using a table scan or by using an index.
Which method is used for your particular query depends upon what indexes are available in that table, what columns you are requesting in your query, the kind of joins you are doing, and the size of your tables.
If you have a clear understanding of how the index works and how SQL engine retrieves data from the disk then you can quickly identify performance problems and solve them. That's where most of the SQL developers, especially the Java application developer who write queries and design database lack.
You might have heard nasty things about table scans but in truth, it's the fastest way to retrieve data especially if your table is quite small. It starts being bad when your table starts growing. You can imagine doing a full table scan in a table with 4 million rows and a full table scan in a table with just 100 rows.
In a small table, a query engine can load all data in just one shot but in a large table, it's not possible, which means more IO and more time to process those data.
Normally, a full table scan is used when your query doesn't have a WHERE clause, I mean, you want more or less every record from a table like the following query will use a full table scan:
SELECT * from Employee;
Btw, If your query is taking too long in a large table then most likely it using either table scan or index scan. You can see that by enabling an execution plan like by doing Ctrl + A in Microsoft SQL Server Management Studio. If you are new to SQL Server, I highly recommend you to join the Microsoft SQL for Beginners to learn T-SQL and SQL Server Management Studio better.
Once the right index is chosen, the SQL Query processor or engine navigates the tree structure to the point of data that matches your criteria and again extracts only the records it needs
Which method is used for your particular query depends upon what indexes are available in that table, what columns you are requesting in your query, the kind of joins you are doing, and the size of your tables.
If you have a clear understanding of how the index works and how SQL engine retrieves data from the disk then you can quickly identify performance problems and solve them. That's where most of the SQL developers, especially the Java application developer who write queries and design database lack.
Difference between table scan, index scan, and index seek
In this article, we'll go through each three i.e. table scan, index scan, and index seek and try to understand how database process a particular query hence a basic understanding of database, SQL and index are required.1. What is a Table Scan in a database?
A table scan is a pretty straightforward process. When your query engine performs a table scan it starts from the physical beginning of the table and goes through every row in the table. If a row matches the criterion then it includes that into the result set.You might have heard nasty things about table scans but in truth, it's the fastest way to retrieve data especially if your table is quite small. It starts being bad when your table starts growing. You can imagine doing a full table scan in a table with 4 million rows and a full table scan in a table with just 100 rows.
In a small table, a query engine can load all data in just one shot but in a large table, it's not possible, which means more IO and more time to process those data.
Normally, a full table scan is used when your query doesn't have a WHERE clause, I mean, you want more or less every record from a table like the following query will use a full table scan:
SELECT * from Employee;
Btw, If your query is taking too long in a large table then most likely it using either table scan or index scan. You can see that by enabling an execution plan like by doing Ctrl + A in Microsoft SQL Server Management Studio. If you are new to SQL Server, I highly recommend you to join the Microsoft SQL for Beginners to learn T-SQL and SQL Server Management Studio better.
2. What is the Index Scan in a database?
If your table has a clustered index and you are firing a query which needs all or most of the rows i.e. query without WHERE or HAVING clause, then it uses an index scan. It works similar to the table scan, during the query optimization process, the query optimizer takes a look at the available index and chooses the best one, based on information provided in your joins and where clause, along with the statistical information database keeps.Once the right index is chosen, the SQL Query processor or engine navigates the tree structure to the point of data that matches your criteria and again extracts only the records it needs
The main difference between a full table scan and an index scan is that because data is sorted in the index tree, the query engine knows when it has reached the end of the current it is looking for. It can then send the query, or move on to the next range of data as necessary.
For example, the following query, same as above will use Index scan if you have a clustered index in your table:
SELECT * From Employee;
This is slightly faster than the table scan but considerably slower than an index seek which we'll see in the next section.
This happens when you specify a condition in WHERE clause like searching an employee by id or name if you have a respective index.
For example, the following query will use an index seek, you can also confirm that by checking the execution plan of this query when you run this on SQL server:
SELECT * from Employee where EmployeeId=3;
In this case, the Query Optimizer can use an index to directly go to the third employee and retrieve the data. If you look at the execution plan shown below, you can see that it uses an index seek using the index created on EmployeeId.
If you want to learn more about how the SQL engine process requests I suggest you go through the SQL Tuning course by Amarnath Reddy from Udemy/ No you will not only learn more about SQL performance tuning but also learn SQL performance tuning tools which can help you with the task. I highly recommend this course to anyone who wants to improve their SQL and Database performance skills
1) A table scan and an index scan are used when you need to retrieve all data like 90% to 100% while index seek is used when you need to retrieve data based upon some conditions like 10% of data.
2) If your query doesn't have WHERE clause and your table doesn't have a clustered index then a full table scan is used, if it does have a clustered index then index scan is used.
3) index scan is faster than a table scan because they look at sorted data and query optimizer know when to stop and look for another range.
4) index seek is the fastest way to retrieve data and it comes into picture when your search criterion is very specific. Normally, when you have WHERE clause in your query and you are using a column which also has an index, then index seek is used to retrieve data as shown in the following query:
select * from Employee where Id= 3;
You can confirm that by actually looking at the execution plan for your query.
For example, the following query, same as above will use Index scan if you have a clustered index in your table:
SELECT * From Employee;
This is slightly faster than the table scan but considerably slower than an index seek which we'll see in the next section.
3. What is Index Seek in SQL?
When your search criterion matches an index well enough that the index can navigate directly to a particular point in your data, that's called an index seek. It is the fastest way to retrieve data in a database. The index seeks are also a great sign that your indexes are being properly used.This happens when you specify a condition in WHERE clause like searching an employee by id or name if you have a respective index.
For example, the following query will use an index seek, you can also confirm that by checking the execution plan of this query when you run this on SQL server:
SELECT * from Employee where EmployeeId=3;
In this case, the Query Optimizer can use an index to directly go to the third employee and retrieve the data. If you look at the execution plan shown below, you can see that it uses an index seek using the index created on EmployeeId.
If you want to learn more about how the SQL engine process requests I suggest you go through the SQL Tuning course by Amarnath Reddy from Udemy/ No you will not only learn more about SQL performance tuning but also learn SQL performance tuning tools which can help you with the task. I highly recommend this course to anyone who wants to improve their SQL and Database performance skills
Difference between table scan, index scan, and index seek in SQL
Based upon our understanding of indexes, you can now deduce the following points to summarize the difference between table scan, index scan, and index seek in a database:1) A table scan and an index scan are used when you need to retrieve all data like 90% to 100% while index seek is used when you need to retrieve data based upon some conditions like 10% of data.
2) If your query doesn't have WHERE clause and your table doesn't have a clustered index then a full table scan is used, if it does have a clustered index then index scan is used.
3) index scan is faster than a table scan because they look at sorted data and query optimizer know when to stop and look for another range.
4) index seek is the fastest way to retrieve data and it comes into picture when your search criterion is very specific. Normally, when you have WHERE clause in your query and you are using a column which also has an index, then index seek is used to retrieve data as shown in the following query:
select * from Employee where Id= 3;
You can confirm that by actually looking at the execution plan for your query.
In MSSQL management studio, you can see the execution plan by clicking Ctrl + A and then running your query.
That's all about the difference between table scan, index scan and index seek in a database. As I told, there are only two ways to retrieve data in a database either by using a table scan or by using an index. The later is faster in case of large tables. The choice of the index depends upon multiple things like the WHERE clause and joins in your table, the columns you are requesting, the size of tables, etc.
If you feel that your query is slow, you must check the execution plan to confirm whether its using index seeks or index scan or table scan. Then you can optimize your query by introducing the right index or tunning your query.
That's all about the difference between table scan, index scan and index seek in a database. As I told, there are only two ways to retrieve data in a database either by using a table scan or by using an index. The later is faster in case of large tables. The choice of the index depends upon multiple things like the WHERE clause and joins in your table, the columns you are requesting, the size of tables, etc.
If you feel that your query is slow, you must check the execution plan to confirm whether its using index seeks or index scan or table scan. Then you can optimize your query by introducing the right index or tunning your query.
No comments:
Post a Comment
It’s all about friendly conversation here at small review :) I’d love to be hear your thoughts!
Be sure to check back again because I do make every effort to reply to your comments here.