Filtering large datasets is resource intensive and time consuming on the server side. This can lead to slow application performance—and, ultimately, dissatisfied customers and end users.
At Logi Analytics, we recommend solving the problems of slow page load times and lagging performance by:
- Implementing Common Table Expression (CTE) in Tree Data Structure using Logi Info
- Control runs client-side
- Using Dynatree (jQuery plugin) to display tree data results
- Add node to list using TagIt (jQuery plugin)
- Typeahead to find all node that match keyed text. Then traverse node to find connecting parent and children nodes.
Recursive CTEs are special since they reference themselves, which makes them good at working with hierarchical data such as organizational hierarchies, file systems, inventory systems, and more.
Since a recursive CTE allows you to retrieve relational data in a hierarchical format, it helps the end user navigate the data. The hierarchical dataset establishes parent-child relationships between entities.
By running the recursive CTE on the client-side, we have been able to reduce page load times from an average of 8 seconds per page to less than 2 seconds per page.
Dynatree (or a similar jQuery plugins) displays the data in an intuitive way using lists. Each row in the list can contain child rows to create an expandable-list, or tree. This makes it easy to display and navigate deep layers of hierarchical data.
The Tag-it plugin is a configurable tag editing widget that allows typeahead filtering. In Logi Info, we created a quick “add” button that updates the tags and results, while placing the IDs in a hidden element.
Check out an example of recursive CTE filtering using Logi Info: http://apps.logianalytics.com/TreeDataControl/rdPage.aspx
What does recursive CTE filtering feature in Logi Info?
Our unique implementation of recursive CTE filtering at Logi Info allows for:
- Parent-Child Display: When searching for Child, the parent is displayed. For example, if a user searched for CINNAMON in the example linked above, they will see the hierarchy shown below, with the REGULAR CINNAMON RAISIN BAGELS highlighted.
- Highlights Text Matching Search: All instances of CINNAMON in the example above will be highlighted in the appropriate tree node.
- Shows Number of Results: The number of results matching the search text will be displayed at the top.
- Autocomplete Support: Tag-it JQuery plugin allows for adding quick filtering criteria and autocomplete support.
- Lazy Load Support: Supports delayed loading of tree nodes.
What should I consider in my design?
- Make sure you understand your data structure really well. Although most tree data is standard, the way the data relates to each other may get confusing.
- Create a Unique Identifier for each node element.
- Create Data Definitions to get the Hierarchical Data and store them in a local JSON object.
- Use jQuery to filter the data results.
What Are Your Variable Names?
SQL Code running the queries
The four SQL code snippets below illustrate the recursive CTE statements for each scenario:
- Retrieve all records to (n) level
- Expand category
- Recursively get tyepahead data
- Lazy Load
1. Retrieve All records to (n) Level:
This SQL code retrieves all records to (n) level:
SELECT distinct [key], title, parent, [level] FROM tree_data WHERE [level] <= '@Request.Level~' order by [level], title
1. Expand Category:
When a user clicks a category, this SQL retrieves the data:
SELECT distinct [key], title, parent, [level] FROM tree_data WHERE parent = '@Request.ID~' AND [level] = '@Request.Level~'
3. Recursively get typeahead data:
This SQL code recursively gets typeahead data:
;WITH RecursiveCte AS ( SELECT H1.[Level], H1.[key], parent, H1.title FROM tree_data H1 WHERE title LIKE '%@Request.Description~%' UNION ALL SELECT RCTE.[level] - 1 as Level, H2.[key], H2.parent, H2.title FROM tree_data H2 INNER JOIN RecursiveCte RCTE ON H2.[key] = RCTE.parent ) SELECT distinct [key], title, [level], parent FROM RecursiveCte order by [level], title
4. Lazy Load
The lazy code snippet allows delayed loading of tree nodes, which means the user reads the child nodes only when their parent is expanded:
SELECT distinct [key], title FROM tree_data WHERE [key] in (@Request.FilterBy~)
Questions or concerns? Feel free to email us.