+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Approach: Reducing the number of SQL calls (/showthread.php?tid=47059)
Approach: Reducing the number of SQL calls - El Forum - 11-24-2011
[eluser]bunal[/eluser]
Well you might not want to use ORM for your application and your application may be making the same SQL calls (in different models/libraries or ...) multiple times when it tries to show/render the page. Actually better to have a memcache or similar layer but here it is anyway.
Here is my approach for this situation:
Extend the CI_Model class to store the results of the SQL queries
Code:
class MY_Model extends CI_Model
{
// Store the data generated from SQL
private $_sql_caches = array();
public function __construct(){
parent::__construct();
}
public function get_sql_cache($key=FALSE){
if($key==FALSE){
$key = sha1($this->db->get_compiled_select("",FALSE));
}
if(isset($this->_sql_caches[$key])){
$this->db->reset_query();
return $this->_sql_caches[$key];
}
return FALSE;
}
public function set_sql_cache($key,$data){
if($key==FALSE){
$key = sha1($this->db->last_query());
}
$this->_sql_caches[$key] =$data;
return TRUE;
}
}Example
Code:
class Category_model extends MY_Model {
function get_category_detail($category_slug = FALSE, $options = array()) {
$this -> db -> from("category");
// Some more active records here
if(!$data = $this -> get_sql_cache(FALSE)) {
$data = $this -> db -> get() -> row_array();
$this -> set_sql_cache(FALSE,$data);
}
return $data;
}
}In basic having a singleton approach. Hope it helps someone.