I'm writing a stored procedure on SQL Server 2000.
I've written a complicated select statement that takes 18 seconds to run.
The question is, how can I self-join the result correctly and efficiently?
我正在SQL Server 2000上编写一个存储过程。我编写了一个复杂的select语句，需要18秒才能运行。问题是，如何才能正确有效地自我加入结果？
Is it possible to manage it in a single select statement without repeating the big query statement that I currently have? Or should I save the result in a table variable or temp table?
Many thanks.
是否可以在单个select语句中管理它而不重复我目前拥有的大查询语句？或者我应该将结果保存在表变量或临时表中？非常感谢。
1 个解决方案
#1
4
If the original query runs a long time, but the result is small, yes, you could consider a temporary / helper table, and run your second query (the self-join) on that table.
如果原始查询运行时间很长，但结果很小，是的，您可以考虑临时/帮助程序表，并在该表上运行第二个查询（自联接）。
This may also help keep your approach modular / maintainable. And you'll be able to tune performance on that long-running query without your second one staring you in the face in the meantime.
这也可能有助于保持您的方法模块化/可维护。而且你可以在那个长时间运行的查询中调整性能而不会让你的第二个人同时盯着你。
#1
4
If the original query runs a long time, but the result is small, yes, you could consider a temporary / helper table, and run your second query (the self-join) on that table.
如果原始查询运行时间很长，但结果很小，是的，您可以考虑临时/帮助程序表，并在该表上运行第二个查询（自联接）。
This may also help keep your approach modular / maintainable. And you'll be able to tune performance on that long-running query without your second one staring you in the face in the meantime.
这也可能有助于保持您的方法模块化/可维护。而且你可以在那个长时间运行的查询中调整性能而不会让你的第二个人同时盯着你。