0

We use MariaDB database on our projects. I observed program was working slowly on my machine. I made some test using Heidisql. Updating 2000 row un my machine takes 9 second. But when I open virtual machine in my computer and run same query it takes 0.7 seconds. Other computers in our office also shows the same result. They all execute the query around 0.7 seconds.

Table create code is like this.

CREATE TABLE `t_tag` (
    `Id` INT(10) NOT NULL AUTO_INCREMENT,
    `TagName` VARCHAR(100) NOT NULL DEFAULT ' ' COLLATE 'utf8mb3_general_ci',
    `DataType` INT(10) NOT NULL DEFAULT '0',
    `DataBlock` INT(10) NOT NULL DEFAULT '0',
    `VarType` INT(10) NOT NULL DEFAULT '0',
    `ByteAddress` INT(10) NOT NULL DEFAULT '0',
    `BitAddress` INT(10) NOT NULL DEFAULT '0',
    `PlcId` INT(10) NOT NULL DEFAULT '0',
    `TagLogTimerId` INT(10) NOT NULL DEFAULT '0',
    `ValueOffset` DOUBLE NOT NULL DEFAULT '1',
    `Digit` INT(10) NOT NULL DEFAULT '0',
    `ModbusType` INT(10) NOT NULL DEFAULT '0',
    `TagValue` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
    `TagMaxValue` DOUBLE NULL DEFAULT NULL,
    `TagMinValue` DOUBLE NULL DEFAULT NULL,
    `LastReadTime` DATETIME NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`Id`) USING BTREE,
    INDEX `FK_t_tag_t_plc_address` (`PlcId`) USING BTREE,
    CONSTRAINT `FK_t_tag_t_plc_address` FOREIGN KEY (`PlcId`) REFERENCES `t_plc_address` (`Id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4006
;

and update query is like this.

UPDATE t_tag SET TagValue = '91' WHERE Id = 1;
UPDATE t_tag SET TagValue = '90' WHERE Id = 2;
UPDATE t_tag SET TagValue = '89' WHERE Id = 3;
UPDATE t_tag SET TagValue = '88' WHERE Id = 4;

total 2000 rows

All the machines have same config files and same MariaDB versions. I removed and clean installed the MariaDB several times but no changes on the result.

If anyone encountered problems like this or knows how to solve this problem, it would be big help otherwise last resort is the installing windows again, but it is too much work to install all the development setup again.

I have tried installing and uninstalling the MariaDB. I have tried different version of MariaDB. I tried same query on different machines. I tried same query on my computer but in a virtual machine.

I expected the query takes similar times on all machines but. It only takes 9 seconds on my computer and 0.7 seconds on other computers.

Kivannc
  • 106
  • 8

1 Answers1

0

When running many queries in HeidiSQL, it can make a big difference to execute them in one (or few) larger batch(es). Executing them one by one adds a significant overhead. Try it out, on the dropdown menu besides the blue "execute" button:

enter image description here

Anse
  • 1,573
  • 12
  • 27
  • I choose send batch in one go in all computers all of them have the same execution conditions but only my computer has the slow execution time. – Kivannc Jan 05 '23 at 06:11
  • Well then it's probably a slow network connection of that specific computer. Or something on the router or firewall in your network which makes it slower. – Anse Jan 07 '23 at 12:29