Could this SP be causing Timeout errors

The other day I had added a little call to a proc that logged
the adds or updates to several tables. I wanted to do this to tell who made
changes to the records in case we have an issue down the road. After putting
this change in I started to really have a major problems with the system
slowing down. Here is some the tables and procs:
Table TableLog:
MessageId int
Message varchar 500
MessageDate datetime
AppLoggedInUser varchar 100
ComputerName varchar 100
CompLoggedInUser varchar 100
Proc to add new entries:
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
How the above proc is used:
CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
@.PatientId varchar(20),
@.AttendDate datetime,
@.C_ID int,
@.GroupTime varchar(8),
@.Diagnostic tinyint,
@.Program tinyint,
@.AttendedGroup tinyint,
@.DocId int,
@.GroupId int,
@.GroupType Tinyint,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
@.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
Session_User,@.ComputerName, @.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Does this look like it could cause the slow down in the system. I backed out
most of the procs that had the call to the Log table, but still have the
issue. If you think this is causing the issue with slow downs or locks, is
there a better way to do this. THanks for any suggestions.
MichaelHope this helps a bit:-
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Manu Jaidka
"Michael" wrote:
> The other day I had added a little call to a proc that logged
> the adds or updates to several tables. I wanted to do this to tell who made
> changes to the records in case we have an issue down the road. After putting
> this change in I started to really have a major problems with the system
> slowing down. Here is some the tables and procs:
> Table TableLog:
> MessageId int
> Message varchar 500
> MessageDate datetime
> AppLoggedInUser varchar 100
> ComputerName varchar 100
> CompLoggedInUser varchar 100
> Proc to add new entries:
> CREATE PROCEDURE [dbo].[AddTableLog]
> @.Message varchar(500),
> @.AppLoggedInUser varchar(100),
> @.ComputerName varchar(100)
> AS
> Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
> CompLoggedInUser)
> Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
> GO
> How the above proc is used:
> CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
> @.PatientId varchar(20),
> @.AttendDate datetime,
> @.C_ID int,
> @.GroupTime varchar(8),
> @.Diagnostic tinyint,
> @.Program tinyint,
> @.AttendedGroup tinyint,
> @.DocId int,
> @.GroupId int,
> @.GroupType Tinyint,
> @.ComputerName varchar(100),
> @.LoggedInUser varchar(100)
> AS
> Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
> Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
> UpdatedBy,ComputerName, LoggedInUser)
> values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
> @.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
> Session_User,@.ComputerName, @.LoggedInUser)
> Declare @.UserMessage Varchar(500)
> select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
> record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
> Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
> GO
> Does this look like it could cause the slow down in the system. I backed out
> most of the procs that had the call to the Log table, but still have the
> issue. If you think this is causing the issue with slow downs or locks, is
> there a better way to do this. THanks for any suggestions.
> Michael
>