預編譯sql查詢語句
In this article, we will explain what compilations and recompilations are, and give recommendations for creating reusable queries to keep the pressure off your processor.
在本文中，我們將解釋什么是編譯和重新編譯，并提供有關創建可重用查詢的建議，以減輕處理器的負擔。
數據庫防止sql注入？A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. This query execution plan is then stored in cache and can be quickly accessed.
編譯是基于當前數據庫和數據庫對象狀態優化存儲過程的查詢執行計劃時的過程。 該查詢執行計劃然后存儲在緩存中，并且可以快速訪問。
When a query is executed, it’s sent to the parser first. The parser checks the query syntax and stops the execution if the syntax is incorrect.
執行查詢后，它將首先發送到解析器。 語法分析器檢查查詢語法，如果語法不正確，則停止執行。
?
SELECT *FROM person.address;
INERT INTO Person.Address1 (AddressID, AddressLine1, AddressLine2)
VALUES(1, N'1970 Napa St.', NULL)sqlserver查看執行過的語句？If multiple syntax errors exist in the submitted query (in this example both in the first and the third line), the parser will stop when it reaches the first one.
如果提交的查詢中存在多個語法錯誤（在此示例中，第一行和第三行均如此），則解析器在到達第一個查詢時將停止。
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘*’.
Msg 102，第15級，狀態1，第1行
'*'附近的語法不正確。
jdbclikesql注入，When the syntax is correct, the query goes further to the algebrizer. The algebrizer finds all objects used in the query, verifies their names, finds the data types used, checks whether aggregate functions are used, and based on the information collected creates a syntax-based optimization.
當語法正確時，查詢將進一步進入代數器。 代數查詢器查找查詢中使用的所有對象，驗證其名稱，查找使用的數據類型，檢查是否使用了聚合函數，并基于收集的信息創建基于語法的優化。
A query compilation consists of the processes executed in the parser and algebrizer. A complied plan is saved in cache.
查詢編譯包含在解析器和代數器中執行的過程。 符合計劃的計劃保存在緩存中。
sql無法預編譯？A recompilation is the same process as a compilation, just executed again. If the database structure or data change significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.
重新編譯與編譯的過程相同，只是再次執行。 如果數據庫結構或數據發生重大變化，則需要重新編譯以創建新的查詢執行計劃，該計劃對于新的數據庫狀態將是最佳的，并確保更好的過程性能。
A recompilation degrades SQL Server performance, as SQL Server is performing the same action multiple times, instead of using its resources for other important actions. If you could use the cached execution plan, instead of recompiling the stored procedure again, that would make query execution faster for the time needed for a recompilation.
重新編譯會降低SQL Server的性能，因為SQL Server會多次執行同一操作，而不是將其資源用于其他重要操作。 如果可以使用緩存的執行計劃，而不必再次重新編譯存儲過程，則可以使查詢的執行速度更快，達到重新編譯所需的時間。
mysql sql注入？Therefore, it’s recommended to have reusable query execution plans.
因此，建議制定可重復使用的查詢執行計劃。
Recompilation cannot be completely eliminated, but you should watch for a large number of recompilations which indicates that queries are reused, but their query execution plan is not. Also, a large number of compilations should be investigated, as that means that new queries are excessively compiled, and not reused. In some cases, it’s recommended to check the compilation/recompilation ratio.
重新編譯不能完全消除，但是您應該注意大量的重新編譯，這表明查詢已被重用，但查詢執行計劃卻沒有。 另外，應調查大量的編譯，因為這意味著新的查詢會被過度編譯，而不會被重用。 在某些情況下，建議檢查編譯/重新編譯比率。
sqlserver日志，If the recompilations occur more than expected, find the stored procedures that are frequently recompiled. You can use SQL Profiler, or a SQL Server monitoring tool. Then determine why the stored procedure is frequently recompiled instead of reused from cache, and finally fix the problem.
如果重新編譯發生的次數超出預期，請查找經常重新編譯的存儲過程。 您可以使用SQL事件探查器或SQL Server監視工具。 然后確定為什么要頻繁地重新編譯存儲過程而不是從高速緩存中重新使用存儲過程，最后解決該問題。
One of the mechanisms that SQL Server uses to provide compiled queries to be reused is using parameterization. For example, we will create a query execution plan for the following query that contains the specific value for the AddressID.
SQL Server用于提供要重用的已編譯查詢的機制之一是使用參數化。 例如，我們將為以下查詢創建查詢執行計劃，該查詢包含AddressID的特定值。
?
DELETE FROM Person.Address1WHERE AddressID = 100000判斷sql是否執行成功、If no parameterization was used, it would mean that each time the same query is executed, but with different values for the AddressID, it would have to be recompiled. As shown in the query execution plan, this is not the case, as the @1 parameter is used instead of the exact value, so the query execution plan can be reused for every AddressID value.
如果未使用參數化，則意味著每次執行相同的查詢，但AddressID的值不同時，都必須重新編譯。 如查詢執行計劃中所示，情況并非如此，因為使用@ 1參數代替確切的值，因此可以對每個AddressID值重用查詢執行計劃。
As described above, a compiled query execution plan is stored in cache.
db2 sql3114？ 如上所述，已編譯的查詢執行計劃存儲在高速緩存中。
The queries are automatically recompiled when:
在以下情況下，查詢將自動重新編譯：
A query is executed using a RECOMPILE query hint.
sqlserver查看語句執行記錄、 使用RECOMPILE查詢提示執行查詢。
There are three query hints that define query execution plan use. Query hints are executed on the statement level, so they don’t affect the whole stored procedure or query.
有三個查詢提示定義了查詢執行計劃的使用。 查詢提示在語句級別執行，因此它們不會影響整個存儲過程或查詢。
RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled. This is an alternative to using the WITH RECOMPILE clause in stored procedures; it is useful if you want to recompile only some of the statements in the stored procedure, not all of them.
RECOMPILE –指定在執行查詢后，從緩存中刪除存儲在緩存中的查詢執行計劃。 當再次執行同一查詢時，緩存中將沒有現有計劃，因此必須重新編譯該查詢。 這是在存儲過程中使用WITH RECOMPILE子句的替代方法。 如果只想重新編譯存儲過程中的某些語句，而不是全部，則很有用。
?
CREATE PROCEDURE <name>
Query1
Query2 OPTION (RECOMPILE)
Query3For example:
例如：
?
SELECT *
FROM Sales.SalesOrderDetail OPTION (RECOMPILE)KEEP PLAN – specifies that a query execution plan is not recompiled when it normally is. When the table index column changes due to an INSERT, DELETE, UPDATE, or MERGE statement, the query execution plan is recompiled. The number of changed rows that triggers a recompilation is different for temporary and permanent tables and depends on the number of rows in the table. The threshold value is higher for permanent tables. For example, if a temporary table has less than 6 rows, the plan will be recompiled if 6 new rows are added. For the permanent table with 6 rows, 500 new rows must be added to trigger a recompilation. The KEEP PLAN raises this threshold, so the recompilations occur less frequently.
KEEP PLAN –指定在正常情況下不重新編譯查詢執行計劃。 當表索引列由于INSERT，DELETE，UPDATE或MERGE語句而更改時，將重新編譯查詢執行計劃。 臨時表和永久表的觸發重新編譯的已更改行數不同，并且取決于表中的行數。 永久表的閾值較高。 例如，如果一個臨時表少于6行，則如果添加6個新行，則將重新編譯該計劃。 對于具有6行的永久表，必須添加500個新行以觸發重新編譯。 KEEP計劃提高了此閾值，因此重新編譯的頻率降低了。
?
CREATE TABLE #t (Id INT, Address1 [nvarchar](60), Address2 [nvarchar](60), Town [nvarchar](30))INSERT #t
SELECT [AddressID], [AddressLine1], [AddressLine2], [City]
FROM Person.AddressSELECT count(*)
FROM #t WHERE ID = 37 OPTION (KEEP PLAN)KEEPFIXED PLAN – specifies that a query execution plan is never recompiled due to index column changes and changes in statistics. When this query hint is used, the plan is recompiled only if the schema of tables used in the query is changed or the sp_recompile stored procedure is executed on these tables. The example above applies here as well; the only difference is OPTION (KEEPFIXED PLAN) instead of OPTION (KEEP PLAN).
KEEPFIXED PLAN –指定永遠不要由于索引列更改和統計信息更改而重新編譯查詢執行計劃。 使用此查詢提示時，僅當更改查詢中使用的表的模式或對這些表執行sp_recompile存儲過程時，才重新編譯計劃。 上面的示例也適用于此。 唯一的區別是OPTION（保持計劃）而不是OPTION（保持計劃）。
A WITH RECOMPILE option us used in a CREATE PROCEDURE statement or in an EXECUTE statement when the procedure is called
在調用過程時，在CREATE PROCEDURE語句或EXECUTE語句中使用WITH WITH COMCOMLE選項
?
CREATE PROCEDURE Person.AddressLines @AdrID INT = '32'WITH RECOMPILE
AS
SELECT *
FROM Person.Address
WHERE AddressID = @AdrID;
GO
The query execution plan is not stored in cache after the stored procedure is executed, so it will be recompiled each time it’s executed
執行存儲過程后，查詢執行計劃未存儲在緩存中，因此每次執行時都會重新編譯查詢計劃
When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache. If there is already a plan in cache for this specific stored procedure, it’s intact.
當在EXECUTE語句中使用WITH RECOMPILE選項執行存儲過程時，將創建一個新的查詢執行計劃并將其用于此特定執行，但不會存儲在緩存中。 如果緩存中已經有針對該特定存儲過程的計劃，則該計劃是完整的。
?
EXECUTE dbo.uspGetBillOfMaterials WITH RECOMPILEThe sp_recompile system stored procedure is used
使用了sp_recompile系統存儲過程
The stored procedure removes an existing query execution plan for a specific stored procedure or query from cache, so they are recompiled the next time they are called.
存儲過程從高速緩存中刪除了特定存儲過程或查詢的現有查詢執行計劃，因此在下次調用它們時會對其進行重新編譯。
?
EXEC sp_recompile N'dbo.uspGetBillOfMaterials'When the stored procedure is executed, the following message is shown
當執行存儲過程時，顯示以下消息
Object ‘dbo.uspGetBillOfMaterials’ was successfully marked for recompilation.
對象“ dbo.uspGetBillOfMaterials”已成功標記為重新編譯。
In this article, we explained what compilations, recompilations, and parameterization are. We showed how to recompile a query using T-SQL query hints, options, and stored procedures. In the next part of this article, we will show how to detect frequently recompiled queries.
在本文中，我們解釋了什么是編譯，重新編譯和參數化。 我們展示了如何使用T-SQL查詢提示，選項和存儲過程重新編譯查詢。 在本文的下一部分中，我們將展示如何檢測經常重新編譯的查詢。
翻譯自: https://www.sqlshack.com/frequent-query-recompilations-sql-query-performance-killer-introduction/
預編譯sql查詢語句
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
