jquery data tables

I'm looking for opinions on pagination using jquery datatables.
my thinking is that datatables are really bad for huge recordsets.
I believe one should page within the query itsself - not query for all records then just display a portion of those.
I'm looking for coherent reasons to use with my employer (who is trying to force me to use datatables)

for jquery people please provide reasons why it may be good

i'm hoping you guys know what I'm referring to - if not I can explain more.
LVL 25
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
We use ASP.Net and Oracle as a back end.  I wrote custom paging for that very reason.  Database servers should have a lot more horsepower than any web server.  I only bring back the number of rows to be displayed by the page.

This requires a little extra code to get the specific rows I'm after but it's worth it from an overall performance standpoint.

As with most things in life: Your mileage may vary.
dgrafxAuthor Commented:
thanks slightwv - your input is always appreciated.
I was hoping to get additional opinions to use as ammo against us using this thing.
The people around here have never heard of paging within the query instead of in the front end code - so its kinda like I'm making it up!
Cause how could I possibly put down something as popular as datatables!
slightwv (䄆 Netminder) Commented:
I'm not sure why your request for attention was denied.  But I'll post and you can re-request attention.

>>so its kinda like I'm making it up!

Been there.  Non-Believers can be easily swayed with hard numbers.  No one can argue actual performance numbers.  Who knows, they might actually be right in your specific situation.

Create a quick test.  One page using client-side paging.  One using database side paging.  Then capture performance.

Be sure to make it realistic:  Update/insert some rows during the test so the client-side doesn't cache the entire recordset for the entire time.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dgrafxAuthor Commented:
good point - thanks ...
Eddie ShipmanAll-around developerCommented:
the DataTables jQuery plugin also offers an ajax server-side processing solution that is much easier to use and handle very large datasets.
We have a DB of over 2.6 mil records and it is handled just fine with this plugin using server-side processing:
dgrafxAuthor Commented:
server side processing  encompasses a lot!
please define

just to clarify - yes ajax (of course) - are you paging within the query?
could you provide a bit more of an explanation please

Eddie ShipmanAll-around developerCommented:
What backend language are you using?
dgrafxAuthor Commented:
We are using ColdFusion with Oracle db
Eddie ShipmanAll-around developerCommented:
Ok, hold on for a little while I look something up.
Eddie ShipmanAll-around developerCommented:
Download the server-side script for CF here:

Now, modify your declaration of the DataTable sAjaxSource parameter
to call the server-side script above. Here is how I configured mine,
I use a file called datatable.php to process my server-side data:
  oUserTable = $('#userInvtable').dataTable({
            "iDisplayLength": 50,
            "bJQueryUI": true,
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "datatable.php",

Open in new window

You want to modify these values to reflect the columns you want returned from the table and the index column to use and the datasource to your data.
You may also need to modify the iDisplayLength to return the number of rows you actually want for each page but I think it is passed via the URL from
the declaration.
<cfset coldfusionDatasource = ""/>
<cfset listColumns = "engine,browser,platform,version,grade" />
<cfset sIndexColumn = "id" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />

Open in new window

The server-side script build the SQL dynamically and returns only the number of rows needed. Looking at the SQL in the example script, it seems that they
are using mySQL, so you will also need to redo the SQL to use PL-SQL.

Good luck...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eddie ShipmanAll-around developerCommented:
I use a file called datatable.php to process my server-side data:
The file you downloaded from the link can be called datatable.cf and use that in the sAjaxSource parameter.
dgrafxAuthor Commented:
Hi Eddie
thank you very much for taking the time to look that up and post.
doing it this way the concept is similar to what I've been doing without datatables, and had posted for opinions on why not to use datatables.

since then I did spend some time one day to modify some of the inner workings of datatables and saw that there is light at the end of the tunnel.

when I get back to it though I will be reworking it to use a query i've written.
I use one query that provides both paging and total recordcount.
the query in the example uses 2 queries - one to get the data and the other to get the recordcount.
but other than that shortcoming I can see that its a lot better than I thought it was - IF its used correctly.

I wonder how many out there are using it correctly?
Thanks ...
Eddie ShipmanAll-around developerCommented:
In reality, it could be done with one query, if you are using mySQL. It would need to be reworked a little to make it work but it can be done.
mySQl uses the SQL_CALC_FOUND_ROWS to get the total number of rows without the filtering (LIMIT) and then FOUND_ROWS() to get the number
dgrafxAuthor Commented:
Thanks for the postings guys!
Was hoping that more would have posted - but oh well ...
I did learn that datatables can be modified to my way of thinking (pagination within the query - not the front end code).
I don't believe that a lot of people are doing that - I don't know though ...

Eddie: thanks for the work but I need to say that I was not looking for "how" to do a query that paginates - but "can" datatables use query pagination.
Eddie ShipmanAll-around developerCommented:
Datatables builds the SQL based on the paging that is sent to it.
dgrafxAuthor Commented:
"paging" is the act of breaking up the entire recordset into smaller more manageable pieces.
So don't know what you mean by "based on the paging that is sent to it".

Also - a wise person would write the sql oneself - NOT have datatables write it.

Again I should point out that this wasn't a question on how to write sql.
Eddie ShipmanAll-around developerCommented:
I understand what the question is about. I was saying that the datatables plugin generates the sql needed to return subsets of data based on values sent to it. Since it was written basically for mySQL, it uses the LIMIT clause to "page" the recordset.

For example...

When you click on Page 2, and your paging is set for 10 records, the plugin builds the sql with a limit clause like this:
LIMIT 20, 10

The first parameter in the LIMIT clause is the offset into the recordset.

That is how it handles paging internally.

Here would be how to do that using Oracle:

dgrafxAuthor Commented:
Eddie - you are still posting "how-to" tips and now even a link!
This is proof that you do NOT get the meat of this post.

And your statements repeatedly trying to explain the same thing over and over really makes a person wonder about you.

And you keep mentioning mysql ???
Everyone that writes sql knows that there are syntax differences between different db servers !!!
You seem to be stuck on explaining that the example is for mysql !!!


to clear up a few thing that maybe i didn't post:
myself and a LOT of other developers have been paging within our queries for years - YEARS!!!
AND I write sql for ms sql - oracle - and mysql - i know the differences in syntax!

but keep your tips coming in on "How SQL works"
Eddie ShipmanAll-around developerCommented:
<quote>So don't know what you mean by "based on the paging that is sent to it".<quote>
You didn't have to be rude. , I was explaining *that* to you.

If you don't like how jQuery DataTables works, then don't use it.
dgrafxAuthor Commented:
slightwv - I hear ya - I should have never responded after certain things were obvious ...

but I would have thought also that you would have posted a comment - not as an administrator, but as a participant

thanks guys - have a good one!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.