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