duanluanlang8501 2011-11-28 23:51
浏览 17
已采纳

使用SimplePie使用RSS源进行优化的数据库插入 - 需要创意

So basically I have just benchmarked my update_feeds controller and found that the amount of sql queries that are run are shocking. I am looking for a way to optimise the process of fectching multiple feeds and then insert the data into a table (title and url)

Currently there are 193 Feeds in the DB that I fetch the URL's for and then I process these one by one checking and insert data of them into another table. The problem is simplepie goes through and inserts every item.

So I am ending up with QUERIES: 3297 Total Execution Time 202.8051

I am looking for a way to optimise this process does anyone have any tips? I will post some code. Thanks.

Controller for fetching the feeds

          $this->output->set_profiler_sections($sections);
        $this->load->library('simplepie');
        //$this->simplepie->cache_location = BASEPATH .'cache';
        $this->load->model('FeedModel');
        $this->load->model('FeedItemModel');
        $feeds = $this->FeedModel->get_feed_update_urls();
        foreach ($feeds as $feed_id => $feed_url) {
            $this->simplepie->set_feed_url($feed_url);
            //$this->simplepie->set_cache_duration(0);
$this->simplepie->set_timeout(0);
            $this->simplepie->init();
            $items = $this->simplepie->get_items();
            foreach ($items as $item) {
                $this->FeedItemModel->load($feed_id, md5($item->get_id()));
                $this->FeedItemModel->link = $item->get_permalink();
                $this->FeedItemModel->title = $item->get_title();
                $this->FeedItemModel->created_time = $item->get_date('Y-m-d H:i:s');
                $this->FeedItemModel->save();
            }
        }

Model for inserting feeds

function save() {
        if ($this->_id !== false) {
            $this->db->query('UPDATE feed_items SET link=?, title=?, created_time=? WHERE id=?', array($this->link, $this->title, $this->created_time, $this->_id));
        } else {
            $this->db->query('INSERT INTO feed_items(feed_id, remote_id, link, title, created_time, updated_time) VALUES (?, ?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE remote_id=remote_id', array($this->feed_id, $this->remote_id, $this->link, $this->title, $this->created_time, $this->remote_id));
            $this->_id = $this->db->insert_id();
        }
    }
  • 写回答

1条回答 默认 最新

  • duanjuduo4573 2011-11-29 00:28
    关注

    Instead of loading every single time and then saving, you should instead do an update (and then insert when update doesn't find rows). That will reduce by a single query per feed item.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题