B4J Question MySQL DELETE Slowness ?

[Mashiane]

Hi there

Has anyone experienced this? How did you solve it?

The volume of my deletes are like 5 records at a time and soon after that I execute an insert, however the inserts happen first, thus the sequence flow is not followed.

So after some time, adding sleep between the deletes and the inserts worked, but this might be / might not be the best approach. Or perhaps instead of hard deletes I might have to do soft deletes?

Any thoughts?

Assumptions: PHP usage.

 

[Sandman]

Show SQL.

 

[Mashiane]

Its something as simple as

DELETE FROM `organization_products` WHERE `organization_id` = ?

 

[Erel]

The thread title is bad.

How is PHP related? Why sleep?
What is the output of:

Dim n As Long = DateTime.Now
SQL.ExecNonQuery2("DELETE ...
Log(DateTime.Now - n)

 

[Sandman]

Wait, did you post in the B4J forum, but you actually want support for your PHP code? That's very confusing in that case - if so, ask Erel to move the thread to Chit Chat instead.

 

[Mashiane]

The thread title is bad.

Fixed

How is PHP related?

I'm calling a MySQL REST API from a B4J WebApp.

Why sleep?

When trying to figure this out it seemed to be the only solution that made sense with my approach

Perhaps some background. I have organizations and products. I have a table that links organizations to products. This is done initially when one registers an organization.

CREATE TABLE `organization_products` (
  `organization_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `organization_productid` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`organization_id`,`organization_productid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So what happens is, when a person selects 1..n products for an organization, the links are kept here. If for example, a person removes the products, a delete of all products linked to that organization should take place.

If products are updated / new products are added, a delete happens anyways and then an insert happens. So I inserted a Sleep between the Delete & Inserts, because for some reason the insert happen and then the deletes follows.

What is the output of:

293

 

[Sandman]

So I inserted a Sleep between the Delete & Inserts

A database like MySQL is meant to be pounded, hard. You're not meant to insert pauses for it to work well.

It's possible to make a database slow, typically by making a mistake with the indices. To investigate if that is the case for you I recommend executing your SQL statements using the awesome database tool DBeaver, or whatever other application you prefer. It should be instant. If it's not, that's your first action, to investigate the reason for that slowness.

 

[Mashiane]

A database like MySQL is meant to be pounded, hard. You're not meant to insert pauses for it to work well.

I believe so too that's why I'm all puzzled, kinda bizarre. We are using Heidi so I'm happy with that. Thanks

I also don't want the Sleep in my code and I will dig deeper into my code, perhaps there is something there I could do differently.

?

 

[Num3]

These two commands are your best friend for bulk transactions:

START TRANSACTION;
---
LOOP YOUR MYSQL COMMANDS HERE
--
COMMIT;

This way you get lot's of speed. I use this to insert over 500.000 records in a database in a few seconds.

MySQL - START TRANSACTION Statement

MySQL - START TRANSACTION Statement - In general, transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

www.tutorialspoint.com

 

[Sandman]

Yes, that is correct. But I would consider it wrong to ignore the root cause and solve the problem like this. (But, depending on your situation, it might be fine with a wrong solution. We're all operating under tight deadlines from time to time, for instance.)

 

[Mashiane]

In post #6 above, I detailed how the schema of my table is and what the indices are. So at the moment I don't really have a clear path in terms of what the root cause is.

My sub is simple, delete all records for this organization from this table and 2. Insert these records for this organization.

It would really be interesting to find out why the inserts happen after the deletes still. Its a very interesting observation though. Here is the example of a live version...

The Duplicate 10-2 should not happen because the assumption is that the records are deleted.

Thus me concluding that perhaps the DELETES are slow. The solution was to add a sleep between the DELETE & INSERT and it solved it, but that is not how I would do it.

I will play around with the code..

 

[aeric]

Fixed


I'm calling a MySQL REST API from a B4J WebApp.


When trying to figure this out it seemed to be the only solution that made sense with my approach

Perhaps some background. I have organizations and products. I have a table that links organizations to products. This is done initially when one registers an organization.

CREATE TABLE `organization_products` (
  `organization_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `organization_productid` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`organization_id`,`organization_productid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So what happens is, when a person selects 1..n products for an organization, the links are kept here. If for example, a person removes the products, a delete of all products linked to that organization should take place.

If products are updated / new products are added, a delete happens anyways and then an insert happens. So I inserted a Sleep between the Delete & Inserts, because for some reason the insert happen and then the deletes follows.


293

So you are creating an intermediate table between organization and product tables? Try adding Foreign keys.

CREATE TABLE `organization_products` (
  `organization_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `organization_productid` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`organization_id`, `organization_productid`) USING BTREE,
  FOREIGN KEY(organization_id) 
  REFERENCES organization(organization_id)
  ON DELETE CASCADE
  FOREIGN KEY(organization_productid) 
  REFERENCES product(productid)
  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL - ON DELETE CASCADE Constraint - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

www.geeksforgeeks.org

 

[alwaysbusy]

Show the full B4J code of the sub that does make the calls. I suspect some Promise thing may be going on.