Best use : filtering or adapted request ? Topic is solved

[yvesf]

Hi,
I would like to know the best option for filtering a browse list: using an SQL query or the filtering mechanism provided by the object run.

have two tables: customers and tasks.
Each task is associated with a customer, and for each customer, I display the corresponding list of tasks.

What is the best approach to accomplish this?

Should I use an SQL query with a WHERE clause on the customer_id to filter the tasks related to the open record?
Or should I use a filtered request on the tasks list?
I am particularly concerned about potential performance issues. I have attached a database presenting both options.

2025-03-15_162027_67d5b6db6c70d_nuBuilder_backup.zip

Thank you for advising me on the best option.

Yves

[kev1n]

Hi,

Both variants will work, even with a large number of records.
However, the one with WHERE ((tasks.tsk_assigned_id ='#record_id#')) should be slightly more performant since its `WHERE` clause is less complex. You can inspect the Browse SQL to compare their differences.

That said, since browsing uses pagination, only a limited number of records are loaded at a time, so the performance difference may not be significant.

For fun, I added 100000 tasks for a customer using the procedure below, and both browse forms still load lightning-fast.
(It might take some time to execute it....)

Code: Select all

DELIMITER $$

CREATE PROCEDURE InsertTasks()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 100000 DO
        INSERT INTO `tasks` (`tasks_id`, `tsk_deadline`, `tsk_title`, `tsk_description`, `tsk_status`, `tsk_assigned_id`) 
        VALUES (i, '2025-03-15', 'valider le fonctionnement du systeme', 'je ne crois pas que je sois pret', 'A faire', '67d5453e8effb9e');
        
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

Code: Select all

CALL InsertTasks();

[yvesf]

Top !!! thanks Kevin