Code Readbility and Performance

Its always good when TSQL code we write with proper indentations, naming conventions, readability. Equally to all this points the code should be performance compliant. I found this post good one. I want to try the same. We have a table with 3 columns. All columns indexed. We need to make a search based on the three columns.

Code Required to setup our demo
USE TEMPDB
CREATE TABLE TestforCode
(
     ID INT IDENTITY(1,1) PRIMARY KEY,
     NAME VARCHAR(100),
     CITY VARCHAR(100)
)

DECLARE @I INT
SET @I = 1
WHILE 1=1
BEGIN
    INSERT INTO TestforCode(NAME, CITY)
    VALUES (('NAME ' + CONVERT(CHAR(20),@I)),('CITY ' + CONVERT(CHAR(20),@I)))
    IF @I > 10000
    BREAK;
    SET @I = @I+1
END

CREATE INDEX IX_TestforCode_City ON TestforCode(City)
CREATE INDEX IX_TestforCode_Name ON TestforCode(Name)

Input is all 3 columns I need to search based on it, Below two Queries would serve the purpose.
Query#1
DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)

SET @ID = NULL
SET @City = 'City 1'
SET @Name = NULL
IF @ID IS NOT NULL
    SELECT * FROM TestforCode WHERE (ID = @ID)
ELSE
    IF @Name IS NOT NULL
    SELECT * FROM TestforCode WHERE (NAME = @Name)
ELSE
    IF @City IS NOT NULL
    SELECT * FROM TestforCode WHERE (CITY = @City)

Query#2
DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)
SET @ID = NULL
SET @City = 'City 1'
SET @Name = NULL
SELECT * FROM TestforCode WHERE (ID = @ID) OR
(NAME = @Name) OR (CITY = @City)

Looking at this Query#2 looks sleek and simple but what about performance. End of Day You query should be optimal in terms of CPU, IO usage. Lets see Execution Plan for both Queries. Ctrl Key+M is the command to get actual execution plan.

Query#1











Query #2







Query #1 is the optimal query which uses non-clustered index on city column and a bookmarklookup to fetch other records. As much as code readability it also need to be performance compliant code.


More Reads

Top 10 Developer Mistakes That Won't Scale
Transact-SQL Tips and Tricks


Happy Learning!!!