使用 SQL Developer 在 SQL Server 數據庫中執行參數化查詢需要 30 多分鐘，非參數化需要 60 秒

我是一名java開發人員。我對數據庫部分沒有更深入的了解（也許我應該有）。我正在從 java 向 SQL Server 數據庫發出幾個複雜的查詢，它返回數據的速度非常快。

所有查詢都是參數化查詢&我PreparedStatement在 java 中使用這意味著查詢在發送到數據庫之前是預編譯的。一切都很好，但只有一個查詢需要 30 多分鐘。

奇怪的是，當我在 SQL Developer 中通過提供所有參數（即沒有？同樣，如果我在 java 中對沒有任何參數的查詢進行硬編碼，則需要 60-70 秒才能返回數據。

如果在 SQL Developer 中，我通過輸入 :parameter 並在提示時提供值來使其參數化，那麼它再次需要 30 分鐘才能執行。

誰能幫助我為什麼會發生這種奇怪的行為？

**編輯：**對不起，我沒有執行計劃。這也不是間歇性問題。我只有對數據庫的讀取權限。

這是查詢：

SELECT DISTINCT
 sectionslist.*,
 persons.ID                      AS pid,
 persons.PREFERRED_EMAIL_ADDRESS AS ologin,
 persons.FIRST_NAME              AS given,
 persons.LAST_NAME               AS family,
 persons.MIDDLE_NAME             AS middle,
 persons.PREFERRED_NAME          AS fn,
 persons.PREFERRED_EMAIL_ADDRESS AS email,
 addr.COUNTRY                    AS country,
 addr.ADDRESS_LINE_1             AS line1,
 addr.ADDRESS_LINE_2             AS line2,
 NULL                            AS line3,
 addr.CITY                       AS city,
 addr.STATE                      AS state,
 addr.ZIP                        AS zip,
 persons.PERSONAL_PHONE_NUMBER   AS phone,
 'instructor'                    AS rolename
FROM
 (
   SELECT
     sections.SEC_NO               AS oid,
     sections.SEC_SHORT_TITLE      AS odescription,
     sections.SEC_NO               AS shortdescription,
     terms.TERMS_ID                AS term_id,
     terms.TERM_DESC               AS term_short,
     NULL                          AS division_id,
     NULL                          AS division_short,
     dep.DEPTS_ID                  AS department_id,
     dep.DEPTS_ID                  AS department_short,
     courses.CRS_NO                AS course_id,
     courses.CRS_NO                AS course_short,
     courses.CRS_SHORT_TITLE       AS course_descr,
     courses.CRS_NO                AS course_number,
     sections.SEC_CURRENT_STATUS   AS section_status,
     sections.SEC_NO               AS section_number,
     sections.SEC_CAPACITY         AS estimated_enrollment,
     sections.ACTIVE_STUDENT_COUNT AS actual_enrollment,
     sections.SEC_CAPACITY         AS section_capacity,
     NULL                          AS section_included,
     NULL                          AS section_key,
     sections.CAMPUS_CODE,
     instr.CSF_FACULTY
   FROM
     (
       SELECT
         fcs.*,
         (
           CASE
             WHEN SEC_NO LIKE 'D%'
             THEN 'D'
             WHEN SEC_NO LIKE '__V%'
             THEN 'V'
             ELSE SEC_LOCATION
           END) AS CAMPUS_CODE
       FROM
         COURSE_SECTIONS fcs
     )
     sections
   LEFT JOIN
     (
       SELECT DISTINCT
         dep.*,
         (
           CASE
             WHEN SEC_NO LIKE 'D%'
             THEN 'D'
             WHEN SEC_NO LIKE '__V%'
             THEN 'V'
             ELSE SEC_LOCATION
           END) AS CAMPUS_CODE
       FROM
         DEPTS dep ,
         COURSE_SECTIONS
       WHERE
         DEPTS_ID=SEC_SUBJECT
     )
     dep
   ON
     sections.SEC_SUBJECT  =dep.DEPTS_ID
   AND sections.CAMPUS_CODE=dep.CAMPUS_CODE
   LEFT JOIN TERMS terms
   ON
     sections.SEC_TERM=terms.TERMS_ID
   LEFT JOIN
     (
       SELECT
         crs.*,
         (
           CASE
             WHEN SEC_NO LIKE 'D%'
             THEN 'D'
             WHEN SEC_NO LIKE '__V%'
             THEN 'V'
             ELSE SEC_LOCATION
           END) AS CAMPUS_CODE
       FROM
         COURSES crs,
         COURSE_SECTIONS
       WHERE
         COURSES_ID=SEC_COURSE
     )
     courses
   ON
     courses.COURSES_ID   =sections.SEC_COURSE
   AND courses.CRS_SUBJECT=sections.SEC_SUBJECT
   LEFT JOIN
     (
       SELECT
         fa.*,
         sec.*
       FROM
         CRSE_SEC_FACULTY fa,
         COURSE_SECTIONS sec
       WHERE
         sec.COURSE_SECTIONS_ID=fa.CSF_COURSE_SECTION
       AND PRIMARY_IND         ='Y'
     )
     instr
   ON
     instr.CSF_COURSE_SECTION=sections.COURSE_SECTIONS_ID
   AND instr.SEC_TERM        =sections.SEC_TERM
   AND instr.SEC_SUBJECT     =sections.SEC_SUBJECT
   WHERE
     NOT sections.SEC_COURSE       IS NULL
   AND sections.SEC_CURRENT_STATUS IN ('A')
   AND sections.SEC_TERM            = '17/SP'
   AND sections.CAMPUS_CODE        IN ('C','D','V')
 )
 sectionslist
