Efficient T-SQL Code – A Case of Incorrect Use of a UNION Operator

A couple of days back, I had a developer complaining me the slow performance of some T-SQL which was causing the application to throw a 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'
select 'Nikhil','Goa'
select 'Rahul','Gurgaon'
select 'Rajat','Kanpur'

Create indexes on the student table :



Now, assume a user executes the below T-SQL:

select * from student where student_name like '%N%'
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 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.

Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |