Performance for server side processing

jb48888

I am going to add server side processing to an ASP.Net MVC app. The database query may take 7 to 8 seconds to fetch data. I understand that database query will get executed by every paging and search on Datatables control. I assume that when the end user types a letter in the search box, the table will wait 7-8 seconds to display the new data. It's too expensive to query database by every letter input in the search box. I wonder how you all solve the performance problem.

Thanks.

jb48888

Tom (DataTables)

Hi

7-8 seconds seem far too long for a database query, which would lead me to believe something in your query it to blame.

Possibly a join is being performed on a non-indexed column or LIMIT/OFFSET hasn't been set or paging is turned off. That would cause the query to take too long.

If you want to sent a link to the page showing the issue I can take a look for you.

Thanks

Tom

jb48888

Hi Tom,

Thanks very much for your input. This is an internal application with a sql query joining 5+ big tables that have been indexed. The database part is out of my control. In order to make the Datatables control to work, I might hide the search box.

Any other solutions?

Thanks.

jb48888

allan

Is the server only returning the 10 records that DataTables is requesting? That would be the first thing to check.

If it is, then I would suggest you get the SQL query that is being run and EXPLAIN it to find out where all the time is going.

Allan

jb48888

Hi Allan,

Yes, the sql query returns only 10 or 25 records a time, which takes 7 seconds. I know the query is slow. However, it's out my control. I'm thinking about to disable the instant search function. Instead, add a search button next to the search box.

Thanks.

jb48888

allan

Regardless of the search, you would also have a 7 second delay for every draw (paging, sorting, etc). I would suggest using EXPLAIN with your SQL query to understand why it is taking so long. 7 seconds for 10 rows is quite exceptional.

Allan

jb48888

Hi allan,

Waiting for 7 seconds in paging seems OK. User feels like a page is reloading. However, waiting for 7 seconds between every character input is not acceptable. I am thinking about to add a search button next to the search box.

Thanks for your reply,

jb48888

allan

Out of interest how many records are you working with? 7 seconds seems like a really long time, even with millions of rows. Unless the joins you are doing a quite complex?

Allan

jb48888

Hi allan,

The joined tables range from 1.7 to 42 million rows. I doubt that instant search can handle that. Adding a search button next to the search box while disabling the instant search function might solve the issue in such a scenario.

Thank you.

jb48888

allan

That's a healthy number of rows .

With the next major version of DataTables I plan to add an option to do a key debounce so it will wait for a set period of time before triggering the search - so you can type quickly and not trigger the search frequently.

This legacy plug-in does exactly that.

Allan

jb48888

Hello allan,

It's wonderful that you have already had a plug-in for the solution. I will add it to my project.

It's good to know your plan in the next release. It would definitely make the search much more efficient.

Much appreciated.

jb48888