By: Ben Snaidero
Overview
In my years being a DBA I’ve seen many (even made some myself) common mistakes when reviewing the SQL queries that run against the systems I maintain. With this experience I’ve found that there are a some general guidelines that should be followed when writing queries and also when designing a database schema. In this tutorial we will take a look at a few different areas where these common mistakes are made and what can be done to fix them. These areas include:
- Query writing
- Indexing
- Schema design
Explanation
In each section of this tutorial we will take a look at specific examples that will illustrate things that should be avoided when it comes to performance in SQL Server. For each of these items I will provide a solution or alternative that would provide better performance. Please keep in mind that these are general guidelines and there will be exceptions to these examples but in general following these basic principles should get you off to a fast start performance wise.
The specific topics that will be covered in this tip are as follows:
- Query writing:
- How Join Order Can Affect the Query Plan
- Remove Function Calls From the SELECT List
- Avoid Using <> in WHERE Clause
- Avoid Using Functions in WHERE Clause
- Avoid Using Wildcard Characters to Start Search Criteria
- Use a Derived Table in Place of IN Predicate With Aggregate Functions
- Indexing:
- Make Sure All JOIN Columns are Indexed
- Use WHERE, JOIN, ORDER BY, SELECT Column Order When Creating Indexes
- Make Sure All Tables Have a Clustered Index Defined
- Schema design:
- Use DELETE CASCADE Option to Handle Child Key Removal in Foreign Key Relationships
- Denormalize For Performance
Sample Table and Data Setup
So you can work through these examples in your own environment below is a sample schema with some test data you can use to test each topic.
-- NOTE: You have to recreate these tables after each section of the tutorial
-- table creation logic
-- parent table
CREATE TABLE [dbo].[Parent](
[ParentID] [bigint] NOT NULL,
[IntDataColumn] [bigint] NULL,
[VarcharDataColumn] [varchar](1000) NULL,
[DateDataColumn] [datetime] NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
([ParentID] ASC)
)
GO
-- child table
CREATE TABLE [dbo].[Child](
[ChildID] [bigint] NOT NULL,
[ParentID] [bigint] NULL,
[IntDataColumn] [bigint] NULL,
[VarcharDataColumn] [varchar](10) NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
([ChildID] ASC)
)
GO
-- foreign key constraint
ALTER TABLE [dbo].[Child] WITH CHECK
ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ParentID])
ON DELETE CASCADE
GO
-- child detail table
CREATE TABLE [dbo].[ChildDetail](
[ChildDetailID] [bigint] NOT NULL,
[ChildID] [bigint] NOT NULL,
[ExtraDataColumn] [bigint] NULL,
CONSTRAINT [PK_ChildDetail] PRIMARY KEY CLUSTERED
([ChildDetailID],[ChildID] ASC)
)
GO
-- foreign key constraint
ALTER TABLE [dbo].[ChildDetail] WITH CHECK
ADD CONSTRAINT [FK_ChildDetail_Child] FOREIGN KEY([ChildID])
REFERENCES [dbo].[Child] ([ChildID])
ON DELETE CASCADE
GO
-- data load
DECLARE @val BIGINT
DECLARE @val2 BIGINT
SELECT @val=1
WHILE @val < 100000
BEGIN
INSERT INTO dbo.[Parent] VALUES(@val,@val,'TEST' + CAST(@val AS VARCHAR),getdate()-(@val/24.0))
SELECT @val2=1
WHILE @val2 < 20
BEGIN
INSERT INTO dbo.[Child] VALUES ((@val*100000)+@val2,@val,@val,'TEST' + CAST(@val AS VARCHAR))
INSERT INTO dbo.[ChildDetail] VALUES (1,(@val*100000)+@val2,9999)
INSERT INTO dbo.[ChildDetail] VALUES (2,(@val*100000)+@val2,1111)
INSERT INTO dbo.[ChildDetail] VALUES (3,(@val*100000)+@val2,3333)
INSERT INTO dbo.[ChildDetail] VALUES (4,(@val*100000)+@val2,7777)
SELECT @val2=@val2+1
END
SELECT @val=@val+1
END
GO
-- small table for joins
CREATE TABLE [dbo].[Small](
[SmallID] [bigint] NOT NULL,
[IntDataColumn] [bigint] NULL,
[VarcharDataColumn] [varchar](100) NULL,
CONSTRAINT [PK_Small] PRIMARY KEY CLUSTERED
([SmallID] ASC)
)
GO
-- data load
INSERT INTO dbo.[Small] VALUES(50,80,'TEST5080')
INSERT INTO dbo.[Small] VALUES(510,810,'TEST510810')
INSERT INTO dbo.[Small] VALUES(7001,9030,'TEST70019030')
INSERT INTO dbo.[Small] VALUES(12093,10093,'TEST1209310093')
INSERT INTO dbo.[Small] VALUES(48756,39843,'TEST48756,39843')
INSERT INTO dbo.[Small] VALUES(829870,57463,'TEST82987057463')
GO
-- cleanup statements
--DROP TABLE [dbo].[Small]
--DROP TABLE [dbo].[ChildDetail]
--DROP TABLE [dbo].[Child]
--DROP TABLE [dbo].[Parent]