Slowness of Editor processing (weird question)

joshlevine102

Hello. We have a case where an app is having slowness only on the first read (GET) of the data for the DataTables, only in our production environment. It seems much faster in our other environments and locally. The code in our non-Prod environments is identical to Prod, and the database should be very similar, as we recently refreshed non-Prod from Prod. This is using the .NET Editor code for the back end with the WebApi controller. The page uses the ajax property to get this data once on page load, and get updates in the Editor when changes are made. The JS code for the DataTable in the view page is like the following.

table = $('#example').DataTable({
            dom: "Blfrtip",
            ajax: "/bis401/api/sites",
            stateSave: true,
            pageLength: 50,
            DT_RowID: "Site_ID",
            columns: [
...

The AJAX request to get the data is taking a long time, just in the Prod environment, as I can see in the Chrome dev tools network tab.

I added some debugging lines to try to see the timing of the database calls. These lines were added to the Sqlserver/Query.cs class _Exec method. I'm not sure this is the right idea. This is what I did.

        protected override DataTables.Result _Exec()
        {
            var dt = new System.Data.DataTable();

            HttpContext.Current.Trace.Write("DataTables _Exec", "Starting Query at " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff zzz"));

            using (var dr = _stmt.ExecuteReader())
            {
                dt.Load(dr);
            }

            HttpContext.Current.Trace.Write("DataTables _Exec", "Finished Query at " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff zzz"));

            return new Sqlserver.Result(_db, dt, this);
        }

When I look at the web trace, I see a long delay after the first, long query, which I assume is occurring when the code is processing the result set to form the DataTables response. This is how the web trace looks.

Just wondering if you've seen any cases of slowness that seems to be coming from the processing of DB result sets in the code, and what might be occurring. Sorry for the vagueness of this question.

allan

Does it happen every time you reload the page? I've noticed that if I start the app, and try to access the database, it can get a few seconds for it to establish the connection pool. But its fast from then on.

Are you able to enable slow query logging on the SQL server to see if that is what is causing the issue? (I assume you are using SqlServer?).

Allan

joshlevine102

Hi Allan. It seems to happen consistently, even on page reloads. I can get the SQL Select statement from debugging the app (or switching on _debug for the Editor), and when I run it in SQL Server Management Studio (yes, SQL Server), it completes very quickly. The DBAs have said, according to their monitoring tools, the query completes very fast. I had thought, if the DB calls were slow, my timing calls above might have indicated that, but I'm not sure I set them up right. They may not fully reflect the timing of these calls. At least, I should've used System.Diagnostics Stopwatch or something for timing tests. Thanks.

allan

On the client-side, what I would suggest is to open the network panel in the browser's inspector and check the timings that it shows. I would expect from your description that it will say that the "waiting" is quite long - the actual request and then the response will be really short. If so, then its certainly a server-side issue.

At that point you'll probably need to use the profiling tools in Visual Studio (assuming you are able to do that in your production environment?) to determine where the slow down is. It might be the connection to the SQL server, if you can run profiling on the production server, then that would be the first place I would stick a couple of timing calls (in Database.cs there are database connection Open() calls inside the constructor for the class).

Allan

joshlevine102

Hi Allan. Thanks. That gives me a few things to try. I'll take a look. Yes, the browser console shows it is waiting for the first byte for a long time, definitely something server-side.

allan

Let me know if you find anything - curious as to what is happening here!

Allan

joshlevine102

On a side note, if I'm displaying five columns in the DataTable, but I want to let the user edit many more columns using the Editor, should I be fetching all the data for all the columns and all the rows in one ajax request (it's about 1 MB)? I know, there are ways of using server side fetches for changing pages and searching and so forth, but I did like the speed of having the processing be client-side.

joshlevine102

From reading the manual a bit more, I think I see from the rule of thumb, with the relatively small number of rows we have (1600), client side should be fine. Having additional columns which aren't displayed in the table should also work okay.

allan

Yes, you'd need to transmit the data for the other fields as well. There is an example of that here (although it isn't using server-side processing).

Allan

joshlevine102

This was apparently a server issue. The Ops team has set up monitoring tools, and now the page load time is normal! Sorry for the bother.

allan

No worries - thanks for letting me know.

Allan