douquanzhan0315
2012-10-23 16:08
浏览 46
已采纳

从mysql数据库查询使用php时加速csv导出

Ok, so i've got a web system (built on codeigniter & running on mysql) that allows people to query a database of postal address data by making selections in a series of forms until they arrive at the selection that want, pretty standard stuff. They can then buy that information and download it via that system.

The queries run very fast, but when it comes to applying that query to the database,and exporting it to csv, once the datasets get to around the 30,000 record mark (each row has around 40 columns of which about 20 are all populated with on average 20 chars of data per cell) it can take 5 or so minutes to export to csv.

So, my question is, what is the main cause for the slowness? Is it that the resultset of data from the query is so large, that it is running into memory issues? Therefore should i allow much more memory to the process? Or, is there a much more efficient way of exporting to csv from a mysql query that i'm not doing? Should i save the contents of the query to a temp table and simply export the temp table to csv? Or am i going about this all wrong? Also, is the fact that i'm using Codeigniters Active Record for this prohibitive due to the way that it stores the resultset?

Pseudo Code:

$query = $this->db->select('field1, field2, field3')->where_in('field1',$values_array)->get('tablename');
$data = $this->dbutil->csv_from_result($download_query, $delimiter, $newline); // the some code to save the file
$this->load->helper('download');
force_download($filename, $filedata);

Any advice is welcome! Thank you for reading!

图片转代码服务由CSDN问答提供 功能建议

好的,所以我有一个网络系统(建立在codeigniter和mysql上运行),允许人们 通过以一系列形式进行选择来查询邮政地址数据的数据库,直到它们到达想要的选择,非常标准的东西。 然后他们可以购买该信息并通过该系统下载。

查询运行速度非常快,但是当将查询应用于数据库并将其导出到csv时, 数据集大约达到30,000个记录标记(每行有大约40列,其中大约20个,每个单元平均填充20个字符数据),可能需要5分钟才能导出到csv。

所以,我的问题是,缓慢的主要原因是什么? 来自查询的数据的结果集是如此之大,以至于它遇到内存问题? 因此,我应该为这个过程留出更多的记忆吗? 或者,有没有一种更有效的方法从我不做的mysql查询导出到csv? 我应该将查询的内容保存到临时表,只需将临时表导出到csv吗? 或者我是否认为这一切都错了? 此外,由于存储结果集的方式,我使用Codeigniters Active Record这个禁止的事实?

Pseudo Code:

  $ query = $ this-> db-> select('field1,field2,field3') - > where_in('field1',$ values_array) - > get('tablename'); 
  $ data = $ this-> dbutil-> csv_from_result($ download_query,$ delimiter,$ newline);  //保存文件的一些代码
 $ this-> load-> helper('download'); 
force_download($ filename,$ filedata); 
   
 \  n 

欢迎任何建议! 感谢您阅读!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongre9937 2012-10-30 14:33
    已采纳

    Ignoring Codeigniter for a moment, you basically have three options for exporting CSV using PHP:

    • To disk - typically the slowest option
    • To memory - typically the fastest option
    • Directly to the browser

    In your case I would skip any built-in Codeigniter CSV functions and try streaming directly to the browser (see link above for a complete example).

    评论
    解决 无用
    打赏 举报
  • 查看更多回答(1条)

相关推荐 更多相似问题