Parameterized query is taking more than 30 min to execute in SQL Server Database using SQL Developer, non-parameterized taking 60sec

Question

I am a java developer. I don't have a deeper knowledge on the database part (may be I should have). I am firing several complex queries from java to a SQL Server database & it is returning data pretty fast.

All the queries are parameterized queries & I am using PreparedStatement in java that means the query is precompiled before sending to the database. All is fine but for only one query is taking more than 30 min.

Strangely when I am writing the query in SQL Developer by giving all the parameter (i.e no ? or :parameter in the sql) it is taking between 60 sec - 114 sec. Similarly if I hard-code the query without any parameter in java it is taking 60-70 sec to return data.

If, in SQL Developer, I make it parameterized by putting :parameter & supply the value when prompted then it is again taking 30 min to execute.

Can anyone help me why this strange behaviour is happening?

Edit: I am sorry I don't have the execution plans. Also this is not a intermittent issue. I have only read access to the database.

Here is the query:

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 

Answer 1

It took me a little while but I was able to connect to my local SQL Server through SQL Developer. The issues that you are seeing are probably caused by the driver. The SQL that you are sending to SQL Server may not necessarily be what you expect. Let's suppose I submit the following query through SQL Developer to 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;

The details of that query aren't important. I just wanted something that wouldn't finish too quickly so I could easily observe the running query in SSMS. Here is the query actually executed according to 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

Okay, so far so good. However, what happens if we use a bind variable in SQL Developer? Let's suppose I submit the following query through SQL Developer to SQL Server with a bind variable value of 10000:

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

Here is what is executed by 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

The queries are now different. The query run by SQL Server has a local variable. Replacing a hardcoded literal with a local variable in SQL Server can have a dramatic effect on performance. This is mainly because SQL Server will not know the values of the variables before creating a query plan. SQL Server often can create better performing query plans if it has access to more information. The use of local variables (hiding information) can throw off cardinality estimations and lead to a suboptimal plan. This is mentioned as a possible cause in the link provided in the comments.

The two most straightforward ways to work around this problem are to avoid using local variables (for you this means not using bind variables) or do add a RECOMPILE hint to the query. The RECOMPILE hint allows SQL Server to create a query plan with the real values of the variables at the cost of not being able to use a previously cached plan. If your queries are run thousands or more times a day RECOMPILE could add too much overhead.

I urge you to troubleshoot performance by observing the query text that is actually executed on SQL Server. Maybe you, or someone else, can access the server through SSMS and you can do side-by-side testing.

Answer 2

The performance got improved significantly when I added the below parameter in the url string sendStringParametersAsUnicode=false . The following post explains about it:

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