Table loading speed issue

jayreis

Hello

I am using this DataTables code to make my html/php based table that pulls its contents from a mysql database be interactive.
I like that a user can easily filter and search for specific content in the table with this jquery code. However I am having a speed issue. The table contains 3,100 rows of data which of course is spread out over multiple pages. However because of the quantity of rows the table seems to load really slow. I optimized my query and after running the query in a phpmyadmin to test the query speed I narrowed it down to this datatables jquery code that is causing the slowness.

So I am looking for suggestions on how to make the table load the contents faster. Any suggestions would be helpful
Thanks

tangerine

I think you'll need to tell us more. 3,100 rows isn't enough to cause a significant slowdown, unless they are unusually long rows. What does your table look like?

jayreis

Well the table rows are a bit long but I am selecting specific columns from the table. So for example their are 45 columns but in my query I am only selecting 13 of the columns that I want to display/use on my php page.

jayreis

The sql when I run it in phpmyadmin is fast though

allan

Can you please link to a test case showing the issue, as requested in the forum rules and in the create new discussion form. At the moment I don't know if you are Ajax loading the data or not, client-side or server-side processing? Please also see: http://datatables.net/faqs#speed .

Allan

jayreis

Thank you for the response. I can not link to a test page because the system is hosted on a php/mysql server that is on a LAN and is not connected to the internet.
However I believe what I need to do is the serverside processing via Ajax/php/mysqli. However I am not sure how I would write the code.

First because my pages query has a join on three different tables and I do not see any clear cut way to use my query in the sample code seen here http://datatables.net/development/server-side/php_mysqli

Second I am not sure how to make the servside processing work.
My page with the table has this
[code]



Sys ID
Legacy ID
Client Name
Phone
Email
Updated On
YTD Pmts
Status
Agent
Checked In sys
Options






Loading data from server



[/code]

and at the very bottom of the page I have
[code]

$(function () {
$('#dt2').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./clientlist_processing.php"
});

});


[/code]

My clientlist_processing.php is the code from
http://datatables.net/development/server-side/php_mysqli

However I am getting a error message saying Json data could not be parsed. This is caused by json formatting error. When I look at the page is firefox firebug I do not see any json data coming through. So I am not sure what I am doing wrong? When I go to the clientlist_processing.php page I get an error saying
Error loading character set "utf8": not sure what would cause that error and I am thinking it might be the issue. I tried commenting out the line that says mb_internal_encoding('UTF-8'); but that did not do anything

jayreis

I am getting an error saying Error loading character set "utf8": when I go to my php processing page and I am not sure how to fix it. I tried commenting out a line that references utf8 but that did not fix anything

allan

I'm afraid I don't know what that error is as that isn't a script I wrote. If you comment out the set of the character set does that specific error go away?

Allan