Retrieve Huge Data Set From Database For Searching

AT-2017

Well! The issue may seems simple but I am doing a R & D for one of my project that has a huge data set (40000k+) from database (Microsoft SQL Server with ASP.NET MVC C#) and it has to searched efficiently. Say using jQuery DataTable, I managed to show data as below that's being created in the client-side:

<link href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
<link href="https://cdn.datatables.net/scroller/2.0.2/css/scroller.dataTables.min.css" rel="stylesheet" type="text/css" />

<script src="https://code.jquery.com/jquery-3.5.1.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/scroller/2.0.2/js/dataTables.scroller.min.js" type="text/javascript"></script>
<script>
    $(document).ready(function () {
        var data = [];
        for (var i = 0 ; i < 40000000; i++) {
            data.push([i, i, i, i]);
        }

        $('#example').DataTable({
            data: data,
            deferRender: true,
            scrollY: 200,
            scrollCollapse: true,
            scroller: true
        });
    });
</script>

<form id="form1" runat="server">
        <div>
            <table id="example" style="width: 100%">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Country</th>
                    </tr>
                </thead>
            </table>
        </div>
</form>

So pretty simple! When the data is up to 4000k, then the data is rendered perfectly in the browser. Now here is the catch, when data gets to 40000k+, the browser throws an exception - Error code: Out of Memory.

I am not sure if this has to be done anything in the client-side but my concern is the database as my plan is to retrieve huge data from database. If this exception appears in the client-end, then for server-side it would be a mess when I'll use Ajax call to get those data. Any efficient way to handle this situation with an example would be highly appreciated. I've been doing few R & D and checked the jQuery DataTable to make it work but I believe, it requires more concerns.

My requirement: By default, I may show data up to 100 at a time from database but when a user will search data from database, it has to fetch data from 40000k+ data as the searched data may not appear in the data list of 100.

colin

Yep, that amount of data would kill most browsers. This section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin