performance issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

performance issue

Author

Topic

rex99
Starting Member

5 Posts

Posted - 2004-07-20 : 05:13:46

Hi all,

I have a stored procedure which I execute on a table with tens of thousands of records.
When I run it for the first time after starting SQL Server (2000), or when I run it for the
first time from my .NET app, it can take 20 seconds to execute. Running it on subsequent
occasions is almost instantaneous.

It is a complex stored procedure with cursors (unavoidable, believe me), but the fact that
it can run at an acceptable speed makes me wonder what makes the difference when it is run
the first time.

I looked at the execution plan in Query Analyzer - a lot of the time is spent on table
scans. I haven't done any work with indexes before and am curious to find out whether index
scans might be a way around this odd bottleneck.

Here is the table script:

Thanks,

John

CREATE TABLE [dbo].[tblEmp_Timesheet] (
[Emp_Timesheet_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Employee_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Supervisor_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cost_Centre_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Event_ID] [int] NOT NULL ,
[Start_DTM] [datetime] NOT NULL ,
[Finish_DTM] [datetime] NOT NULL ,
[Posting_DTM] [datetime] NULL ,
[Rate] [smallmoney] NOT NULL ,
[Rate_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Approved] [bit] NOT NULL ,
[Duration] [smallint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEmp_Timesheet] ADD
CONSTRAINT [DF_tblEmp_Timesheet_Rate] DEFAULT (0) FOR [Rate],
CONSTRAINT [DF_tblEmp_Timesheet_Approved] DEFAULT (0) FOR [Approved],
CONSTRAINT [DF_tblEmp_Timesheet_Duration] DEFAULT (0) FOR [Duration],
CONSTRAINT [PK_tblEmp_Timesheet] PRIMARY KEY NONCLUSTERED
(
[Emp_Timesheet_ID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_Employee_ID] ON [dbo].[tblEmp_Timesheet]([Employee_ID]) ON [PRIMARY]
GO

CREATE INDEX [IX_Supervisor_ID] ON [dbo].[tblEmp_Timesheet]([Supervisor_ID]) ON [PRIMARY]
GO

CREATE INDEX [IX_Cost_Centre_ID] ON [dbo].[tblEmp_Timesheet]([Cost_Centre_ID]) ON [PRIMARY]
GO

CREATE INDEX [IX_Job_ID] ON [dbo].[tblEmp_Timesheet]([Event_ID]) ON [PRIMARY]
GO

CREATE INDEX [IX_Start_Finsh_DTM] ON [dbo].[tblEmp_Timesheet]([Start_DTM], [Finish_DTM]) ON [PRIMARY]
GO

CREATE INDEX [IX_Posting_DTM] ON [dbo].[tblEmp_Timesheet]([Posting_DTM]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEmp_Timesheet] ADD
CONSTRAINT [Employee <> Supervisor] FOREIGN KEY
(
[Supervisor_ID]
) REFERENCES [dbo].[tblEmployee] (
[Employee_ID]
),
CONSTRAINT [FK_tblEmp_Timesheet_tblCost_Centre] FOREIGN KEY
(
[Cost_Centre_ID]
) REFERENCES [dbo].[tblCost_Centre] (
[Cost_Centre_ID]
),
CONSTRAINT [FK_tblEmp_Timesheet_tblEmployee] FOREIGN KEY
(
[Employee_ID]
) REFERENCES [dbo].[tblEmployee] (
[Employee_ID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_tblEmp_Timesheet_tblEvent] FOREIGN KEY
(
[Event_ID]
) REFERENCES [dbo].[tblEvent] (
[Event_ID]
)

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 05:20:44

If it is only slow on first usage its probably because SQL is making a query plan.

But it would be advisable to get rid of the TABLE SCANs if you can - adding indexes etc. may help, but each index slows down INSERTs and UPDATEs, and costs disk space, so you probably need to take that into consideration.

Oh yeah, and getting rid of that "unavoidable, believe me" cursor would help

Post the source of the SProc, I'm sure folk here will advise if they think it can be improved.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 08:30:22

You should post the script anyway. We can look at the ddl until we're blue in the face. We won't be able to see what's causing the table scans though without the script.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources