duplicate SQL queries being run back to back by SearchPanes.

MarkAndersonUK

Hi,

I have searchpanes enabled for a couple of fields, and while I was looking my my SQL server query log (I was optimizing tables, so had logging turned on). I noticed that during the datatable initialization searchpanes, is sending the exact same command to the SQL server twice. Obviously that is not very efficient, and in this case add about 2sec to the overall load time per 'searchPane' enabled field as these are large tables.

The double SQL query is run for every single field that has searchPane enabled.

My environment is NodeJS, and the datatable is running in serverSide mode.

Below is one of the queries that was run twice back-to-back.:

SELECT DISTINCT
    `recruits`.`_search_states` AS `label`,
    `recruits`.`_search_states` AS `value`,
    COUNT(*) AS `total`
FROM
    `recruits`
        LEFT JOIN
    `recruits_data` ON `recruits_data`.`recruitID` = `recruits`.`recruitID`
GROUP BY `recruits`.`_search_states`;

Here is the extract from the field definition :

            new Field("recruits._search_states")
                .searchPaneOptions(new SearchPaneOptions())
                .set( false ),

SQL Server log extract:

Does anyone know why it is running the exact same query twice, and how to stop it.

allan

Many thanks for flagging this up. We'll try to reproduce it here and report back. Could you confirm what version of our client-side libraries and the NodeJS datatables.net-editor-server package you are using please?

Thanks,
Allan

MarkAndersonUK

Hi @allan,

I am using v2.0.7 on both the client and server sides. On the server side it is the npm package (so it is using the ts files). The client is using the js files.

Also, in addition to the duplicate statements, I noticed that even when the searchpane field is in the main table, a SQL query being used to produce the optionlist values and counts includes an unnecessary LEFT JOIN, if you take the example I listed in my original post, the JOIN could be removed and it could be re-written with identical results as :

SELECT DISTINCT
    `recruits`.`_search_states` AS `label`,
    `recruits`.`_search_states` AS `value`,
    COUNT(*) AS `total`
FROM  `recruits`
GROUP BY `recruits`.`_search_states`;

This way consumes about 10% of the resource cost of the statement in my original post and so i much quicker. Using the MySQL Query 'Execution Plan calc' command I recorded the original query has a cost of "854846.28" the second method has a cost of "73374.55" which makes the current method 11.65 times more costly.

For fields that ONLY include a searchpane field in a Child table, you can
write the count command like :

 SELECT DISTINCT
    `recruits_data`.`chip_id` AS `label`,
    `recruits_data`.`chip_id` AS `value`,
    COUNT(*) AS `total`
FROM  `recruits_data`
GROUP BY `recruits_data`.`chip_id`; 

again this is many times more efficient and faster.

The bottom line is, using a LEFT JOIN in the enumeration of the SearchPane option values and counts is much slower than it needs to be. I hope this make sense. This is really a second issue / comment, the first issue of the statements being executed twice, just make the inefficiencies twice as bad.

I am really hoping we can make this faster as currently, between the duplicate statements and extra processing times, I end up adding 2 secs to the load time of every searchpane.

Thanks for everything you guys do

sandy

Hi @MarkAndersonUK ,

Thanks for pointing this out to us. I've managed to replicate the issue locally and something isn't right there. Definitely something that we should look to improve. I've raised an issue internally (DD-2505 for my reference) and will report back here when there is an update.

Thanks,
Sandy