Hi, Paul.

Sorry for a late reply.

If i understood, your MySQL Query from one of the SQL tables of our plugin has caused the server's disk space to run out of Memory, looking from what your hosting has written?

This means that it is not an issue from our plugin,

but rather a server issue, or in other words, the server is not capable to handle all the data in the way how you configured the table and the Query.

We will do our best to advise what you can do to make a lesser load on the hosting server.

-

And this happened after you added a checkbox filter to one of the table's columns?

Generally speaking, with our tables,

the loading speed and the ammount of memory that will be used depends on a lot of factors -

 your hosting plan, server's performance, amount of other data on the page, internet speed, etc. 

For example, you can take a look at the table on our documentation, which contains around 2.600.000 rows, and see how it loads. 

The more data the table has, the more chance that it will impact the loading time.

-

Can you tell me, how many rows does this SQL table have?

If it has more than 2 thousand rows, the server-side processing will be forced.

Please check if server-side processing is enabled in the table's back-end settings/Data Source :

If it is, then check under Display settings, what is the number of 'Default rows per page'?

If you set this to "All" and if you have a lot of rows, like more than a thousand rows,

try to reduce this value - set it to 10 rows per page - it will create pagination on the table,

so users have to go through several pages to see all the data, but this will lessen the load on the hosting server,

because AJAX calls will load only 10 rows at a time.

Let me know if that helps.

-

You can also check, in all of the String Columns,

how many unique filtering values are set to be loaded for available filtering.

If you have, for example - a string column that you set as a selectbox/multiselectbox, 

and if it has more than 50 possible filtering values,

you can go into that column setting, and check what is set for "Number of possible values to load" – 

Here you can define how many possible values per page will be loaded in selectbox filter and editor inputs.

 It is recommended not to be set for All if you have more than 50 possible values for this column.

So, check if that might be the case, try to lower the value and see if it helps the server load.

-

Also, it is possible for other plugins to affect the loading speed.

We had a couple of cases with users who have an "optimization plugin" with specific settings, 

such as  'SiteGround Optimizer', for example.

These plugins have certain settings that can negatively affect the performance of our tables.

If there is any JS minification going on,  there can be other settings, I can't say exactly,

but something to be aware of.

If you wish to do troubleshoot testing to check if an optimization/caching plugin might be slowing down tables,

you can try to disable all plugins and just leave wpDataTables - then clear all cache and reload the page, see if it loads faster.

Then, if any optimization plugin Is used, try to revert all its settings to default, and if you use server-side tables ( such as SQL or Manual tables) ,  then exclude the pages where you have tables from caching.

-

Let us know if that helps.

Thank you.

Hi, Paul.

Thank you for confirming these details.

It is good that you already set the default number of rows per page to load at the minimum - 10,

and the available filtering values to 10.

But for the filtering, the checkbox will always load all available values,

so could you consider changing that column filter to a multiselectbox instead and see if that helps?

-

Can you also check what time interval did you set as the "auto refresh" on the SQL Table?

For example, suppose if you have set this to 5.

 In that case, it means that the calls towards the server which is holding that external Database with SQL table(s) in question is going to be called every 5 seconds,

so see if that is the case, set this to blank or zero

 - then the table will only update when the page is refreshed, with the latest data,

it will not automatically make calls to the DB server.

-

Also, when you say that this SQL table pulls data from another database,

is this a remote Database connection from another server,

or is it the same hosting server that the WordPress database is on?

-

What you can try is, you could copy this database table ( or multiple tables, since you use the JOIN),

and import these SQL tables that are needed for the Query,  to the same database where you have WordPress,  and make a Query from the WP Database,

see if that helps.

-

You can also try to make the SQL Query in our plugin simpler for processing,

by first making an SQL VIEW in the database,

then in our plugin call it with a simple Query like :

"SELECT * FROM View_Name"

If you need help how to do that, we have this Video Tutorial example how you can use a VIEW in our plugin.

-

In the end, if none of this helps, you can check what is the current PHP Memory limit on the server;

and see if the hosting server could increase the limit value.

For example, if it is currently at 256 MB,  you could increase it to 512 MB, etc.

-

Let us know if that helped.

Hi, Paul.

I am not sure i understood the steps you did, and the issue you are seeing now.

If you created the SQL VIEW in your database,

and now you create a new SQL Table in our plugin with Query as :

SELECT * FROM View_Name

Can you check if you have accent graves around the View Name?

For the MySQL engine, we are dynamically adding the accent grave ( ` ), so there's no need to use it around the table name in the query. If you were to delete them, searching and filtering should work just fine.

I just made a simple Video example how i make a basic SQL View from a JOIN with two tables,

my Query was like this in the database PHPMyAdmin :

and later i call this to make an SQL Query table , sending the Video test as attachment.

I hope this helps.

Let me know if you also did everything like that,

and if you can still see issues, could you please record your screen and send me a Video?

If the video is too large for Ticksy, you can upload it to weTransfer and send me the link.

If it contains any sensitive Data, mark reply as PRIVATE, for safety reasons.

-

And i wanted to confirm one detail.

If i understood you from before,

all your other SQL tables are being pulled from the same Database where you have WordPress.

and just this table in question pulls data from an external server Database?

Could you perhaps try to export/make an SQL dump of that table from that database,

import it to the same Database where you have WordPress, make an SQL VIEW there,

make a new SQL Table in that database, and see if that helps?

Thank you.