查询数据库中的搜索树
我的数据库中有一个表代表一棵树。数据使用嵌套集存储。我想编写一个查询来搜索树并仅返回与模式匹配的节点及其祖先和后代。这是我到目前为止所想出的。
SELECT DISTINCT Node, Parent, Description
FROM Hierarchy
INNER JOIN
(SELECT Lft, Rgt
FROM Hierarchy
WHERE Description LIKE '%SEARCHQUERY%') AS Matches
ON (Hierarchy.Lft <= Matches.Lft AND
Hierarchy.Rgt >= Matches.Rgt) OR
(Hierarchy.Lft >= Matches.Lft AND
Hierarchy.Rgt <= Matches.Rgt)
ORDER BY Description
这个查询可以工作，但是当子查询匹配很多描述时，它有点慢。我正在寻找有关如何提高此查询性能的想法。
如果相关的话，我正在使用 Access。
我有空并且愿意改变表的结构来改进这个查询。该表大约有 8000 个节点。在应用程序的生命周期中，记录数量不会发生太大变化。最大深度为五。
对于常规搜索，性能是可以接受的（返回约 200 个节点的搜索需要几秒钟），但在病态情况下，需要几分钟（例如，如果搜索单个元音）。但即使在这些情况下，子查询花费的时间也更少执行时间少于一秒）。
I have a table in my database representing a tree. The data is stored using nested sets. I want to write a query to search the tree and return just the nodes that match a pattern, along with their ancestors and descendants. This is what I have come up with so far.
SELECT DISTINCT Node, Parent, Description
FROM Hierarchy
INNER JOIN
(SELECT Lft, Rgt
FROM Hierarchy
WHERE Description LIKE '%SEARCHQUERY%') AS Matches
ON (Hierarchy.Lft <= Matches.Lft AND
Hierarchy.Rgt >= Matches.Rgt) OR
(Hierarchy.Lft >= Matches.Lft AND
Hierarchy.Rgt <= Matches.Rgt)
ORDER BY Description
This query works, but it's a little slow when the subquery matches a lot of descriptions. I'm looking for ideas on how to improve the performance of this query.
In case it's relevant, I'm using Access.
I am free and willing to change the structure of the table to improve this query. The table has about 8000 nodes. The number of records won't change much through the lifetime of the application. The maximum depth is five.
The performance is acceptable for regular searches (a few seconds for searches that return ~200 nodes), but on pathological cases it takes a few minutes (if searching for a single vowel, for example. But even in these instances, the subquery takes less than a second to execute).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我可能有点偏离了原来的问题，但我在这里：
正如评论中所建议的，考虑到您可以负担得起重写，您应该研究一种不同的方法来建模您的树结构，特别是考虑到您有“固定深度”通过不同的方法这是很容易管理的。
Faroult 在他的《SQL 艺术》中支持一种基于在字符串字段中表示节点位置的方法，该字符串字段编码节点所在的“分支”。 （有关本书的评论和一些讨论，请参阅此 Slashdot 线程）。
这是我的意思的在线示例 - 艺术书中有一整节 SQL 专门讨论这一点，比较了三种不同的方法（嵌套集、父/子关系表、编码路径字段）并使用滑铁卢军队的战斗顺序作为示例（有大量查询，例如“列出 X 将军领导下的所有营”或“查找谁是 Y 炮兵群的指挥官”）。
Faroult 对性能非常狂热，整本书都是关于如何（重新）编写高效查询的非常合理且实用的建议的非供应商特定集合。
I am probably straying a bit from the original question, but here I go:
As suggested in the comments, considering you can afford a rewrite, you should investigate a different way to model your tree structure, especially considering you have a "fixed depth" that is pretty manageable with a different approach.
Faroult in his "The Art of SQL" favours an approach based on representing the position of the node in a string field encoding the "branch" the node lives on. (For a review of the book, and a bit of discussion, see this Slashdot thread).
Here is an online example of what I mean - The Art of SQL has a whole section of the book dedicated to this, comparing three different approaches (Nested Sets, Parent/Child relation table, Encoded path field) and using the battle order of the armies at Waterloo as an example (with plenty of queries like "List all the battalions under General X" or "Find who was the commander of Artillery Group Y").
Faroult is pretty fanatic about performance and the whole book is a non-vendor specific collection of very sound and practical advice on how to (re)write efficient queries.
我可能只使用表中的
parent_id字段，并使用 3 路外部自联接来获取目标hierarchy记录（经过适当过滤）及其父记录（如果有）和子（如果有）记录。I'd probably use just a
parent_idfield in the table, and use a 3-way outer self-join to get the targethierarchyrecords (filtered appropriately) plus their parent (if any) and child (if any) records.查询速度慢的原因是
LIKE('%blah%')部分。如果您可以删除前%，事情会明显加快。或者，如果 Access 支持 FULLTEXT 索引，则将一个索引放在“说明”字段中并执行MATCH(Description) AGAINST ('blah')The reason your query is slow is the
LIKE('%blah%')part. If you can drop the first%things will speed up appreciably. Or, if Access supports FULLTEXT indices, then put one on the Description field and doMATCH(Description) AGAINST ('blah')