sql-server - 如何提高这个sql查询性能？

Question

假设我有两个表Company(将近 60K 条记录)和 Position(将近 600K 条记录)

公司表:

CompanyID     INT                --PRIMARY KEY
CompanyName   NVARCHAR(100)      
CompanyType   INT               --Just can be (1,2,3,4,5,6)     

位置表:

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

我在 Company 表上创建了一个 NONCLUSTERED INDEX:

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Company] 
(
    [CompanyKind] ASC
)
INCLUDE ( [CompanyName]) ON [PRIMARY]
GO

我还在 Position 表上创建了两个 NONCLUSTERED INDICES:

CREATE NONCLUSTERED INDEX [IX_6] ON [dbo].[Position] 
(
    [CompanyID] ASC
)ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_8] ON [dbo].[Position] 
(
    [UpdateTime] ASC
) ON [PRIMARY]

我的分页存储过程如下所示:

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 

我的调用语句是这样的:

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

//-------------------------------------------- --------------------------

执行计划提示缺少索引(WorkExperience,WorkType)。但是在 (WorkExperience,WorkType) 上创建索引后查询速度变慢。

任何人给我一些建议都会非常感激。抱歉我的英语不好!

Answer 1

DDL:

公司表:

CompanyID     INT NOT NULL PK
CompanyName   NVARCHAR(100) NOT NULL     
CompanyType   TINYINT NOT NULL    

位置表:

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

索引:

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 

手动:

• Bad habits to kick : choosing the wrong data type