⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

7k records slow loading/processing using flexigrid



larasmith

larasmith
  • profile picture
  • Member

Posted 16 April 2015 - 05:13 AM

I have around 7k records and im using flexigrid as a theme. It takes time to load. During add/edit and then update and go back to list it would take about 25 seconds to load. What can I do to make the loading time faster?

 

here is my code:

	public function qb(){
 		/*Create ajax_grocery_CRUD instead of grocery_CRUD. This extends the functionality with the
 		 field_set_defaults keeping all functionality as well (I extended this to grocery_CRUD)*/		
		$crud = new ajax_grocery_CRUD();


		// Set table name
		$crud->set_table('tblqb'); 
						
		// Set this name instead of 'Record' in prompt
		$crud->set_subject('Question');
		
		//* These displays custom name instead of field name in columns	
		$crud->display_as('qb_id','Question ID');						
		$crud->display_as('qb_prog_id','Program');
		$crud->display_as('qb_prog_m_Name_id','Marina Program Name');
		$crud->display_as('qb_funct_id','Function');
		$crud->display_as('qb_course_DescTitle_id','Descriptive Title');
		$crud->display_as('qb_course_RefNo_id','Reference No.');			
		$crud->display_as('qb_course_Title_id','Title');
		$crud->display_as('qb_lo_name_id','Terminal Learning Outcome');
		$crud->display_as('qb_topic_name_id','Enabling Outcome');
		$crud->display_as('qb_sem_id','Semester');
		$crud->display_as('qb_qtype_id','Question Type');
		$crud->display_as('qb_coglvl_id','Cognitive Level');
		$crud->display_as('qb_di_id','Difficulty Level');
		$crud->display_as('qb_encoder_id','Encoder');
		$crud->display_as('qb_author','Author');
		$crud->display_as('qb_reviewed_id','Reviewed');
		$crud->display_as('qb_remarks_id','Remarks');
		$crud->display_as('qb_status_id','Status');
		$crud->display_as('qb_published_id','Published');
		$crud->display_as('qb_publishD8','Date Published');
		$crud->display_as('qb_PilotTest_id','Pilot Tested');
		$crud->display_as('qb_PilotTestD8','Pilot Test Date');
		$crud->display_as('qb_Q','Question');
		$crud->display_as('qb_opt1','Option 1');
		$crud->display_as('qb_opt2','Option 2');
		$crud->display_as('qb_opt3','Option 3');
		$crud->display_as('qb_opt4','Option 4');													
		$crud->display_as('qb_Ans','Correct Answer');
		$crud->display_as('qb_D8Cre8d','Date Created');
		$crud->display_as('qb_D8Upd8d','Last Update');			
		//*	
		
		// Displays the enlisted fields on the datagrid
		$crud->columns('qb_id','qb_prog_id','qb_prog_m_Name_id', 'qb_funct_id', 'Competence', 'qb_course_DescTitle_id', 'qb_course_RefNo_id', 'qb_course_Title_id', 'qb_lo_name_id', 'qb_topic_name_id', 'qb_sem_id', 'qb_qtype_id', 'qb_coglvl_id','qb_di_id', 'qb_reviewed_id', 'qb_remarks_id', 'qb_status_id','qb_author', 'qb_Q', 'qb_opt1', 'qb_opt2', 'qb_opt3', 'qb_opt4', 'qb_Ans','qb_encoder_id','qb_D8Cre8d','qb_D8Upd8d');

		// This is the set of code for hiding/showing OPTIONS depending on QType -*begin
		$f=array('qb_prog_id','qb_prog_m_Name_id', 'qb_funct_id', 'Competence', 'qb_course_DescTitle_id', 'qb_course_RefNo_id', 'qb_course_Title_id', 'qb_lo_name_id', 'qb_topic_name_id', 'qb_sem_id', 'qb_qtype_id', 'qb_coglvl_id','qb_di_id', 'qb_reviewed_id', 'qb_remarks_id', 'qb_status_id','qb_author', 'qb_Q', 'qb_opt1', 'qb_opt2', 'qb_opt3', 'qb_opt4', 'qb_Ans','qb_D8Cre8d','qb_D8Upd8d', 'secret');

	// Makes fields visible in add/edit mode
		$crud->fields($f);						
		$crud->callback_add_field('secret',array($this,'qb_CBae_showhideMCQopt'));
		$crud->callback_add_field('qb_opt1',array($this,'qb_afcb_opt1'));
		$crud->callback_add_field('qb_opt2',array($this,'qb_afcb_opt2'));
		$crud->callback_add_field('qb_opt3',array($this,'qb_afcb_opt3'));
		$crud->callback_add_field('qb_opt4',array($this,'qb_afcb_opt4'));
		$crud->callback_edit_field('secret',array($this,'qb_CBae_showhideMCQopt'));
		$crud->callback_edit_field('qb_opt1',array($this,'qb_efcb_opt1'));
		$crud->callback_edit_field('qb_opt2',array($this,'qb_efcb_opt2'));
		$crud->callback_edit_field('qb_opt3',array($this,'qb_efcb_opt3'));
		$crud->callback_edit_field('qb_opt4',array($this,'qb_efcb_opt4'));		
		
		// -*end

		$crud->field_type('qb_D8Cre8d','invisible');
		$crud->field_type('qb_D8Upd8d','invisible');

		// Makes fields required in add/edit mode
		//PUT 'qb_di_id' IN THE LAST PART OF THE REQUIRED FIELDS TO PREVENT ERROR 500,
		$crud->required_fields('qb_prog_id','qb_prog_m_Name_id', 'Competence','qb_funct_id', 'qb_course_DescTitle_id', 'qb_course_RefNo_id', 'qb_course_Title_id', 'qb_lo_name_id', 'qb_topic_name_id', 'qb_sem_id', 'qb_qtype_id', 'qb_coglvl_id','qb_di_id',  'qb_remarks_id', 'qb_status_id', 'qb_reviewed_id', 'qb_author', 'qb_Q', 'qb_opt1', 'qb_opt2', 'qb_opt3', 'qb_opt4', 'qb_Ans');

		
		// Sets relation with dependent dropdown list
		$crud->set_relation('qb_encoder_id','tblusers','UserName');

		// Sets relation with dependent dropdown list (Program->Function)
		$crud->set_relation('qb_prog_id','tblprogram','prog_Name');
		$crud->set_relation('qb_prog_m_Name_id','tblprogram','prog_m_Name');
		$crud->set_relation('qb_funct_id','tblfunction','funct_Name'); 
		$crud->set_relation_dependency('qb_funct_id','qb_prog_id','funct_prog_id');

		// Sets Competence  
		$crud->set_relation_n_n('Competence', 'tblqb_competence', 'tblcompetence', 'qb_id', 'compete_id', "{compete_Name}", 'priority');

		// Sets relation with dependent dropdown list (Desctitle)
		$crud->set_relation('qb_funct_id','tblfunction','funct_Name');
		$crud->set_relation('qb_course_DescTitle_id','tblcourse','course_DescTitle');
		$crud->set_relation_dependency('qb_course_DescTitle_id','qb_funct_id','course_funct_id');

		// Sets relation with dependent dropdown list (RefNo)
 	    $crud->set_relation('qb_funct_id','tblfunction','funct_Name');
		$crud->set_relation('qb_course_RefNo_id','tblcourse','course_RefNo');
/*		$crud->set_relation_dependency('qb_course_RefNo_id','qb_course_DescTitle_id','course_id');*/
		$crud->set_relation_dependency('qb_course_RefNo_id','qb_funct_id','course_funct_id');
		
		// Sets relation with dependent dropdown list (CourseTitle)
		$crud->set_relation('qb_funct_id','tblfunction','funct_Name');
		$crud->set_relation('qb_course_Title_id','tblcourse','course_Title');
/*		$crud->set_relation_dependency('qb_course_Title_id','qb_course_RefNo_id','course_id');*/
		$crud->set_relation_dependency('qb_course_Title_id','qb_funct_id','course_funct_id');

		// Sets relation with dependent dropdown list (LO)
		$crud->set_relation('qb_course_Title_id','tblcourse','course_Title');
		$crud->set_relation('qb_lo_name_id','tbllo','lo_name');
		$crud->set_relation_dependency('qb_lo_name_id','qb_course_Title_id','lo_course_Title_id');

		// Sets relation with dependent dropdown list (Topic)
		$crud->set_relation('qb_course_Title_id','tblcourse','course_Title');
		$crud->set_relation('qb_topic_name_id','tbltopic','topic_name');
		$crud->set_relation_dependency('qb_topic_name_id','qb_course_Title_id','topic_course_Title_id');

		// Sets dropdown list (Semester)
		$crud->set_relation('qb_sem_id','tblsem','Sem');

		// Sets dropdown list (QType)
		$crud->set_relation('qb_qtype_id','tblqtype','QType');

		// Sets relation with dependent dropdown list (Cognitive Level)		
		$crud->set_relation('qb_coglvl_id','tblcoglvl','coglvl_Name');
		$crud->set_relation('qb_di_id','tbldi_assgn','di_assgn_Name');
		$crud->set_relation_dependency('qb_di_id','qb_coglvl_id','di_assgn_di_id'); //solved through ajax
	
		// Sets dropdown list (Remarks)
		$crud->set_relation('qb_remarks_id','tblqrem','qrem_Name');	

		// Sets dropdown list (Status)
		$crud->set_relation('qb_status_id','tblstatus','status_Name');	

		// Sets dropdown list (Reviewed bool)
		$crud->set_relation('qb_reviewed_id','tblbool','bool_value');

		// Sets dropdown list (Published bool) then turn it as default value as text
		$crud->set_relation('qb_published_id','tblbool','bool_value');		

		// Sets dropdown list (PilotTest bool)  then turn it as default value as text
		$crud->set_relation('qb_PilotTest_id','tblbool','bool_value');

	    $crud->callback_before_insert(array($this, 'qb_CBbi_CreateDate'));// Calls funct for creation date
	    $crud->callback_before_update(array($this, 'qb_CBbu_ModDate'));   // Calls func for last update
	    $crud->callback_after_insert(array($this, 'qb_log_encoder'));     // Calls funct for encoder name,published & Ptested

		// Sets the Question to be unique to avoid duplication
		$crud->unique_fields('qb_Q');

		//Removes add feature
		$crud->unset_add();

		//Removes the print feature
		$crud->unset_print();
		
		//Removes the export feature
		$crud->unset_export();
		
		//Removes the export feature
		$crud->unset_read();	//Prevents using the view button due to conflict in auto hide of options by LARA
		
		//**** These displays the output in the page
		$output = $crud->render();		


     	$state = $crud->getState();
    	$state_info = $crud->getStateInfo();
    	
		if($state == 'edit')
	    {
	        //This updates the encoder of the question upon edit mode.
	        $primary_key = $state_info->primary_key;
	        $data = array(
			   'qb_encoder_id' => $this->session->userdata('username_id'),
			);
			$this->db->where('qb_id',$primary_key);
 			$this->db->update('tblqb',$data);
	    }

		//**** These displays the output in the page	    
		$this->_view_output($output);
		//****
	}

