Triggers Running Slow (Update Trigger)

Jul 20, 2005

am using FOR UPDATE triggers to audit a table that has 67 fields. My
problem is that this slows down the system significantly. I have
narrowed down the problem to the size (Lines of code) that need to be
compiled after the trigger has been fired. There is about 67 IF
Update(fieldName) inside the trigger and a not very complex select
statement inside the if followed by an insert to the audit table. When
I leave only a few IF-s in the trigger and comment the rest of the
code performance increased dramatically. It seems like it is checking
every single UPdate() statement. Assuming that this was slowing down
due to doing a select for every update i tried to do to seperate
selects in the beginning from Deleted and Inserted and assigning
columns name to specific variables and instead of doing
if Update(fieldName) i did
if @DelFieldName <> @InsFieldName
begin
INSERT INTO AUDIT
SELECT WHAT I NEED
END

This did not improve performance. If you have any ideas on how to get
around this issue please let me know.

Below is an example of what my triggers look like.

------------------------------------
Trigger 1 -- this was my original design
CREATE trigger1 on Table
FOR UPDATE
AS

if update(field1)
begin
insert into Audit
SELECT What I need
END

if update(field2)
begin
insert into Audit
SELECT What I need
END

..
..
.. Repeated about 65 more times

if update(field67)
insert into Audit
SELECT What I need
END
---------------------------------------


------------------------------------
Trigger 2 -- this is what i tried but did not improve performance
CREATE trigger2 on Table
FOR UPDATE
AS

Declare @DelField1 varchar
Declare @DelField2 varchar
..
..
Declare @DelField67 varchar

Select
@DelField1 = Field1,
@DelField2 = Field2,
....
@DelField67 = Field67
From Deleted


Declare @InsField1 varchar
Declare @InsField2 varchar
..
..
Declare @InsField67 varchar

Select
@insField1 = Field1,
@insField2 = Field2,
....
@InsField67 = Field67
From Inserted

-- I do not do if Update() but instead compare variables

if @DelField1 <> InsField1
begin
Insert into AUDIT
SELECT what I need
end

if @DelField2 <> InsField2
begin
Insert into AUDIT
SELECT what I need
end
....
....
....



if @DelField67 <> InsField67
begin
Insert into AUDIT
SELECT what I need
end

----------------------------------------------


IF you have any idea how to optimize this please let me know. Any
input is greatly appreciated. I do not have a problem with triggers
doing what they are supposed to, they are very slow this is my
concern. The reason I gave you two examples is because i suspect it
has something to do with the enormouse amount of code inside the
trigger. both examples perform about the same whether i use the two
huge selects from the Inserted and Deleted or not.

Thanks,

Gent

View 1 Replies


ADVERTISEMENT

Update Query Running Very Slow

Apr 17, 2008

Hi all,

I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.

Basically, here's the scenario:

1. Log files are being inserted into a SQL table via Log Parser 2.2.
2. I have a lookup table of ip address ranges to countries and other geographic data.
3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.

The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).

I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:

update dbo.Logs
set [Country] = @Country
where [IpNumber] = @IpNumber and [Country] = Null

Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.

Critical factors:

1. The log table must remain readable.
2. The query must execute in seconds -- not over half hour :)

I've experimented with various combinations of indexing, so far to no avail.

Any suggestions would be very much appreciated.

Regards

View 10 Replies View Related

Slow UPDATE - Running For 16 Hours And Counting...

Jun 7, 2004

The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.


UPDATE Recipients SET UndeliverableTime = getdate()
FROM Recipients
INNER JOIN Domains ON (Recipients.DomainID = Domains.ID)
INNER JOIN Undeliverables ON (
Recipients.UserName + '@' + Domains.Domain =
Undeliverables.EmailAddress)


Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?

Both tables (Recipients and Undeliverables) have approximately 80 million records

I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.

I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?

thanks!

View 14 Replies View Related

Multiple Update Triggers Or One Large Trigger With If&#39;s

May 1, 2001

I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated.
Thanks

View 1 Replies View Related

Very Slow Running Update Query Query

Nov 19, 2004

I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on

the query thats causing teh problem is


--//////////////////////////////////// this is the one thats running


Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions


any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one

Dave

View 6 Replies View Related

DTS Running Too Slow

Aug 8, 2000

I have a dts job set up to transfer 550,000 records from a dbf file into a sql server. If I just let it run, there is a 9-10 minute delay, then it starts. If I try to schedule a job, it fails completely. I looked up ways to get it to execute quicker, mainly going to the advanced tab of the transform arrow and making the inserts 1000 at a time, the table locked, turning constraints off. Any advice on how to speed it up or why the job is failing?

View 7 Replies View Related

Slow Running SP

Apr 27, 2002

Hi All

Last week, we upgraded to sql2000 from 6.5. Everything went on fine. we re-compiled all the procedures. When i try to run a procedure, its running for long time - more than 10 hours (in sql 6.5 it runs for 50 mins). do i need to set any procedure cache?.

Also, the server CPU usuage is constantly high - more than 80%. It was fine till last week.

