Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

get_total_results() is slowing whole process #82

Open
wrabit opened this issue Feb 10, 2015 · 7 comments
Open

get_total_results() is slowing whole process #82

wrabit opened this issue Feb 10, 2015 · 7 comments

Comments

@wrabit
Copy link

wrabit commented Feb 10, 2015

By default, get_total_results() does a ci->db->get() which runs a SELECT *

My table has 50,000 results and it goes from a 1.8 second query to 0.2 if I change the select clause to the first column of my table.

@olarriga
Copy link

olarriga commented Apr 7, 2015

I have the same problem ... but worse. The query that counts the number of rows takes 2s and generates 200MB of traffic while the query to display the lines only takes 1ms.

@wrabit
Copy link
Author

wrabit commented Apr 8, 2015

I overcame this by editing Datatables.php:

  1. A new private variable:
private $count_column;
  1. A new function within the class:
    /**
    * WRA added function to set the count column
    *
    * @param string $column
    * @return mixed
    */
    public function set_count_column($column)
    {
      $this->count_column = $column;
      return $this;
    }
  1. And this in the get_total_results() function around line 436:
      if(strlen($this->count_column) > 0)
        $this->ci->db->select($this->count_column);
  1. And then in my controller:
$this->datatables->set_count_column('id'); 

@olarriga
Copy link

olarriga commented Apr 8, 2015

I replaced the last 2 lines of the get_total_results() by this line:
return $this->ci->db->count_all_results($this->table);

It looks run.

I will also try your solution. Thanks.

@olimortimer
Copy link
Contributor

@digitaloutback that sounds quite useful. Are you planning to submit it as a pull request?

@zazikhan
Copy link

I am new to datatables i have 94,000 rows and it take 8 to 9 secs to get loaded i think the problem is get_total_record function so what should i do make my records get fetched faster

@wrabit
Copy link
Author

wrabit commented Feb 21, 2016

Yes just folliw the instructions that I wrote. Also try to switch to serverside processing setting. Remember to also ensure any table join fields are indexed.

@zazikhan
Copy link

Let me explain fully i have server side enable "bServerSide"=true, also i tried your code which didnt work it was giving me the same result. than for testing purpose i modified my datatable library to retrieve only 10 records and nothing happened let me share my change to make it more clear .

` private function produce_output($output, $charset)
{
$aaData = array();
$rResult = $this->get_display_result();

  if($output == 'json')
  {
    $iTotal = 10;//$this->get_total_results();
    $iFilteredTotal = 10;//$this->get_total_results(TRUE);
  }`

so i disable the count functionality and i put static values so i understand one thing that problem isnt with this function $this->get_total_results();. Plus i have every column index which appears in join and where condition .plus one more thing that it work fine only in aws server and do not work fine in any other server i dont know whats happening

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants