Timeout Expired error. During Investigation, I noticed that the developer made an incorrect use of a UNION operator which was the culprit. In this article, I will demonstrate the issue using a demo student database.
First, create the database :
Create database student
Create a table named student inside the student database :
create table student ( student_id int identity(1,1), student_name varchar(50), city varchar(50) )
Insert a few rows of dummy data into the student table :
insert student(student_name,city) select 'Narayan','Mumbai' union select 'Nikhil','Goa' union select 'Rahul','Gurgaon' union select 'Rajat','Kanpur'
Create indexes on the student table :
CREATE CLUSTERED INDEX IDX_STUDENT_ID ON STUDENT(STUDENT_ID) CREATE NONCLUSTERED INDEX IDX_STUDENT_NAME_CITY ON STUDENT(STUDENT_NAME,CITY)
Now, assume a user executes the below T-SQL:
select * from student where student_name like '%N%' UNION select * from student where city='Kanpur'
Let’s have a look at the query execution plan as shown below:
As seen from the above screen capture, there is a SORT operator which costs around 63% of the overall plan which is very high.
Now if you have a careful look at the T-SQL; both the select statements are the except for the conditional clause in it. Therefore the same query could have been written :
select * from student where ((student_name like '%N%') OR (city='Kanpur'))
Now the query execution plan looks perfect. There is an Index Scan which means the query rustburgpharmacy.com/chloroquine optimizer gets an appropriate index to execute the desired query.
Therefore, it is essential that we carefully analyze the logic to be used while writing T-SQL. Writing untidy code unnecessary puts a burden on the query optimizer which in turn affects the performance of the server as well as the application.
Please let us know in the comments if you have any feedback or suggestions, alternatively you can contact me at singhsatnam84@yahoo.com.
]]>