I have been looking to forums and the suggestion that I'm always getting is to use flexigrid instead of datatables but I'm already using flexigrid... Please help... Thank you very much!


cdaganache

cdaganache
  • profile picture
  • Member

Posted 21 June 2015 - 06:19 AM

change theme from flexigrid to datatables..settle problem =')


Paul Savostin

Paul Savostin
  • profile picture
  • Member

Posted 21 June 2015 - 08:54 AM

change theme from flexigrid to datatables..settle problem =')

Why you think the problem will be settle!?? I think would be much worse cause in list state in datatables theme all records load on one page!

To larasmith, try write same request using pure phpmyadmin, see requested time,

if it will be smth about time when you using GC then you need to improve just your database request, if not - than problem in GC and you need using custom model or smth...


At first look you have many many many relation!


cdaganache

cdaganache
  • profile picture
  • Member

Posted 22 June 2015 - 01:27 AM

To Paul,

 

oh, i'm sorry if my answer disturbing you..but i also have the same problem.. i have data around 8k when i change the theme from flexigrid to datatables the data load fast before this. i'm sorry because i'm newbie here and just new about cI  :)

Hope you can help and guide me also  :D


Paul Savostin

Paul Savostin
  • profile picture
  • Member

Posted 22 June 2015 - 09:07 AM

