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?

Luís Silva · Answer 1 · 2023-01-20T11:45:57.270

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.

score 0 · Answer 2 · answered Jan 18 '23 at 01:18

0

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.

answered Jan 18 '23 at 01:18

Rick James

135,179
13
127
222

I added it to the question. But if you look at the code, I already try to insert 1000 (well close to) into one insert statement. I just concatenate the insert statement until everything is added. Then only after that do I call the execute. As mentioned, the execute is indeed faster than doing it 1000 execute, but it is the concatenation of the values that takes a lot of time – Bew Games Jan 18 '23 at 15:56
@BewGames - "a lot of time" -- Have you instrumented your code to see how many microseconds for the "concatenation" versus the "INSERT"? (I usually use PHP, which is an order of magnitude slower than C++; the extra time for concatenation does not bother me.) – Rick James Jan 18 '23 at 16:41
With 1000 separate execute calls to the DB, I can insert about 500-1000 per second (it varies on my local machine). When I concatenate 1000 values the execute() takes < 1 sec which is expected. But the concatenation takes 20+ seconds (I don't remember the exact amount, but a VERY long time). All I remember is that I was ending up with about 20 inserts / second using this method and 99% of the time was spent on the concatenation. I tried doing a manual concatenation with std::string and calling the command SQL() instead. I ended up inserting 3000 rows per second. – Bew Games Jan 18 '23 at 19:49
Yikes! Let's see the concat code. Surely there is a faster way to do it. – Rick James Jan 19 '23 at 06:45
In PHP, I can do the concat at about 1000/second. But PHP is interpretative, hence perhaps 20 times as slow as C++ ! So, 3K/sec sounds slower than possib.e – Rick James Jan 19 '23 at 06:56

X devAPI batch insert extremely slow

2 Answers2