B4J Question MySQL DELETE Slowness ?

Mashiane

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
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.

B4X:
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
 
Upvote 0

Sandman

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
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.

šŸ™
 
Upvote 0

Num3

Active Member
Licensed User
Longtime User
These two commands are your best friend for bulk transactions:
B4X:
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.

 
Upvote 0

Sandman

Expert
Licensed User
Longtime User
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.)
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
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...

1657127536364.png


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..
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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.

B4X:
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.

SQL:
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

 
Upvote 0
Top