I have a table that contains about 400,000 entries and which is called on the main page of the intranet website. At peak times, we can have 300-400 concurrent users. The SQL Profiler tool produces the following result.
- CPU: 406
- Reads: 32446
- Duration: 397
I indexed the fields involved in the 'where' clause. Is there a way to improve response time?
What should be done to reduce disk reads?
Server Configuration: Windows 2003 64bit, SQL Server 2005 64 bit SP2, .NET 2.0.
The following is the definition of the query and table. There are 40 more fields that I have not added for simplicity. These fields, which are mostly varchar, are not used in the where clause. They have just been shown on the page. There are several fields (5-6) that are not currently used, but I left them in the request because they will be needed later. Should I take them away now? will improve response time?
Query
SELECT
u.[PeopleKey],
u.[EnterpriseId],
u.[PersonnelNbr],
u.LastName,
u.FirstName,
u.MiddleName,
cc.WorkForceCd AS CareerCounselorWorkForceCd,
cc.WorkForceDesc AS CareerCounselorWorkForceDesc,
cc.WorkGroupCd AS CareerCounselorWorkGroupCd,
cc.WorkGroupDesc AS CareerCounselorWorkGroupDesc,
cc.CareerLevelCd As CareerCounselorCareerLevelCd,
cc.CareerLevelDesc AS CareerCounselorCareerLevel,
CL.NextLevelCD as nextCareerLevelCd
FROM
[User] u
LEFT JOIN [User] cc ON
u.[CareerCounselorPeopleKey] = cc.PersonnelNbr
Left JOIN [CareerLevel] CLON
u.WorkForceCd= CL.WorkForceCd AND
u.CareerLevelCd = CL.LevelCd
WHERE
u.PeopleKey = <integer>
[CareerLevel]
ID int 4 [Primary Key - clustered index]
Description varchar 150
WorkforceCd varchar 4
LevelCD varchar 10
NextLevelCD varchar 10
[User]
PeopleKey int 4 [Primary Key - clustered index]
EnterpriseId varchar 50 [non clustered index]
PersonnelNbr varchar 8 [non clustered index]
FirstName varchar 40
LastName varchar 40
MiddleName varchar 40
CareerCounselorPeopleKey int 4
CareerCounselorPersonnelNbr varchar 8
CareerCounselorName varchar 50
CapabilityCd varchar 5
CapabilityDesc varchar 25
WorkforceCd varchar 4
WorkForceDesc varchar 40
WorkGroupCd varchar 4
WorkGroupDesc varchar 50
CareerLevelCd varchar 10
CareerLevelDesc varchar 50