Issue during update

Question

Issue during update

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

net,

I've got a trigger on one of my tables which processes inserted data in join with data from 4 other tables and 17 views. Then it populates "result" table with processing results.

It takes 0.2s to process 600 rows on single CPU single HDD server.

Not sure if any application could give a timeout here.

Only badly written triggers create problems.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

... or, you can throw hardware at the problem ... r-i-g-h-t...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 3

That's why I always insist on making DEV server the weakest one.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Heh... you did that, too, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 5

I wished I could do that too, but the load on my Dev servers is *significant* and all the client side code is full of timeouts settings

* Noel

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

Hello,

I have not tested it yet, but agreed with you, the only problem is that triggers are not only on 1 table but on lots of table, this may affect performance.

It will see later on

Thank

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Net, you probably did not get the point in my initial post.

Trigger must just record PKs of the rows affected by changes.

It must affect just single "trace" table.

Your updates must be be performed independently from another SP.

"Trace" table will just tell to the SP which rows were changed since last run to avoid excessive table scans and re-scans.

Of course, after SP completed it's task it must clear "trace" table from PKs it used for updates.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

I know... preaching to the choir but I have to say it... Putting a better server on the dev side may not help that problem...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fasttrack SSCertifiable Points: 6071 More actions · Answer 9

Hello,

thank to everybody.

My problem is that there are lots of tables and only updated and new data must be moved to the destination tables.

To detect any changes or insert row for every table I have to create a trigger for each table. Each triggers fill a table with pk new or updated (In this way I know only wich row for each table must be selected).

With too many tables and triggers application hold on.

Without trigger the application work fine

Please, any contribution is desidered.

Thank

Sergiy SSC Guru Points: 110208 More actions · Answer 10

I guess your problem is terrible design.

Can you post DDL for your "many tables" to give us an idea what is it about.

And post one of your triggers. I'm pretty sure it's done wrong.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Sergiy (9/24/2007)
I guess your problem is terrible design.
Can you post DDL for your "many tables" to give us an idea what is it about.
And post one of your triggers. I'm pretty sure it's done wrong.

Heh! You always were good at getting right to the heart of the problem 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fasttrack SSCertifiable Points: 6071 More actions · Answer 12

Hello,

the database was built years ago by others people, tables to be check are +/- 100.

I cannot modify anything about this database and anyone cannot modify the application.

I was asked to move data to new database. Because I would like to avoid to move data already "moved", I thought to add trigger for each table, but here the problem.

About ddl for tables, sorry I cannot to distribute to external peolpe.

If anyone can find a solution to this issue he is very welcome.

Thank

Sergiy SSC Guru Points: 110208 More actions · Answer 13

net,

terrible design remains terrible design regardless who and when has done it.

If you need to do 100 tables you need 100 triggers, one extra field in your tracing table "SourceTableID" and extra table to hold the list of those "Source Tables".

And question remains the same: what makes your trigger(s) slow?

_____________
Code for TallyGenerator