Query Timeouts after adding a logging table and SP

Hi everyone,
I hope you can help with some good suggestions. I have a system that was
working fine as far as performance, but after I added the following table and
SP, with a call to this SP from my Insert/Update/Delete queries, I started
getting timeout errors and the system came to a crawl. The first time it
happened I could not get it working until we rebooted the server. After that
it happened again the following day and then I backed out the calls to the
AddTableLog proc. Here is the table def and proc:
TableLogs definition
3 MessageId int 4 0
0 Message varchar 500 1
0 MessageDate datetime 8 1
0 AppLoggedInUser varchar 100 1
0 ComputerName varchar 100 1
0 CompLoggedInUser varchar 100 1
Proc to add to above table
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
Here is an example of how I was using the above log tables:
CREATE PROCEDURE [dbo].[nf_AddAttendance]
@.PatientId varchar(20),
@.AttendDate datetime,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, 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
Is there a better way to do this. I wanted to be able to log who actually
made the change and what computer they where at. So I pass that info in.
Would using a Trigger be faster at doing this? Or am I on the right track.
Thanks for any suggestions.
Michael LeeI forgot to mention that we are using SQL Server 2000.
Thanks again.
Michael Lee

Query taking longer to run after adding only 2 fields and additional JOIN

why does my query take 2x as long to run after adding the Fees in?

SELECT m.customer,

c.name,

c.customer,

c.state,

m.Branch,

CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then

(ph.totalpaid - ph.ForwardeeFee)

ELSE

0.00

END AS [Posted Amount],

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

'' AS [New Old CC],

'In-House' AS Type,

'' AS Active,

ph.UID,

m.number,

dc.amount CC,

p.amount AS PDC,

m.original,

ph.OverPaidAmt,

fg.FeeGoal_AZ,

fg.FeeGoal_IL

FROM dbo.Master m LEFT JOIN dbo.payhistory ph ON m.number = ph.number

INNER JOIN dbo.DeC dc ON dc.number = m.number

INNER JOIN dbo.pdc p ON p.number = m.number

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

INNER JOIN ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer

GROUP BY m.customer,

c.name,

c.customer,

c.state,

m.Branch,

ph.OverPaidAmt,

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

ph.UID,

m.number,

dc.amount,

p.amount,

m.original ,

ph.systemmonth,

ph.systemyear,

ph.ForwardeeFee,

fg.FeeGoal_AZ,

fg.FeeGoal_IL

HAVING ph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND

ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate())

ORDER BY m.customer

I assume that you mean that you added the two columns from the FeeGoal table to the select list and this caused the query to run 2x as long.

My guess would be that these columns are not included in an index and you forced the optimizer to do a table scan on the FeeGoal table.

Have you looked the the execution plan for one versus the other? What indexes do you have on the FeeGoal table? How big is the FeeGoal table?

I would start by looking at the plan, but if you could provide a bit more about these tables and what indexes they have on them, I might be able to give some tips.

HTH