large database

estudios

I have a large table so I need server side process if not it takes a lot time to load the data, but with server side I lost my filters.
Is there any way to use client side but not load the whole table??? How can I tell datatables that just load a part of the table??
For instance, if the table has a date field we may set datatables to load only the last year or so
Thanks

estudios

More info
I have 9000 row!!. It does not seems to be that big?
What can be causing the enormous delay??

allan

Are you using Editor's PHP Join class by any chance? Its current approach is really rather sub-optimal in how it tries to do things I'm sorry to say. What would probably be best is to perform the query directly on the database using a custom built query that exactly matches what you need - rather than having Editor's generic handling try to do it. This is the problem with Editor - it is very generic and therefore suboptimal. I'm planning to do some work on the Editor libraries to improve this in future as we discussed before, specifically for the join case, but at the moment, and I'm more than happy to give you an early release of them, but it will be next month before they are at all usable.

Until then, as I say, I think a custom query would be needed. As you say, 9000 rows is not that many - server-side processing should be able to cope with millions!

Allan

estudios

Allan, thanks a lot. Yes I am using Editor. I would love to use the query directly on the datatable. Could you please help me how to do it. May be a simple example or something to read
Thanks again

allan

The `sql`method of the Database class can be used to run queries directly against the database. There are a couple of examples of how it can be used int eh documentation here:
https://editor.datatables.net/docs/current/php/class-DataTables.Database.html#_sql

Regards,
Allan

estudios

Allan

I did just this in the php file...there is no error but the data do not show in datatable

$out = $db ->query ('select')
->table ('factprove')
->get ('fecha, numero' )
->order ('fecha desc')
->exec()
->fetchAll();

echo json_encode( $out );

allan

I doubt that is returning data in the format DataTables is expecting. Try setting the sAjaxDataProp option to be an empty string.

Allan

estudios

Allan, great! it works but I had to limit the query if not fatal error came up.
Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 35 bytes)

So there is still a problem with the number of rows, even using sql

Now I have a large amount of rows (1000 equal to the limit) on screen, not only the ten that should appear.

I do remember it happened to me, but I could not find your previous answer. May be if I solve this I can take off the limit (what do you think?

I am sorry for asking two times the same question !!!

Thanks again

tangerine

You can use PHP to increase available memory. For example:
ini_set( "memory_limit", "500M" ) ;

16777216 bytes isn't a lot.

allan

Heh no - 16M isn't a huge amount, but 500M sure is :-). You might run into problems if it is a high volume site.

You might want to try estimating how much RAM you will needed based on the contents of your tables. Perhaps try doubling the value until it works and see if that is acceptable. Having said that, the Editor libraries, as we know, are not massively efficient for joins. As I've mentioned, this is something I plan to work on, but I'm afraid I've not yet had a chance to do so.

Allan

tangerine

[quote]16M isn't a huge amount, but 500M sure is[/quote]

Good point :-) I just took the first example I found in my development version. Should have thought a bit more!

estudios

Thank you guys!!