CODEWITHSUNDEEP
sql-server-2005performance
user20358
user20358

Reputation: 14736

improving sql server query response time

I have a table which contains around 400 000 records and which gets called on the homepage of the intranet website. At peak times we can have 300-400 concurrent users. The SQL Profiler tool gives the following output.

I have indexed the fields involved in the 'where' clause. Is there a way to improve the response time any more?

What needs to be done to reduce the disk reads?

Server configuration: Windows 2003 64bit, SQL Server 2005 64bit SP2, .NET 2.0.

Added the query and table definition below. There are some 40 more fields which I have not added here for sake of simplicity. Those fields which are mostly varchar are not used in the where clause. They are just there to shown on the page. There are some fields (5-6) which are currently not used but I have left them in the query because they will be required later. Should I take them off now then? would that improve the 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

Upvotes: 3

Views: 4766

Answers (4)

MikeyKennethR
MikeyKennethR

Reputation: 608

Join Left On - all these columns: ON u.[CareerCounselorPeopleKey] = cc.PersonnelNbr Left JOIN [CareerLevel] CL ON u.WorkForceCd= CL.WorkForceCd AND u.CareerLevelCd = CL.LevelCd

Like your WHERE clause the ON clause was for obvious key relations !

So the fat marked columns must be indexed also ...

Upvotes: 0

thijs
thijs

Reputation: 3465

Take a look at the SQL Server Performance Dashboard. If you have obvious issues they will show up there.

Also, you shouldn't just add indexes without analyzing the queries execution plan (and see what it is looking for).

Upvotes: 0

marc_s
marc_s

Reputation: 754398

Can you publish the table schema and the index definitions? What do you queries look like?

The amount of Reads would hint at massive I/O operations - so maybe due to your table and index setups and/or the way you query, even though you have indices, they're not being used.

Do you regularly run maintenance plans on the database? E.g. do your indices get reorganized and/or rebuilt when index fragmentation gets too high?

You could always use SQL's Database Tuning Advisor to try and get hints about how to optimize certain queries - or better yet, collect some real-life usage data using a SQL Trace and analyze that for potential tuning steps.

Marc

Upvotes: 1

Ian Suttle
Ian Suttle

Reputation: 3402

How large is your DB and how much RAM do you have on that box?

You mention indexes on the fields involved in the 'where' clause... are your index columns set in the same order as your where clause columns?

If you run the query execution plan in sql management studio do you see any table scans (you want seeks). If you see scans you might be missing indexes.

What's your primary key / clustered index? Can you switch your lookups to use the clustered index? This is always your fastest route to your data.

Hope that helps!

Upvotes: 0

Related Questions