I have written an interface where by the end user puts together search criteria for finding records that can be nested such that statistics are presented for each section of the criteria they have supplied.
This is shown in the top section of the image. Below is effectively what I am doing in code, which is to use SQL select statements to generate the node level statistics and then as you ascend the tree, I dynamically construct an SQL selection that reflects the combination generating a rather long strung together SQL statement.
Performance is reasonable but less so where an auwful lot of matches have to be joined against, particularly in the case of the NOT criteria which returns most of the records in the table less those that matched.
I am wondering if there is an approach you guys can suggest I take in constructing the SQL in order to support the dynamic nature of the criteria but to maximize performance.
I had wondered about perhaps storing each nodes results into a temporary table such that as more criteria are added, theparent nodes only need refer to those temporary tables for child results rather than requerying the whole ContactSkill or Contact table by virtue of the strung together expression.
Any comments on my approach would be welcomed. I am using SQL server 2012.