加快 SQL Server 中的临时表连接速度
我有 2 个临时表#temp1 和#temp。两者都有一个键和日期列。两者都有大约 25k 行。我根据所有行中唯一的键和日期加入它们。此连接大约需要 4 分钟才能完成。有什么办法可以加快速度或者有其他方法吗？
I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative methods?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我相信您可以像在任何其他表上一样在临时表上创建索引。
I believe you can create indexes on temporary tables as on any other tables.
如果您连接
25k表需要4分钟，则说明有问题。很可能您输入了错误的
JOIN条件，导致笛卡尔连接（或类似的连接），从而导致返回25k * 25k = 625M记录。这实际上可能需要
4分钟（如果不是更长的话），但我认为这不是您想要的。可能您的查询中有
DISTINCT/GROUP BY子句，这使得查询以非最佳方式返回正确的结果集。能否请您发表您的疑问，以便我能告诉您确切的原因？
If your join of
25ktables takes4minutes, there's something wrong with it.Most probably you put a wrong
JOINcondition which leads to a cartesian join (or something close to it), which results in25k * 25k = 625Mrecords returned.This can take
4minutes indeed if not more, but I don't think it was what you wanted.Probably you have
DISTINCT/GROUP BYclauses in your query, which makes the query to return correct resultset but in a non-optimal way.Could you please post your query so that I can tell the exact reason?
我怀疑您的整个查询可能需要查看。可能还有另一种方法可以给这只兔子剥皮。
您能留下更多细节吗？
I have a suspicion that your whole query might want looking at. There may be another way to skin this particular rabbit.
Could you leave some more detail?
根据您正在做的事情，您可能可以完全避免临时表并拥有基于集合的解决方案（它将运行得更快并且扩展得更好），但是如果不知道您的查询是什么，就很难知道这一点。
depending on what you're doing you could probably avoid the temp tables altogether and have a set-based solution (which will run a lot quicker and scale better), but that's hard to know without any idea of what your query is.