Reputation: 2001
How to improve this sql query performace?
Let's say I have two tables Company (almost 60K records) and Position (almost 600K records)
Company table:
CompanyID INT --PRIMARY KEY
CompanyName NVARCHAR(100)
CompanyType INT --Just can be (1,2,3,4,5,6)
Position table:
PositionID INT --Primary key
PositionName NVARCHAR(100)
CompanyID INT --FK point to Company Table
WorkExperience INT --Just can be (1,2,3,4,5,6,7,8)
WorkType INT --Just can be (1,2)
CreateTime datetime
UpdateTime datetime
I have created a NONCLUSTERED INDEX on the Company table:
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Company]
(
[CompanyKind] ASC
)
INCLUDE ( [CompanyName]) ON [PRIMARY]
GO
And I have created two NONCLUSTERED INDICES ON the Position table also:
CREATE NONCLUSTERED INDEX [IX_6] ON [dbo].[Position]
(
[CompanyID] ASC
)ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_8] ON [dbo].[Position]
(
[UpdateTime] ASC
) ON [PRIMARY]
My paging stored procedure looks like this:
ALTER PROC [dbo].[spIndexJobList]
@KeyWord NVARCHAR(50) ,
@WorkExperience INT ,
@WorkType INT ,
@CompanyType INT ,
@PageSize INT ,
@PageNumber INT
--@RowCount INT OUTPUT
AS
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @SQL NVARCHAR(4000)
DECLARE @ParamDefinition NVARCHAR(2000)
SET @SQL = N'SELECT C.CompanyID,C.CompanyName,P.PositionName,P.PositionID,P.UpdateTime, Row_number() OVER (ORDER BY P.UpdateTime DESC) AS RowNumber FROM Company C INNER JOIN Position P ON C.CompanyID=P.CompanyID WHERE 1=1 '
IF @KeyWord!=''
SET @SQL = @SQL + ' AND PositionName LIKE @KeyWord'
IF @WorkExperience !=0
SET @SQL = @SQL + ' AND P.WorkExperience=@WorkExperience'
IF @CompanyType != 0
SET @SQL = @SQL + ' AND C.CompanyType=@CompanyType'
IF @WorkType !=0
SET @SQL = @SQL + ' AND P.WorkType=@WorkType'
SET @ParamDefinition = ' @KeyWord NVarchar(50),
@WorkExperience INT,
@WorkType INT,
@CompanyType INT,
@PageSize INT,
@PageNumber INT'
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber - 1
SET @RowStart = @PageSize * @PageNumber + 1 ;
SET @RowEnd = @RowStart + @PageSize - 1 ;
SET @SQL = '
WITH AllJobs
AS (' + @SQL
+ ')
SELECT *,(SELECT Count(RowNumber) FROM AllJobs) AS TotalRows FROM AllJobs WHERE RowNumber >='
+ STR(@RowStart) + ' AND RowNumber <= ' + STR(@RowEnd) + ''
EXECUTE sp_Executesql @SQL, @ParamDefinition,
@KeyWord, @WorkExperience,@WorkType,
@CompanyType, @PageSize, @PageNumber
END
My call statement is this:
SET STATISTICS IO ON
DECLARE @return_value int
EXEC @return_value = [dbo].[spIndexJobList]
@KeyWord='',
@WorkExperience = 3,
@CompanyType = 2,
@WorkType =1,
@PageSize = 30,
@PageNumber =2000
SELECT 'Return Value' = @return_value
GO
SET STATISTICS IO OFF
//-----------------------------------------------------------------------
(30 row(s) affected)
Table 'Company'. Scan count 3, logical reads 632, physical reads 0
Table 'Position'. Scan count 3, logical reads 4865, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
//-----------------------------------------------------------------------
The execute plan prompt missing index on(WorkExperience,WorkType). But after created index on (WorkExperience,WorkType) query slower.
Anyone give me some suggestions will very appreciate. Sorry for my bad english!
Upvotes: 3
Views: 184
Answers (4)
Reputation: 121902
DDL:
Company table:
CompanyID INT NOT NULL PK
CompanyName NVARCHAR(100) NOT NULL
CompanyType TINYINT NOT NULL
Position table:
PositionID INT NOT NULL PK
PositionName NVARCHAR(100) NOT NULL
CompanyID INT NOT NULL
WorkExperience TINYINT NOT NULL
WorkType TINYINT NOT NULL
CreateTime SMALLDATETIME
UpdateTime SMALLDATETIME
Index:
CREATE NONCLUSTERED INDEX IX_Position
ON Position (WorkExperience, WorkType, PositionName)
INCLUDE (UpdateTime)
SP:
ALTER PROC [dbo].[spIndexJobList]
@KeyWord NVARCHAR(50)
, @WorkExperience TINYINT
, @WorkType TINYINT
, @CompanyType INT
, @PageSize INT
, @PageNumber INT
AS BEGIN
SET NOCOUNT ON;
IF @PageNumber > 0 BEGIN
DECLARE
@RowStart INT
, @RowEnd INT
, @SQL NVARCHAR(4000)
, @ParamDefinition NVARCHAR(500)
SELECT @SQL = N'
SELECT
c.CompanyID
, c.CompanyName
, p.PositionName
, p.PositionID
, p.UpdateTime
, RowNumber = ROW_NUMBER() OVER (ORDER BY p.UpdateTime DESC)
FROM dbo.Company c
JOIN dbo.Position p ON c.CompanyID = p.CompanyID
WHERE 1=1 '
+ CASE WHEN @KeyWord != '' THEN ' AND PositionName LIKE @KeyWord' ELSE '' END
+ CASE WHEN @WorkExperience != 0 THEN ' AND p.WorkExperience = @WorkExperience' ELSE '' END
+ CASE WHEN @CompanyType != 0 THEN ' AND c.CompanyType = @CompanyType' ELSE '' END
+ CASE WHEN @WorkType != 0 THEN ' AND p.WorkType = @WorkType' ELSE '' END
SET @ParamDefinition = '@KeyWord NVARCHAR(50),
@WorkExperience INT,
@WorkType INT,
@CompanyType INT,
@PageSize INT,
@PageNumber INT'
SELECT
@PageNumber = @PageNumber - 1
, @RowStart = @PageSize * @PageNumber + 1
, @RowEnd = @RowStart + @PageSize - 1
, @SQL = '
WITH AllJobs AS (' + @SQL + ')
SELECT *
FROM AllJobs a
CROSS JOIN (
SELECT TotalRows = Count(RowNumber)
FROM AllJobs
) t
WHERE a.RowNumber BETWEEN ' + STR(@RowStart) + ' AND ' + STR(@RowEnd)
EXEC sys.sp_executesql
@SQL,
@ParamDefinition,
@KeyWord,
@WorkExperience,
@WorkType,
@CompanyType,
@PageSize,
@PageNumber
END
END
Manual:
Upvotes: 2
Reputation: 2042
Company.CompanyID and Position.CompanyID should be indexed, and you might as well take advantage of CLUSTER indexing these two columns so that the rows get physically ordered in sync with their indexes. This should provide significant performance improvement.
Upvotes: 0
Reputation: 1
Also I would create another non-clustered index on Company like below:
CREATE NONCLUSTERED INDEX IX_Company_CompanyId_Type
ON Company(CompanyId,CompanyType)
Because the where clause include "C.CompanyType=@CompanyType", without this index, this condition caused the table scan Company. With this index, it's index seek.
Upvotes: 0
Reputation: 12940
I would create a nonclustered index on Position like so:
CREATE NONCLUSTERED INDEX IX_Position_WorkExperienceWorkTypePositionName
ON Position (WorkExperience, WorkType, PositionName)
INCLUDE (PositionID, UpdateTime)
Upvotes: 0
Related Questions
- Need to improve the performance of a SQL query
- How to get better performance this sql query?
- Improve performance of a sql query
- Trying to see how I can improve the performance of this Sql query
- How to increase performance of this query?
- How to improve performance of the following query in MS SQL
- How to improve performance of this SQL Server query?
- How to speed up this SQL query
- Speeding up this SQL Query?
- Looking for some help improving the performance of this SQL query