Take care while using Function or Sub Query in Select List - SQL Server by serverku

At the time of development, we are just looking for it to be functionally work. For easy development, we are applying any code sometimes. But we never look for the performance issue at the time of the development. We look forward to the performance on the second stage. At this stage we find the issue and resolve it.

I want you to go through on one example where I have used one function and sub query in the select list to get the data in the script. Some time inner join is best instead of function and sub query, But it totally depends on the data and the script which you write.

How can we review?
We have a script to check all the stuffs here,

 
-- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
   DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
   DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO

-- Inserting records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Creating function which will be used in first script below
CREATE FUNCTION dbo.fn_GetItemTypeDesc   
(   
    @ItemType varchar(10)  
)   
RETURNS varchar(100)   
AS   
BEGIN   
 DECLARE @ItemTypeDesc varchar(100)  

 SELECT 
  @ItemTypeDesc = ItemTypeDesc 
 FROM ItemTypes 
 WHERE ItemType = @ItemType

 RETURN @ItemTypeDesc

End  
GO

Here we will review all below scripts which using function, subquery, and inner join accordingly and review the elapsed time and you for all.

 
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 dbo.fn_GetItemTypeDesc(id.ItemType) as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 (SELECT it.ItemTypeDesc FROM ItemTypes it WHERE it.ItemType = id.ItemType) as ItemTypeDesc,
id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
 ON (it.ItemType = id.ItemType)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Here are screen shots of output of each respectively.

(Click on images to enlarge)

I hope you have a better idea what are best for the execution of the query. Please note, review the execution plan before applying any change for the optimization.