CODEWITHSUNDEEP
phpmysqlperformanceoptimization
blues
blues

Reputation: 5185

Technical reasons to write sql query into a file instead of executing it?

What the title says. I am reading some old code and there is a part where a lot of data is read in, but instead of directly writing it into the database an ascii file with a lot of sql INSERT queries is created and this file is than delivered to the sql-server. Why would anyone do that when you can just execute the queries right away? My understanding is that this can only hurt performance? Or is there a good reason to do this?

Upvotes: 0

Views: 46

Answers (2)

Rick James
Rick James

Reputation: 142278

One could say there are three ways to do lots of INSERTs:

  • One row at a time from code, and one per transaction.
  • Batched INSERT statements, with at least 100 rows per insert.
  • LOAD DATA INFILE ...

The first is the slowest, the last is the fastest. There is probably more than a factor of 10 in performance.

But what you described sounds like a file full of 1-row INSERTs. That does not help performance, but it might be more convenient for the programmer.

If that set of INSERTs is all one InnoDB transaction, then it ranks somewhere between the first and second bullet items above.

Upvotes: 1

thomasKberlin
thomasKberlin

Reputation: 96

maybe the idea was to keep track of changes to the database on a time-slice-basis or have a pre-commit-backup? distribution on multiple servers? debugging is a good guess as well. anyways, there are better ways to do those things.

Upvotes: 1

Related Questions