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]