Using SQL That Return Varied Columns with Out Of Memory asp mvc

Question

I'm working on a generic search from an object and his relation objects. I execute a SQL query and display the data dynamically on a grid. The problem that I faced is that my query returns varied Columns (more then 18) and so much data (1474656 records) and when I return the data to display them in the grid an Out Of MemoryException occured. How can I solve this problem?

@Giorgi Nakeuri i have a very large data to display ,i'm working on generic search to display an object by filtering it with all its proprotes and all related objects — ODE, May 12 '15 at 10:38
What would a user even *do* with 14,746,56 rows of data? Take a book from the library, don't walk around with the building — Alex K., May 12 '15 at 10:46
possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Jodrell, May 12 '15 at 11:11
You'll have to dynamically add an order by and add the paging clause (depending on your version of SQL Server.) If you need to know the total number of pages you'll need to execute the query again, aggregating the count but omitting the paging. — Jodrell, May 12 '15 at 11:16

score 0 · Answer 1 · answered May 12 '15 at 11:14

0

Your grid needs to either implement a paging solution (fairly easy) or you need to load rows on demand as the user scrolls down (could be a little bit more difficult). Regardless, you should only be loading a small subset of the data as needed for display on the UI.

answered May 12 '15 at 11:14

Pittmyster

62
4

i use a sql server 2008 which pagination must i use? please help me with a documentataion – ODE May 12 '15 at 15:01
You're a developer - implement a paging solution yourself in the application code. – Pittmyster May 13 '15 at 18:44

score 0 · Answer 2 · answered May 12 '15 at 21:41

You tagged Kendo Grid, so I will assume you're using that.

Implementing paging is very easy with Kendo Grid, by setting a page size in the datasource and configuring pageable.

Example

 $("#grid").kendoGrid({
    dataSource: {
        type: "json",
        transport: {
            read: '@Url.Action("MyAction", "Home")'
        },
        pageSize: 20
    },
    height: 550,
    groupable: true,
    sortable: true,
    pageable: {
        refresh: true,
        pageSizes: true,
        buttonCount: 5
    },
    columns: [{
        field: "ContactName",
        title: "Contact Name",
        width: 200
    }, {
        field: "ContactTitle",
        title: "Contact Title"
    }, {
        field: "CompanyName",
        title: "Company Name"
    }, {
        field: "Country",
        width: 150
    }]
});

Controller

public JsonResult MyAction([DataSourceRequest]DataSourceRequest request) {
    var result = BL.GetYourData();
    return Json(result.ToDataSourceResult(request));
}

ToDataSourceResult will apply your paging, so only 20 records will be returned to the client. If your data access is set up properly your SQL Server will also only return 20 records.

Using SQL That Return Varied Columns with Out Of Memory asp mvc

2 Answers2