LEFT JOIN
 (
   SELECT
     fp.*,
     ph.PERSONAL_PHONE_NUMBER
   FROM
     PERSON fp,
     PERSON_PHONE ph
   WHERE
     fp.ID=ph.ID
 )
 persons
ON
 sectionslist.CSF_FACULTY=persons.ID
LEFT JOIN ADDRESS addr
ON
 addr.ADDRESS_ID=persons.PREFERRED_ADDRESS 

當我在 url 字元串中添加以下參數時，性能得到了顯著提高sendStringParametersAsUnicode=false。以下文章對此進行了解釋：

http://emransharif.blogspot.com/2011/07/performance-issues-with-jdbc-drivers.html

我花了一點時間，但我能夠通過 SQL Developer 連接到我的本地 SQL Server。您看到的問題可能是由驅動程序引起的。您發送到 SQL Server 的 SQL 不一定是您所期望的。假設我通過 SQL Developer 向 SQL Server 送出以下查詢：

SELECT MAX(h1.N + h2.N)
FROM X_1000000_NUM_1 h1
CROSS JOIN X_1000000_NUM_2 h2
WHERE H1.N < 10000;

該查詢的詳細資訊並不重要。我只是想要一些不會太快完成的東西，這樣我就可以輕鬆地觀察 SSMS 中正在執行的查詢。這是根據以下實際執行的查詢sp_whoisactive：

SELECT MAX(h1.N + h2.N)
FROM X_1000000_NUM_1 h1
CROSS JOIN X_1000000_NUM_2 h2
WHERE H1.N < 10000

好的，到目前為止一切順利。但是，如果我們在 SQL Developer 中使用綁定變數會發生什麼？假設我通過 SQL Developer 向 SQL Server 送出以下查詢，綁定變數值為 10000：

SELECT MAX(h1.N + h2.N)
FROM X_1000000_NUM_1 h1
CROSS JOIN X_1000000_NUM_2 h2
WHERE H1.N < :i;

以下是 SQL Server 執行的內容：

SELECT MAX(h1.N + h2.N)
FROM X_1000000_NUM_1 h1
CROSS JOIN X_1000000_NUM_2 h2
WHERE H1.N <  @P0

查詢現在不同了。SQL Server 執行的查詢有一個局部變數。在 SQL Server 中用局部變數替換硬編碼文字會對性能產生顯著影響。這主要是因為 SQL Server 在創建查詢計劃之前不會知道變數的值。如果 SQL Server 可以訪問更多資訊，它通常可以創建性能更好的查詢計劃。使用局部變數（隱藏資訊）可能會影響基數估計並導致計劃不理想。這在評論中提供的連結中被提及為可能的原因。

解決此問題的兩種最直接的方法是避免使用局部變數（對您而言，這意味著不使用綁定變數）或RECOMPILE在查詢中添加提示。該RECOMPILE提示允許 SQL Server 使用變數的實際值創建查詢計劃，但代價是無法使用以前記憶體的計劃。如果您的查詢每天執行數千次或更多次，RECOMPILE可能會增加太多成本。

我敦促您通過觀察在 SQL Server 上實際執行的查詢文本來解決性能問題。也許您或其他人可以通過 SSMS 訪問伺服器，並且可以進行並行測試。

引用自：https://dba.stackexchange.com/questions/160520