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.

DataType can matter in where condition - a worst case scenario by serverku

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,

 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

Best case,

 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'

Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.

Table scan or Unexpected output due to improper where condition applied for DateTime filter by serverku

When you are using date filter with queries, like to find the records from tables for the particular year/month, we are approaching some extra data type conversion on fields. Below are some examples.

Should not use,

 
-- #1. : This cause to table scan
SELECT *  
FROM OrderDetails
WHERE datepart(month,Orderdate) =?
AND datepart(year,Orderdate) = ?

-- #2. : This cause to unexpected output 
SELECT * 
FROM OrderDetails
WHERE convert(varchar,OrderDate,112) between
'20110101' and '20110131' 

Should use,

 
-- #1.
SELECT * 
FROM OrderDetails
WHERE convert(varchar,Orderdate,112) >= '20110101' 
AND convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

-- #2.
SELECT * 
FROM OrderDetails
WHERE cast (OrderDate as datetime) BETWEEN
 '2011-01-01 00:00:00.000' AND '2011-01-31 23:59:59.900'

Create table with sample records or use existing table which has datetime data type field, Run query above and check the results with execution plan. Hope you like it.

Missing index suggesion by Execution Plan - SQL Server by serverku

The index is a most important factor in the optimization and performance. Whenever we work for the query optimization, we are mostly looking for indexes, then check any other factors. Also scheduling the job for the maintenance of the indexes on periodical basis. From Index usage and statistics report, we can have an idea for the index optimization. But SQL Server query execution plan also recommended and give the suggestion with index hint. We will look here the demonstration for the same with execution plan.

--- 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 sample 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

-- Review the execution plan.
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)
GO

You can see the index hint with the execution plan, and following is a script for same.

/*
Missing Index Details from SQLQuery.sql - ReportServer (dba (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.0255%.
*/

/*
USE [ReportServer]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO
*/

Apply it and enjoy!

Using Covering Index and Performance Review - a new Index type of SQL Server 2005 by serverku

We all know about the indexes and the concept of it. As well as the types of the indexes. During working one query optimization, I got suggested by the SQL Server Optimizer with index creation and that index was Covering index.

What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,

USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblCoveringIndex','U') > 0)
DROP TABLE TblCoveringIndex

CREATE TABLE TblCoveringIndex
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in table 

INSERT INTO TblCoveringIndex
SELECT TOP 80000
convert(bigint,CONVERT(VARCHAR(100),a.object_id) + CONVERT(VARCHAR(100),b.object_id) ),
CONVERT(VARCHAR(100),a.name) + CONVERT(VARCHAR(100),b.name) ,
B.create_date,
b.type_Desc
FROM SYS.OBJECTS A
CROSS JOIN SYS.OBJECTS B

GO

Now we will review the execution plan without any indexes created on the table.

--  Execution plan with any indexes
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex
WHERE CreateDate >= GETDATE() - 50

GO

Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblCoveringIndex (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblCoveringIndex (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblCoveringIndex (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

We have created an additional non-clustered index with an included column, which are going to be used in the select list. After index's creation, It is finally time to review for each index created and execution plans of the queries using each of them. Here we force the query to use the normal non-clustered index and addition non-clustered index created.

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate)) 
-- Forcing index hint of normal nonclustered index
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate_Covering)) 
-- Forcing index hint of additional nonclustered index 
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

GO

(Clink on image to enlarge)

I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.