Server Side Pagination sql server and datatables

maniya

Hi, I have the datatables Successfully Implemented with my Coldfusion code. But there is a Problem, I am unable to use the sql server fetch offset with it,

right now my query returns around 5000000 records without using offset which is usually taking a lot off time , i am trying to show 10 at a time so query should not time out and it should on click of next page start with the offset and show 10 rows only

This is my code

Jquery Code

    $(document).ready(function() {
        $('#ButtonSubmit').on('click', function (e) {
            e.preventDefault();
            $.ajax({
                url: "getheaders.cfm",
                cache: false,
                data : $('#form').serialize(),
                method: "post",
                success: function(response){
                    $('.mytable').html(response);
                }
            }).done(function(data) {
                $('.table').show();
                $(".table").DataTable({
                    "bFilter": true,
                    "serverSide": true,
                    "columns": [
                        { "data": "name", "title": "Name", "autoWidth": true },
                        { "data": "fname", "title":"First Name", "autoWidth": true }
                    ],
                    "ajax": {
                        "url" : "datatables.cfm",
                        "type" : 'post'
                    },
                    "language": {
                        "processing": "Loading..."
                    }
                });
            })
        });
    });

now the coldfusion code

<cffunction name="records" access="remote" returnformat="json" returntype="any" output="false">

        <cfparam name="draw" default="1" type="integer" />
        <cfparam name="start" default="0" type="integer" />
        <cfparam name="length" default="10" type="integer" />
        <cfparam name="search" default="" type="string" />

        <cfset var aData = arrayNew(1) />
        <cfset var sGridData = StructNew() />
        <cfif len(form["search[value]"]) gt 0>
            <cfset search=form["search[value]"]>
        </cfif>

        <cfset var rsQuery = ''>

        <cfset var listColumns = 'name,firstname'>
        <cfquery name="rsQueryData" datasource="#Application.dsLocal#">

            ;WITH cte AS(
                SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateCreated DESC) AS rn,
                    name,firstname
                FROM table1 
                WHERE 1=1

                    <cfif len(trim(search))>
                    AND
                        ( 
                        <cfloop list="#listColumns#" index="thisColumn">
                        <cfif thisColumn neq listFirst(listColumns)> 
                        OR 
                        </cfif>
                        #thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />    
                        </cfloop>
                        )
                    </cfif>
                    order by 2 desc 
                    OFFSET val(start+1) ROWS FETCH NEXT #length# ROWS ONLY;

            UNION
                SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY datecreated desc ) AS rn,
                    name,firstname
                FROM table2 
                WHERE 1=1

                    <cfif len(trim(search))>
                    AND
                        ( 
                        <cfloop list="#listColumns#" index="thisColumn">
                        <cfif thisColumn neq listFirst(listColumns)> 
                        OR 
                        </cfif>
                        #thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />    
                        </cfloop>
                        )
                    </cfif>
                    order by 2 desc 
                    OFFSET val(start+1) ROWS FETCH NEXT #length# ROWS ONLY;


            )
            SELECT *  
            FROM cte WHERE rn = 1 
        </cfquery>

        <!--- Loop our results, implementing our filtering and pagination and putting into an array to be returned --->
        <cfoutput query="rsQueryData" startrow="#val(start+1)#" maxrows="#val(length)#">
            <cfset sGridData = structNew() />
                <cfset sGridData['name'] = rsQueryData['name'][currentRow] />
                <cfset sGridData['firstname'] = rsQueryData['FirstName'][currentRow]/>

            <cfset ArrayAppend(aData, sGridData) />
        </cfoutput>

        <cfset sGridReturn["draw"] = val(draw) />
        <cfset sGridReturn["recordsTotal"] = 10000 />
        <cfset sGridReturn["recordsFiltered"] = rsQueryData.RecordCount />
        <cfset sGridReturn["data"] = aData />
        <cfreturn sGridReturn />
    </cffunction>

as i am using the order by clause and the offset it gives me just 17 records while i have so many records, what i am missing here

i want the query should give calculate all the records and give me the results paginated way like 10 at a time so my query should not timeout and smoothe it should go

because after this next task is to get full data in excel

dalenw

I can't fix your code, but I may be able to help.

You need to return 4 values when using server side loading and pagination.

payload = {
        draw: params[:draw],
        recordsTotal: People.all.size,
        recordsFiltered: data.length,
        data: data
      }

draw is the draw value data tables sends with it's request.

recordsTotal is an int representing the total amount of records. So in your case this should be around 5000000

recordsFiltered is an int representing the number of records you are sending back. In this case 10.

data is simply an array of the objects you are sending back.

Hopefully this helps.

colin

Yep, as @dalenw suggested, it sounds like your server-side script isn't returning what's expected. The protocol is discussed here. Also see examples here.

Cheers,

Colin

maniya

@dalenw @coliin did yow saw my CF Code, i am returning theses 4 variables

kthorngren

right now my query returns around 5000000 records without using offset

That would require debugging your server script. Have you debugged it to see what SQL statement is built?

Kevin

maniya

@kthorngren i mnaged to fix this by using a cte and then passing the pagination in cte

like this

in mycfc i will made this change

<cfif start NEQ -1>
            <cfset startRow = start + 1 />
            <cfset endRow = startRow + length />
        </cfif>

;with cte (select * from tab1 union select * from tab2),,
                Count_CTE AS (
                SELECT COUNT(*) AS [TotalCount] 
                FROM CTE 

            )
            SELECT *  
            FROM cte,Count_CTE 
order by 2 desc
            OFFSET #val(startRow)# ROWS FETCH NEXT #endRow# ROWS ONLY 

it seems the pagination started working but when i click the last number, i get this

https://prnt.sc/1rmrjln

kthorngren

I would start by making sure your calculations for recordsTotal and recordsFiltered are correct. That is what the client side Datatables uses to calculate the paging buttons. Otherwise you will need to debug your server script to find out why its not returning any rows for the last page.

Kevin

maniya

is it because i m using a CTE and count in the same query, because for count in a separate query, i do not know if i have to call the CTE again or whatever i should be doing here

allan

Very likely yes - but for SQL specific help, you'd be better asking on StackOverflow. You typically need three queries for server-side processing:

1. Get the total number of records in the data set without filtering
2. Get the number of records in the data set with filters (this would be the same as 1. if no filter was applied)
3. Get the data for the current page

Allan

maniya

@allan Qustion here

I have very big dataset so i can change the ways it loads data, i can scrap the offset and fetch, i can use cfoutput startrow an maxrows to do pagination, my only concern is query takes lots of time to return data but in the front end, i want to actually start seeing records if in the backend it keeps working, not concerned about filtering/search

is this doable, i am trying to understand can i achieve same results without using fetch

allan

Does it take a long time to run the query even if you limit it to say 10 rows? That should be how server-side processing operates - it just sends back the rows needed for the current pagination.

Allan

maniya

that is what i am trying to understand, so i use pagination by cfoutput, the query has 70000 records, so it loads that many records in memory and then pagination by cfoutput, is that how server side works because i tried implementing pagination on sql query but that produces wrong results

allan

Sounds like the SQL query is wrong then. You most certainly don't want to load 70k rows into memory just to throw away all but 10 of them. That would negate much of the advantages that server-side processing will afford you.

I'd suggest debugging the SQL statement if it is giving the wrong results.

Allan