CODEWITHSUNDEEP
c#mysqlsqlperformanceinsert
Tibia Rook
Tibia Rook

Reputation: 369

C# - Make faster MySql queries?

what is the best way to insert a lot of data to mysql database?

Upvotes: 1

Views: 1191

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You are not just doing an insert, but also using on duplicate key update.

I would suggest that you take a bulk-load approach:

  • Put the 18-25,000 things into a file, appropriately delimited.
  • Load the data from the file using load data infile into a staging table.
  • Use the insert . . . on duplicate key update on the staging table.

The final step looks similar to your query:

INSERT INTO players (name, online, world, level, vocation, month, week, today)
    SELECT name, online, world, level, vocation, time, time, time
    FROM players_staging
     ON DUPLICATE KEY UPDATE
         online = values(online), world = values(world), level = values(level),
         vocation = values(vocation), month = month + values(time),
         week = week + values(time), today = today + values(time);

Bulk operations are faster for two reasons. First, they requires fewer calls into the databases -- setting up queries, returning results, and other overhead going back and forth. Second, they reduce the amount of logging and locking required for the operations.

Upvotes: 1

Related Questions