sql - optimizing a view for hierarchical data -


I currently have a query in which all the direct and indirect managers -Includes store company organization information. In this SQL notation, the numbers before a colon (examples: 1) are variables:

  Select parent.depth from the relationship, as the mother in the relationship as the node in the relationship Join the parent as a parent .lft and parent.rgt and node.empid =: 1 order by parent.lft  

I simply shorten the manager n < / Em> can return level to parent.depth = node.depth -: 2 by adding a condition or where a segment (side question: Micro fast?) Adding staff.

Problem: I'm trying to convert this query to a view, and I'm not fortunate enough. The problem is that my or all of the variables are in the condition of joining my query. My current best plan is to split those pieces into columns, which I can see when I can break the scene during the query , For example:

select node.Empid, parent.empid as MgrID, MgrDepth as parent.depth, form node from MgrRelativeAltitude RelationshipMgr as node.depth - parent.depth In relationMgr, the parent.lft and parent.rgt correspond to the node.lft You can see that the invention of the MgrRelativeAltitude column to identify the level of manager n above the employee But this is hardly the biggest problem. I am worried that there will be serious performance problems, because SQL Server appears to be in the form of a perfect combination as per the insertion status, and then to add intellectually to where the boundary is added using the range of clauses Is Filtered Is There A Better Way To Create A Scene? Should I leave it as a question and forget about creating a scene? Can I achieve anything by making it a stored procedure instead of a scene?

And please say "Pre-optimization is not evil" ... it's not ahead of time. Implementation I am using something like a tasteless adjacent list, whose employee had a record related to one of his direct and indirect managers ... worst case O (N ^ 2) records, and in anticipated serious performance problems Participated we had more than 300000 employees in the hierarchy, my new nested-set implementation would reduce those performance problems, leaving this one query Do ... if you've been trying to do a select * on the proposed sequence, the result will be almost identical to the old table I change ', and it makes me very worried.

You are trying to determine the hierarchical relationship of non-adjacent nodes as you have found, This is a relatively expensive run time calculation, visual or routine query. Instead, if repeatedly run, I am known as the update or real time through the trigger - either the form of indexed view in SQL Server 2005+ (although the indexed view approach is not tried) Is known in It has been noted that the nested set provides better read time than the adjacent list.

Business is a table with lots of rows from closed source because it effectively shows relationships between all nodes which slow down writing because it adds a node at some point, is removed , Or the parent ID changes, in return you can index it and get faster recovery time. An adaptation, if updating the bridge proves to be an obstacle, then it is reaching through a stored procedure where the bridge often works as a cache to run a combination of inputs, but in the run-time, Counts cases, you need to evaluate the typing frequency of typing the built-in node table to create that assessment vs. evaluation.


Comments

Popular posts from this blog

Eclipse CDT variable colors in editor -

AJAX doesn't send POST query -

wpf - Custom Message Box Advice -