CODEWITHSUNDEEP
javamysqlsql
Ini Koq Apah
Ini Koq Apah

Reputation: 75

Update data programmatically in code(Java) or in database using trigger (MySQL)?

Edit : I need the program to do computational faster, the update process is needed to be real time (calculating 100.000 to 200.000 records for about 0-5 seconds), this process will be doing in multi threading (about 2-8 thread)

I will create program that will update data in database. The update process will be triggered if I do insert data in table. The update process will computational costly. This is the idea of my update process :

  1. I will have 3 tables (A,B,C) and each of it will have data aroud 100.000-200.000 records
  2. I will have table D to save table A, B, C record count
  3. I will have table E to save table A, B, C, D calculation, the calculation including multiplication, division and log base 2
  4. I will insert data to table A, B, C and after that data in table D and E must be updated
  5. This process can be repeated 3-5 times in every update

Which one is computationally faster, doing update programmatically or using trigger?

Upvotes: 2

Views: 1964

Answers (4)

O. Jones
O. Jones

Reputation: 108806

Computational cycles in your database server are a scarce resource. You can have as many server instances running your Java program as you can afford, and as your application scales up (adds users, for example) you can distribute the computationally intensive operation to multiple servers.

If you perform the computationally expensive stuff on your MySQL server, you are deciding to do it all on a central machine.

Plus, in my experience, it is easier to unit test and maintain Java code than it is to do the same for trigger / stored procedure code.

That being said, MySQL's floating-point math operations (log, multiplication) exploit the server's math instruction set and are reasonably efficient.

Upvotes: 1

duffymo
duffymo

Reputation: 308988

I will have 3 tables (A,B,C) and each of it will have data aroud 100.000-200.000 records

This is a trivially small database.

I will have table D to save table A, B, C record count

Are you keeping a history of records in each on a particular datetime? That's the only reason to do this.

I will have table E to save table A, B, C, D calculation, the calculation including multiplication, division and log base 2

Easy stuff.

I will insert data to table A, B, C and after that data in table D and E must be updated

Making all of this a single unit of work is the important thing. I hope you're making them a transaction.

This process can be repeated 3-5 times in every update Which one is faster, doing update programmatically or using trigger?

I would think database - stored procedure and trigger first. Especially if there are a lot of UPDATEs.

Are the three sources of data A, B, and C independent? If I INSERT into A, do I execute the calculations for D and E? Likewise for B and C? Could you potentially have a great deal of traffic if all three have records INSERTed in a short period of time?

Upvotes: 0

XSen
XSen

Reputation: 318

A trigger will be faster as it will work closest to the data and you will not have to transfer data between the database and the program layer. The problem with a trigger is that it will be triggered for every insert. Is that something that you want or would you prefer to only do the computation once a lot of data (ie many inserts) has been inserted.

Upvotes: 0

Deepak Rai
Deepak Rai

Reputation: 2203

Better to go with TRIGGERS and keep this complexity on DB side. This will help you in keeping your code clean. If you are going to do INSERT from different files then you may need to rewrite UPDATE logic in all those files.

And if in future you need to change the after insert logic then you just need to update your trigger instead of updating the code.

Upvotes: 3

Related Questions