选项重新编译使查询快速-好还是坏? [英] Option Recompile makes query fast - good or bad?

问题描述

我有两个SQL查询，每个查询约有2-3个INNER JOINS.我需要在它们之间做一个相交.

I have two SQL queries with about 2-3 INNER JOINS each. I need to do an INTERSECT between them.

问题在于，各个查询的运行速度很快，但相交后总共需要大约4秒钟.

Problem is that indiividually the queryes work fast, but after intersecting take about 4 seconds in total.

现在，如果我在整个查询的末尾放置一个OPTION(RECOMPILE)，则该查询可以很好地工作，并且可以非常快速地返回，并且几乎立即返回！.

Now, if I put an OPTION (RECOMPILE) at the end of this whole query, the query works great again working quite fast returning almost instantly!.

我知道选项重新填充会强制执行计划的重建，因此，如果我的耳环查询花费4秒钟更好，或者现在可以重新编译，但是花费0秒更好，那么我现在就感到困惑.

I understand that option recopile forces a rebuild of execution plan, so I am confused now if my earler query taking 4 seconds is better or now the one with recompile, but taking 0 seconds is better.

推荐答案

而不是回答您所问的问题，这是您应该做的:

Rather than answer the question you asked, here's what you should do:

更新您的统计信息:

EXEC sp_updatestats

如果这不起作用，请重建索引.

If that doesn't work, rebuild indexes.

如果这不起作用，请查看

If that doesn't work, look at OPTIMIZE FOR

这篇关于选项重新编译使查询快速-好还是坏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！