X devAPI batch insert extremely slow

Question

I use the C++ connector for MySQL and the X dev API code.

On my test server (my machine), doing a single insert in loop is pretty slow (about 1000 per second) on a basic table with a few columns. It has a unique index on a char(40) field which is possibly the cause of the slowness. But since the DB is configured as developer mode, I guess this should be expected.

I wanted to improve this by doing batch inserts. The problem is that it is even slower (about 20 per second). The execute() itself is quite fast, but the .values() are extremely slow. The code looks something like this:

try
{
    mysqlx::TableInsert MyInsert = m_DBRegisterConnection->GetSchema()->getTable("MyTable").insert("UniqueID", "This", "AndThat");
    for (int i = 0; i < ToBeInserted; i++)
    {
        MyInsert = MyInsert.values(m_MyQueue.getAt(i)->InsertValues[0],
            m_MyQueue.getAt(i)->InsertValues[1],
            m_MyQueue.getAt(i)->InsertValues[2]);
    }
    MyInsert.execute();
}
catch (std::exception& e)
{
}

Here is the table create:

CREATE TABLE `players` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `UniqueID` char(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `PlayerID` varchar(500) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `Password` varchar(63) DEFAULT NULL,
  `CodeEmailValidation` int DEFAULT NULL,
  `CodeDateGenerated` datetime DEFAULT NULL,
  `LastLogin` datetime NOT NULL,
  `Validated` tinyint DEFAULT '0',
  `DateCreated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqueID_UNIQUE` (`UniqueID`)
) ENGINE=InnoDB AUTO_INCREMENT=21124342 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Any clue why this is much slower? Is there a better way to do a batch insert?

Answer 1

The issue is in your code.

MyInsert = MyInsert.values(m_MyQueue.getAt(i)->InsertValues[0],
        m_MyQueue.getAt(i)->InsertValues[1],
        m_MyQueue.getAt(i)->InsertValues[2]);

You are copying over and over again the MyInsert object to a temporary and restroying it....

Should only be:

MyInsert.values(m_MyQueue.getAt(i)->InsertValues[0],
        m_MyQueue.getAt(i)->InsertValues[1],
        m_MyQueue.getAt(i)->InsertValues[2]);

However, since this could be prevented on the connector code, I'll report a bug to fix the copy behavior.

Answer 2

INSERT up to 1000 rows in a single INSERT statement. That will run 10 times as fast.

Is that CHAR(40) some form of UUID or Hash? If so, would it be possible sort the data before inserting? That may help it run faster. However, please provide SHOW CREATE TABLE so I can discuss this aspect further. I really need to seen all the indexes and datatypes.