UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Super Slow Updating A Table With Triggers, Access 2010    
 
   
RichardADD
post Oct 6 2019, 12:51 PM
Post#1



Posts: 33
Joined: 4-February 16



I have a table of 1.6M records on a backend
It has a table macro trigger that appends records to a log table also on the backend
I run an SQL update from the front end on the first mentioned table. It does not affect the field being logged so the trigger is not being used
When I remove the trigger, the update takes 3 minutes.
With the trigger, it goes for say 2 hours before I kill the Access job

Has anybody else had this problem ?

I have to remove the trigger and re-instate it when I want to run an update query

Thanks

Go to the top of the page
 
Jeff B.
post Oct 6 2019, 01:33 PM
Post#2


UtterAccess VIP
Posts: 10,309
Joined: 30-April 10
From: Pacific NorthWet


Can you post the code/trigger?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
theDBguy
post Oct 6 2019, 01:48 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you're updating a million record and each record is updating another table at the same time, then that might explain why it's taking a while to finish the job. Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Oct 6 2019, 02:33 PM
Post#4


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


My guess would be that, even if the trigger isn't fired, the condition to determine whether it should be fired has to be evaluated for each updated record, so that checking slows it down as you see.

We'd have to see more details about the process (i.e. the trigger itself) to be able to offer specific suggestions.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 6 2019, 05:01 PM
Post#5


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


I want to rephrase that.

Even if the trigger doesn't actually make any changes to data, it should be firing for each update, as that is what triggers are designed to do.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RichardADD
post Oct 7 2019, 12:16 AM
Post#6



Posts: 33
Joined: 4-February 16



Attached File  TriggerAppend.PNG ( 6.38K )Number of downloads: 0
Go to the top of the page
 
Jeff B.
post Oct 7 2019, 08:23 AM
Post#7


UtterAccess VIP
Posts: 10,309
Joined: 30-April 10
From: Pacific NorthWet


… and what does Census.AuditAdd do?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
orange999
post Oct 7 2019, 08:40 AM
Post#8



Posts: 1,975
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Richard,
Can you post a copy of the database with only a few records so we can "see and work" the database and the table data macro?

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 09:28 AM
Post#9


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


Just as I suspected, that macro is going to check which field got updated for each record that gets updated, one at a time. That would slow things down.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RichardADD
post Oct 7 2019, 09:45 AM
Post#10



Posts: 33
Joined: 4-February 16



Census.AuditAdd This adds one record to a thin table CensusAudit: datetime stamp and Email Address before and after
Remember under my problem condition, the trigger does not 'do' anything

I don't really really want you good people to solve the problem, but more to tell me if any of you have seen it before (or that you do this without a problem regarding performance)

Thanks
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 09:55 AM
Post#11


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


As I've observed twice, the macro FIRES every time a record gets updated because it has to check which field was updated. It doesn't matter that nothing happens after that check. The macro checks every single update to see which field was updated.

I don't know what you can do about that because it's exactly how these things are supposed to work.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nvogel
post Oct 7 2019, 01:04 PM
Post#12



Posts: 1,011
Joined: 26-January 14
From: London, UK


1.6 million rows in an ACE database? Have you considered putting this data into Oracle, SQL Server or some other SQL DBMS?
Go to the top of the page
 
RichardADD
post Oct 9 2019, 01:17 AM
Post#13



Posts: 33
Joined: 4-February 16



Whatisalsointeresting is that when the SQL update hits 26k records out of the 1.6M, the process takes less than 10 seconds if the SQL mentions only the target table.
The update SQL that gives me grief is one that has a join. ie update Target inner join Source on ... set Target.field = Source.field
Then again maybe its not the join but the number of records hit ... Mmm

Thanks for all your input so far
This post has been edited by RichardADD: Oct 9 2019, 01:19 AM
Go to the top of the page
 
GroverParkGeorge
post Oct 9 2019, 09:08 AM
Post#14


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


Hi again.

David's question is rather significant. It may be time to consider SQL Server or another server based database.

And, as I tried to highlight, the fact is that EVERY record updated does fire the Update Macro (or "trigger") because it has to check which field was changed. The fact that the update doesn't impact the target field only means that the remaining steps in your macro are not implemented. But that initial question, "Was Email updated in this record?" gets asked and answered EVERY TIME a record is updated.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RichardADD
post Oct 11 2019, 04:37 AM
Post#15



Posts: 33
Joined: 4-February 16



Second Prize:

Is there a way to switch triggers on / off without actually deleting them ?

Go to the top of the page
 
arnelgp
post Oct 11 2019, 05:15 AM
Post#16



Posts: 1,480
Joined: 2-April 09
From: somewhere out there...


just delete the After Update macro, the other macro won't get affected.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
MadPiet
post Oct 11 2019, 08:45 AM
Post#17



Posts: 3,334
Joined: 27-February 09



Switch them off in SQL Server?
You can disable them.

https://docs.microsoft.com/en-us/SQL/t-SQL/...SQL-server-2017
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2019, 08:45 AM
Post#18


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


I don't think so.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2019, 08:47 AM
Post#19


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


We're talking about Data Macros here, not SQL Server triggers. I am not a fan of using the term "trigger" interchangeably with "data macro" because of this very reason. It's a nice analogy to say that DMs work like triggers. It is also potentially misleading.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:19 AM