Member Avatar for kgizo

Hi I have data to upload to a Mysql database and I need to get the time taken for each row to be inserted so that I can pass that number through to a progress bar. I have alreay tried accomplishing this by determining the number of rows affetced by the insertion then find the percentage of that number which is not the correct manner to do this.

here is the code

$result3=mysql_query("INSERT INTO dest_table.create_info SELECT * from Profusion.source_cdr") or   die(mysql_error());
$progress=mysql_affected_rows();

// Total processes
$total = $progress;
// Loop through process
for($i=1; $i<=$total; $i++){
// Calculate the percentage
$percent = intval($i/$total * 100)."%";
echo $percent;

this actually divides the total number of rows by 1 and multiplies by 100 to get the percentage and this is wrong .

I need the time taken for each row to be inserted and then find the percentage of that.

Your help will be highly appreciated.

  • 4 Contributors
  • 3 Replies
  • 408 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

— short answer: you can’t measure per-row times from PHP after a single server-side INSERT ... SELECT has finished. is correct about that, and is also right: if you need per-row progress you must change how the copy runs. A practical, common compromise is to copy in batches and report progress after each batch — that gives accurate percent/ETA while keeping the copy reasonably fast.

Recommended approach (high-level)

  • Get the total number of rows in the source (SELECT COUNT(*)) once at the start.
  • Copy rows in batches (e.g. 500–5000 rows). Use an indexed column (id) with WHERE id > last_id ORDER BY id LIMIT <batch> rather than large OFFSETs.
  • For each batch measure elapsed time (microtime), update a processed counter, compute percent = processed/total, and push that percent to the progress UI (AJAX poll, websocket, or update a progress table the UI reads).
  • Use transactions around each batch or a bulk insert for speed, and tune batch for your row size and server capacity.

Minimal example (pseudo-PHP using PDO — adapt to your schema)

$total = (int)$pdo->query("SELECT COUNT(*) FROM source_table")->fetchColumn();
$batch = 1000; $processed = 0; $lastId = 0;
while (true) {
  $rows = $pdo->prepare("SELECT * FROM source_table WHERE id > ? ORDER BY id LIMIT ?");
  $rows->execute([$lastId, $batch]); $data = $rows->fetchAll();
  if (!$data) break;
  $t0 = microtime(true);
  // perform bulk insert or prepared-statement inserts inside a transaction
  $t1 = microtime(true);
  $processed += count($data);
  echo intval($processed / $total * 100) . "%";
  $lastId = end($data)['id'];
}

Extra tips

  • If the transfer is small, an indeterminate spinner is fine — no need to slow the fastest server-side method. If both DBs are local and speed matters, INSERT ... SELECT or LOAD DATA INFILE is fastest but gives no per-row hook.
  • Use MySQLi or PDO (not the old mysql_* functions), wrap batches in transactions, and consider disabling/rebuilding noncritical indexes for big bulk loads.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

I need the time taken for each row to be inserted and then find the percentage of that.

You can't with the above code. By the time your code gets to the loop, the query has already been successfully completed.

Member Avatar for minitauros

What pritaeas says is (well, what else would you expect) right. To track progress, you'd have to separately select and insert those rows, which would - in this case - probably create an unnecessary delay in your operation.

Edited by minitauros
Member Avatar for Member #120589

How big is the transfer?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.