Any suggestions?

Thanks in advance

Jaya

View 2 Replies View Related

SP Running Slow

Aug 30, 2006

i have written a SP, but it eventually runs slower. i have to run this SP 500 times.

do you know what is causing that?

View 13 Replies View Related

Slow Running

Jul 1, 2007

when i turn on my pc, it takes about 10 minutes from turning on to actually be able to use.

View 4 Replies View Related

Slow Running Server

May 2, 2001

Our server is running. There are no locks, and server has been rebooted but the problem is still there. This has been going on for some time now. I intend to restart the server. Does anybody have a quick solution, please help. Thanks for your assistance!!

View 4 Replies View Related

Slow Running Server

May 17, 2001

Please what do I look out for 6.5 if I want to troubleshoot for slow running.
Thanks.

View 2 Replies View Related

SQL Server Running Slow Down

Jun 6, 2001

Hi,

When first time I start my sql server is running faster. After 10 to 15 days later, sql sever performance is very slow. After I restart SQL service, to become normal.

Thanks
Mohan

View 1 Replies View Related

ADO Inserts Running Slow

Oct 13, 1999

Hi list,
I'm a long time lurker on this list and really enjoy the discussions, although I rarely get a chance to participate.

Here is my situation: We are importing chunks of data (500 records at a time) from a C++ interface. The records have to be transformed before inserting into the target table which I am doing using a stored proc which is working fine. The records are in memory in C++ and the programmer is looping through the records building inserts into a temp table through ADO (which my proc picks up). The server business object is using the connection.execute method which is inserting one record at a time. That part of the process is taking over 15 seconds for 500 records which is the bulk of the total time.

My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.

Any insight would be greatly appreciated.

shawn

View 2 Replies View Related

Slow Running Cursor...

Apr 14, 2004

I have linked three SQL Servers together, and have written a stored proc that has a cursor that joined three tables from one of the linked servers. When I pull the SQL out of the cursor definition and run it in a query window it runs fine, but when I run the stored proc that simply steps through the same select result set it is too slow for words. It also throws a warning about serial isolation levels. Is there any way I can fix this.

David

View 1 Replies View Related

Sql Server Running Slow

Mar 2, 2004

Hi,

Our main production server has started running slow, it is a dual zeon thingy with plenty of ram so hardware is not an issue.

Basically a service connects to the database and executes a few stored procs, the only way I can get the system up to speed again is to recompile one of the SPs but that is only a temporary fix.

Anyone had a similar thing?

Can anyone give me help on performance tuning in SQL server 2000.

Thanks

View 2 Replies View Related

Need Help, DTS Is Running Extremely Slow!!!

May 13, 2004

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

Gil

View 5 Replies View Related

Slow Running Proc's

Jan 30, 2008

Hello All,
I have two procedures being run one after the other.
when I run proc1 it runs for about 15 min.
Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min.
If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.

Thanks in Advance.

View 6 Replies View Related

Slow Running Function

Oct 23, 2007

Hi, when the following function is used within the where statement of a select statement it runs very slowly, anyone know why?



ALTER FUNCTION [dbo].[fn_GetLastDayOfLVPeriod] ( @pInputDate DATETIME )

RETURNS DATETIME

BEGIN



DECLARE @vLVDPeriod varchar(4)

DECLARE @vLVDYear varchar(4)

DECLARE @vOutputDate DATETIME

SELECT @vLVDPeriod = LVDPeriod

FROM dbo.tblMIDates

WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)

SELECT @vLVDyear = LVDYear

FROM dbo.tblMIDates

WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)

SELECT @vOutputDate = MIN(Date)

FROM dbo.tblMIDates

WHERE LVDPeriod = @vLVDPeriod

AND LVDYear = @vLVDyear

RETURN @vOutputDate

END

View 5 Replies View Related

Query Running Slow

Aug 24, 2007

hi

if suppose one query is running slow than what steps we can follow to optimize it...

this question is asked me in interview.

thanx

View 3 Replies View Related

Triggers Not Recognized When Running SP To Insert Set Of Records

Jan 31, 2000

I wrote a trigger that works fine when I insert record by record in the DB.
However, when I run a Stored Proc to insert a bunch of records at the same time, the trigger only works for the last record.

Anyone has a clue or a possible solution.
The trigger is well tested and works fine.

Best Regards,

Gabriel Cohen
gabrielc@yahoo.com

View 1 Replies View Related

One Trigger Vs Multiple Triggers ?

Aug 17, 2007



I have a question regarding Triggers.

Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.

Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.

Or I can create three different triggers each for Insrt, update and delete.

Which option is better for performance?

Thanks



View 2 Replies View Related

Urgent SQL Jobs Running Very Very Slow

Dec 29, 2001

Hi,

I have SQLServer 6.5 SP5a update running on Windows NT 4.0 SP6
with 4 gig RAM and 4 processor.

Suddenly the SQL 6.5 jobs running on the production server started running very very slow. A job that suppose to run in 30 minutes are running like 2 hours and completing successfully.

(I suspect the after the Norton Anti virus automatic live update may be the reason but not the Second Vulnerability as mentioned by Microsoft Bulletin last week)

I check the SQLServer, ran the performance monitor, checked pagefiles, disk space, databases,memory, tempdb. Everything seems to be normal.

I rebooted the server, checked any other process making that slow. But no use.

Please help me out with this issue as this is a production and the CRM applications from the clients uses the database server.

Thanks in advance,
Anu

View 1 Replies View Related

Crystal Reports Running Slow ??

Nov 9, 2004

All,

I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.

The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??

'Wale

View 9 Replies View Related

SQL Server 2005 Running Slow

May 3, 2007

Hi all,

I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..

please help me..If you need some more information please ask ..

Thanks in advance.

View 16 Replies View Related

Visual Studio Running Too Slow

Jan 27, 2006

Hi friends
after working visual studio (on my report model project) few minutes it runs too slow. i mean clicking on entities ,attributes takes ages to finish. I opened task manager i see "devenv.exe" is taking more than 800,000 k !!

am using sql server 2005 standard edition.
have you seen similar problem.
Thanks for your help.

View 19 Replies View Related

Stored Procedure Running Slow In ADO.NET

Jan 9, 2008

We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks

View 22 Replies View Related

Slow Running Insert Statement

Apr 6, 2007

I support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.



Thanks,



-Dave

View 3 Replies View Related

Slow Running SELECT TOP Query

Aug 27, 2007

I have a SELECT TOP query in order to return x number of top records from a table which has the indexing service enabled on it, such as this :


SELECT TOP(15) * FROM [TableName] ORDER BY [ColumnName]


and also there are not that many records(MAX 100 rows) kept in the table at the moment however, it will grow later.
The issue stems out from the ORDER BY [ColumnName] part of the syntax that changes the TOP selection order which makes the query to run very slow as I have also analyzed in the SQL SERVER QUERY ANALYZER.
Anyhow, I need to have the ORDER BY statement to show the data based on different columns either ascending or descending.
There might we workarounds to achieve the same goal that I am not aware of.
Any thoughts is appreciated.

View 3 Replies View Related

ASYNC_NETWORK_IO Qyery Running Slow

Oct 5, 2006

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

Environment

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.


Thanks
-Ashis

View 9 Replies View Related

New To Triggers - Deleted Folder Trigger

Jul 4, 2006

Hi

I would like to know how can I write a trigger for a table that lists folders deleted. I have a table called RECORDS which contains folders and would like to know folders that are being deleted.

Thanks...

View 8 Replies View Related

SQL Audit : CLR Trigger Vs TSQL Triggers

Nov 26, 2007

Hi,
I am trying to figure out which of these option is best suited for auditing. Although each one of them has its own pros/cons.
CLR trigger is easy to write and can be made generic so that it fits for any table required to be audited.
I tried both the option in test database and i found the CLR trigger performed poorly.
Results were :
For table A (3 columns) with TSQL trigger took less than a sec for 2500 sequential inserts.
While table B (3 columns) having same structure with CLR trigger took more than 20 sec for 2500 sequential inserts.

Has anybody done performance comparision of this 2 approaches ?
Please share results if any.

I wanted to validate that is my findings correct so that i select best optimized approach.

Thanks,
Jignesh

View 1 Replies View Related

Application Slow - Running Double Take 4.4 On SQL 2000

Jun 6, 2005

Hi,

We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.

We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.

We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.

We have set just like the Tech support recommended.

I would appreciate, if someone give us some recommendations to run the double take without any problem.

Thanks in advance.
Anu

View 3 Replies View Related

SQL Server Running Slow On A High End System

Mar 7, 2004

I have about a 447 MB SQL server 2000 database on a desktop PC acting as a QA server. The hardware specs of the QA box are as follows:

CPU: P4 2.4 GHz
Memory: 1GB
Drives: 80 GB IDE

I recently purchased a Dell PowerEdge 2650 server to act as the staging box. The staging box has

CPU: P4 2.4 GHz
Memory: 2GB
Drives: 40GB SCSI, mirrored

I made a backup of the database on the QA box, and restored it on the staging box. Yet when I run something as simple as a select query (select * from <table>), the less powerful QA box is faster.

I figured maybe the statistics are different on the staging box. I ran dbcc showcontig to make sure the statistics were identical. Also ran RedGate's SQL compare and data compare to make sure everything was identical.

I figured maybe the query optimizer needs to be tweaked. I recreated the indexes and updated statistics on the staging box. The queries actually got slower as a result.

I thought maybe SCSI drives are slower. Tried breaking the mirror on the staging box. No luck. Put the mirror back in place, ran a test where I copied a large folder from one directory to another on the staging box. Repeated the same test with the same data on the QA box. The staging box was more than twice as fast than the QA box.

It doesnt appear to be a problem with the query, adjusting memory in SQL server has not effect, both boxes are using SQL server 2000 SP3, why is the bigger machine running queries hundreds of milliseconds slower than the smaller machine? Any help will be appreciated!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved