Performance Using C# datatables and Editor for large oracle table - 100 columns and 33 million rows
Performance Using C# datatables and Editor for large oracle table - 100 columns and 33 million rows
david.j.meyer2@boeing.com
Posts: 54Questions: 15Answers: 0
Link to test case:
Can't replicate due to volumne in Oracle data. Will provide code.
Debugger code (debug.datatables.net):
N/A
Error messages shown:
No error message, just taking 2-3 minutes per page to load.
Description of problem:
Implementing Datatable/Editor for large Oracle table 100 columns and 33 million plus rows. i am using the editor in c# to load the table as always and this typically does not cause performance issues in smaller tables (1-3 million). Any suggestions is greatly appreciated. I have include the c# code for retrieving the data using the editor object and the table definition in cshtml page.
Here is the table definition in the cshtml:
$('#table').DataTable({
dom: "rtip",
orderable: false,
autoWidth: true,
deferRender: true,
lengthMenu: [[15, 50, 100, 250, 1000, 2500, 25000, 50000], [15, 50, 100, 250, 1000, 2500, 25000, 50000]],
pageLength: 15,
processing: false,
serverSide: true,
bSortClasses: false,
ajax: {
url: "@System.Web.Configuration.WebConfigurationManager.AppSettings["RelativePath"]" + "/api/sm",
type: 'POST',
deferRender: true
},
columns: [
{ data: "SM.a", searchable: false },
{ data: "SM.b", searchable: false },
{ data: "SM.c", searchable: false },
{ data: "SM.d", searchable: false },
{ data: "SM.e", searchable: false },
{ data: "SM.f", searchable: false },
{ data: "SM.g", searchable: false },
{ data: "SM.h", searchable: false },
{ data: "SM.i", searchable: false },
{ data: "SM.j", searchable: false },
{ data: "SM.k", searchable: false },
{ data: "SM.l", searchable: false },
{ data: "SM.m", searchable: false },
{ data: "SM.n", searchable: false },
{ data: "SM.o", searchable: false },
{ data: "SM.p", searchable: false },
{ data: "SM.q", searchable: false },
{ data: "SM.r", searchable: false },
{ data: "SM.s", searchable: false },
{ data: "SM.t", searchable: false },
{ data: "SM.u", searchable: false },
{ data: "SM.v", searchable: false },
{ data: "SM.w", searchable: false },
{ data: "SM.x", searchable: false },
{ data: "SM.y", searchable: false },
{ data: "SM.z", searchable: false },
{ data: "SM.aa", searchable: false },
{ data: "SM.bb", searchable: false },
{ data: "SM.cc", searchable: false },
{ data: "SM.dd", searchable: false },
{ data: "SM.ee", searchable: false },
{ data: "SM.ff", searchable: false },
{ data: "SM.gg", searchable: false },
{ data: "SM.hh", searchable: false },
{ data: "SM.ii", searchable: false },
{ data: "SM.jj", searchable: false },
{ data: "SM.kk", searchable: false },
{ data: "SM.ll", searchable: false },
{ data: "SM.mm", searchable: false },
{ data: "SM.nn", searchable: false },
{ data: "SM.oo", searchable: false },
{ data: "SM.pp", searchable: false },
{ data: "SM.qq", searchable: false },
{ data: "SM.rr", searchable: false },
{ data: "SM.ss", searchable: false },
{ data: "SM.tt", searchable: false },
{ data: "SM.uu", searchable: false },
{ data: "SM.vv", searchable: false },
{ data: "SM.ww", searchable: false },
{ data: "SM.xx", searchable: false },
{ data: "SM.yy", searchable: false },
{ data: "SM.zz", searchable: false },
{ data: "SM.aaa", searchable: false },
{ data: "SM.bbb", searchable: false },
{ data: "SM.ccc", searchable: false },
{ data: "SM.ddd", searchable: false },
{ data: "SM.eee", searchable: false },
{ data: "SM.fff", searchable: false },
{ data: "SM.ggg", searchable: false },
{ data: "SM.hhh", searchable: false },
{ data: "SM.iii", searchable: false },
{ data: "SM.jjj", searchable: false },
{ data: "SM.kkk", searchable: false },
{ data: "SM.lll", searchable: false },
{ data: "SM.mmm", searchable: false },
{ data: "SM.nnn", searchable: false },
{ data: "SM.ooo", searchable: false },
{ data: "SM.ppp", searchable: false },
{ data: "SM.qqq", searchable: false },
{ data: "SM.rrr", searchable: false },
{ data: "SM.sss", searchable: false },
{ data: "SM.ttt", searchable: false },
{ data: "SM.uuu", searchable: false },
{ data: "SM.vvv", searchable: false },
{ data: "SM.www", searchable: false },
{ data: "SM.xxx", searchable: false },
{ data: "SM.yyy", searchable: false },
{ data: "SM.zzz", searchable: false },
{ data: "SM.aaaa", searchable: false },
{ data: "SM.bbbb", searchable: false },
{ data: "SM.cccc", searchable: false },
{ data: "SM.dddd", searchable: false },
{ data: "SM.eeee", searchable: false },
{ data: "SM.ffff", searchable: false },
{ data: "SM.gggg", searchable: false },
{ data: "SM.hhhh", searchable: false },
{ data: "SM.iiii", searchable: false },
{ data: "SM.jjjj", searchable: false },
{ data: "SM.kkkk", searchable: false },
{ data: "SM.llll", searchable: false },
{ data: "SM.mmmm", searchable: false },
{ data: "SM.nnnn", searchable: false },
{ data: "SM.oooo", searchable: false },
{ data: "SM.pppp", searchable: false },
{ data: "SM.qqqq", searchable: false },
{ data: "SM.rrrr", searchable: false },
{ data: "SM.ssss", searchable: false },
{ data: "SM.tttt", searchable: false },
{ data: "SM.uuuu", searchable: false },
],
columnDefs: [
{
targets: 1,
className: 'noVis'
}
],
select: true,
scrollY: '50vh',
sScrollX: true,
scrollCollapse: true,
fixedHeader: true
});
Here is the c# code that returns the data:
[Route("api/sm")]
[HttpGet]
[HttpPost]
public IHttpActionResult SmList()
{
var request = HttpContext.Current.Request;
var connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
using (var db = new Database("oracle", connectionString))
{
var format = request.QueryString["format"]; // null if not given
// setup editor
var editor = new Editor(db, "SM", "SM.THEKEY")
.Model<Sm>("SM");
var response = editor.Process(request)
.Data();
return Json(response);
}
For those who do not use C# and the editor. This is setup how the examples provided for C#, using the editor to generate the data for the table.
Right now using server side processing deferRender, I am returning a page of data in 2 mins and 30 seconds. I know if i cut down the columns returned it will improve performance. I am hoping to not have to do that.
thanks!!
This question has accepted answers - jump to:
Answers
SM.THEKEY is the primary key for the table.
The first thing I’d do here is enable the debug mode so you can see the SQL that is being executed - that is done by changing:
to be:
Then the JSON returned from the server will include the SQL statements used. There will be three in it here I suspect. Run each of them directly against your database in Workbench or similar with “EXPLAIN” to see where the time in the statements is going and if any optimisations can be made.
My guess is that the COUNT() might be a bit slow, but I can’t recall how Oracle does its cardinality.
Allan
Thanks Allan, that allowed me to see the query being executed. What i noticed is the default order was on a non-index string for a very large table, not good. I set the order to be thekey which is the primary key.
Returns records in 11 seconds. What I assumed was the order would be the primary key identified by default unless an order was specified.
Thanks, Dave
Sounds good - glad that helped!
Allan