As I sad I think the main problem that is too many set_relation:) No problem ask on the forum and mb I can help you


Kaabi

Kaabi
  • profile picture
  • Member

Posted 08 August 2015 - 06:05 AM

GC is load the whole records to flexigrid or datatable  the more join(set_relation) along with records the more records GC retrieve from mysql make it slower to page to load.

I believe you only need to build your own crud for large rows page unless GC is gonna rework on only getting rows by page limit on each pagination


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 10 August 2015 - 12:08 PM

i doubt it is loading all the records at a time - i cant comment cuz i have not seen it soo keenly .. but u can do the same using profiling with GC - there at the first page u will be able to see up with all the queries that got generated. If it dose - its sad - if it don't - then its some other area you need to focus.

 

i will still recommend the tables to be indexed up properly to optimize the query response time ..!!

If not done already - index up the fields that are in relation .. ex... product_id (which is a link to a product in products table in order_items table .... and so on). This should truely improve the performance.

 

Second option is - if u dont wana use so many joins ... use callbacks .. to fetch the record for set of fields

Before you opt in for this choice, do check which joins are making more reads / time consumption ... you can check the same by using the extracted query from profiler and understanding the query operations by using explain in mysql ...

 

i know this is an exhaustive way of looking at a solution like this but i know this will help many developers like my pal (Paul :D) .. 

I wish many of my developer fellows can take up the benefit of the process how to trace down the slow queries .. and how to work around on alternatives ...

 

Happy GCing :)


Kaabi

Kaabi
  • profile picture
  • Member

Posted 14 August 2015 - 19:06 PM

i doubt it is loading all the records at a time - i cant comment cuz i have not seen it soo keenly .. but u can do the same using profiling with GC - there at the first page u will be able to see up with all the queries that got generated. If it dose - its sad - if it don't - then its some other area you need to focus.

 

i will still recommend the tables to be indexed up properly to optimize the query response time ..!!

If not done already - index up the fields that are in relation .. ex... product_id (which is a link to a product in products table in order_items table .... and so on). This should truely improve the performance.

 

Second option is - if u dont wana use so many joins ... use callbacks .. to fetch the record for set of fields

Before you opt in for this choice, do check which joins are making more reads / time consumption ... you can check the same by using the extracted query from profiler and understanding the query operations by using explain in mysql ...

 

i know this is an exhaustive way of looking at a solution like this but i know this will help many developers like my pal (Paul :D) .. 

I wish many of my developer fellows can take up the benefit of the process how to trace down the slow queries .. and how to work around on alternatives ...

 

Happy GCing :)

 

I though in first that is load whole rows in one but I was wrong, but after I enabled profiler for 1 table contain only 2 fields which are name and value both varchar and no relations.

but I got shocked to see over 23 queries just for list. and in other tables with 1 or 2 relations is get up to 55 queries. queries need to be optimized for quicker results.

0.0005  	SHOW TABLES FROM `nac` 
0.0006  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0002  	SELECT `flexpaper`.*
FROM `flexpaper`
 LIMIT 25 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0002  	SELECT *
FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0007  	SELECT `flexpaper`.*
FROM `flexpaper`
 LIMIT 25 
0.0005  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0002  	SELECT *
FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0005  	SHOW COLUMNS FROM `flexpaper` 
0.0002  	SELECT `flexpaper`.*
FROM `flexpaper`
 LIMIT 25 
0.0005  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0002  	SELECT *
FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0004  	SHOW COLUMNS FROM `flexpaper` 
0.0079  	SELECT COUNT(*) AS `numrows`
FROM `applicant`
WHERE `current_phase` = '0'
AND `status` = '1' 

Kaabi

Kaabi
  • profile picture
  • Member

Posted 14 August 2015 - 19:13 PM

I got like 388 rows has 4 relations is load in 11.48 secs without any cache over 59 queries been executed, but what I've noticed that GC with joining is doesnt use any limit is grab all records also I found that most of queries are duplicated 2-3 times.

3.4925  	SELECT subjects.*, (SELECT GROUP_CONCAT(DISTINCT grading_values.grading_name) FROM grading_values LEFT JOIN subjects_grading ON subjects_grading.grading_id = grading_values.id WHERE subjects_grading.subject_id = `subjects`.id GROUP BY subjects_grading.subject_id) AS grading
FROM `subjects`
LEFT JOIN `grades` as `j5c853be8` ON `j5c853be8`.`id` = `subjects`.`grade_id`
LEFT JOIN `subjects` as `j87010369` ON `j87010369`.`id` = `subjects`.`enrichment_subject`
WHERE `subjects`.`archive` =0  

Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 17 August 2015 - 14:22 PM

Well joins are technically not meant to limit the data while joining. The purpose of the same is to get the data of the ids connected to the master data. So that's fine. Well look up for indexing as an option. Also lookup to optimize the mysql server using some fine tuning options. Here's one of such option .. ofcourse you can look for alternatives.. but this is 1 of the refference that can be used up.

http://www.mgt-commerce.com/blog/magento-on-steroids-best-practice-for-highest-performance/

 

Happy GCing :)


Kobus

Kobus
  • profile picture
  • Member

Posted 23 August 2015 - 20:37 PM

Why you think the problem will be settle!?? I think would be much worse cause in list state in datatables theme all records load on one page!

To larasmith, try write same request using pure phpmyadmin, see requested time,

if it will be smth about time when you using GC then you need to improve just your database request, if not - than problem in GC and you need using custom model or smth...


At first look you have many many many relation!

 

I had a similar issue once - my DB table indexes were messed up. With proper indexes, my GC worked well with flexigrid.