StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to improve this sql query performace?
text
Body
copied!<p>Let's say I have two tables <code>Company</code> (almost 60K records) and <code>Position</code> (almost 600K records)</p> <p><code>Company</code> table:</p> <pre><code>CompanyID INT --PRIMARY KEY CompanyName NVARCHAR(100) CompanyType INT --Just can be (1,2,3,4,5,6) </code></pre> <p><code>Position</code> table:</p> <pre><code>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 </code></pre> <p>I have created a <code>NONCLUSTERED INDEX</code> on the <code>Company</code> table:</p> <pre><code>CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Company] ( [CompanyKind] ASC ) INCLUDE ( [CompanyName]) ON [PRIMARY] GO </code></pre> <p>And I have created two <code>NONCLUSTERED INDICES</code> ON the <code>Position</code> table also:</p> <pre><code>CREATE NONCLUSTERED INDEX [IX_6] ON [dbo].[Position] ( [CompanyID] ASC )ON [PRIMARY] CREATE NONCLUSTERED INDEX [IX_8] ON [dbo].[Position] ( [UpdateTime] ASC ) ON [PRIMARY] </code></pre> <p>My paging stored procedure looks like this:</p> <pre><code>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 </code></pre> <p>My call statement is this:</p> <pre><code>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 </code></pre> <p>//-----------------------------------------------------------------------</p> <pre><code>(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 </code></pre> <p>//-----------------------------------------------------------------------</p> <p><img src="https://i.stack.imgur.com/BqR3z.jpg" alt="enter image description here"></p> <p>The execute plan prompt missing index on(WorkExperience,WorkType). But after created index on (WorkExperience,WorkType) query slower.</p> <p>Anyone give me some suggestions will very appreciate. Sorry for my bad english!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload