large data issues

[carpekd]

I'm developing an app that uploads users' CSV files into a mysql database with the following table structure:

 

mysql_query("create table `datasets`(
	dataset_id int auto_increment not null primary key,
	name varchar(255))");
	
mysql_query("create table `dataset_rows`(
	row_id int auto_increment not null primary key,
	dataset_id varchar(255))");

mysql_query("create table `dataset_cols`(
	col_id int auto_increment not null primary key,
	dataset_id varchar (255),
	field_name varchar (255))");
	
mysql_query("create table `data`(
	data_id int auto_increment not null primary key,
	row_id varchar(255),
	col_id varchar(255),
	data varchar (255))");

 

I'm having issues with execution times... I've set my time limit to 0 so the script wouldn't timeout and I could test how long it actually takes.  When a large csv file is uploaded (>3mb) and a loop is done to insert values into mysql, it takes several minutes.  When I then try to loop through and display this data in an HTML table, it takes waaaay longer (>15min).  My question is, how can I run the csv import process in the background when the user submits a csv file? and is there a suggested way to change the database architecture to speed up things?

 

Thanks for the input

[requinix]

I would do the upload with a command-line script. Create a table with information about each upload, like who started it when, where you've relocated the file (temporarily), and what state it's in (like waiting to import, being imported, and finished). Move the file to the new temporary location and create a row for the import.

 

Then make a command-line script that does the import itself. You pass it the ID number of the import from the new table, it does the import and updates the status as it runs. You can run it in the background like

exec("nohup /usr/bin/php /path/to/importscript.php {$jobnumber} &");

(that's for Linux only)

 

When displaying you should look at the import status first so you know whether it's finished or not.

 

 

If, in the future, this results in too much server load because too many imports are happening at once, you can change it to use a queuing system...

 

 

3MB for an import file should not result in 15m to display what it contained. Do you have indexes on the proper columns? What does that code look like?

[carpekd]

Here is my code to display the data from mysql (the display functions are just html templates)... please forgive my messy code

display_main_header('Dataset');

set_time_limit(0); // NO TIMEOUT

$db = account_db_connect();
  
  
				$get_dataset_info = $db->query("SELECT * FROM datasets WHERE dataset_id='".$dataset_id."'");
				$get_dataset_info_fetch = mysqli_fetch_assoc($get_dataset_info);
				$dataset_title = $get_dataset_info_fetch["title"];
				
				$get_fields = $db->query("SELECT * FROM dataset_cols WHERE dataset_id='".$dataset_id."'");
				$n=0;
				while ($fields = mysqli_fetch_array($get_fields, MYSQL_NUM)) {
					$col_id = $fields[0];
					$field_name = $fields[2];
					$field_name_array[$n]=$field_name;
					$col_list[$n]=$col_id;
					$n++;
				}
	display_dataset_header($dataset_title,$field_name_array);
	
	
  $get_rows = $db->query("SELECT * FROM dataset_rows WHERE dataset_id='".$dataset_id."'");
  if (!$get_rows) {
	display_error("Database Error");
	exit;
  }
		while ($row_list = mysqli_fetch_array($get_rows, MYSQL_NUM)) {
			$row_id = $row_list[0];
				$get_cols = count($col_list);
				$n=0;
				while ($n < $get_cols) {
					$col_id = $col_list[$n];
					$data_id = get_data_id($row_id, $col_id);
					$cell_data = get_data($data_id);
					$row_data_array[$n]=$cell_data;
					$n++;
				}
			display_dataset_results($row_data_array);
		}
		
		
		
display_dataset_footer();
	

 

Also, could you elaborate on the queuing system?

[requinix]

Its job is to make sure there aren't too many imports happening at once, but don't worry about it until the imports start becoming a problem (if they ever do).

Edited March 10, 2013 by requinix