Solved: Simplify SQL

Hi

Can you make this SQL query any simpler? I highlighted identical parts with different colors. I thought of using WITH clause, but couldn't figure out how. :dunno
In fact, the length of code doesn't bother me, but the length of time it requires to run does. So I'm looking for, essentially, a faster solution.

Code:

---

SELECT s.id_numeric, s.sample_name, s.login_date, s.dateresreq, t.analysis, t.test_number,
            NVL2(
            (SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    (value_before Is NULL Or value_before = 'U') And
                    value_after = 'V' And
                    record_key0 = t.test_number),
            (SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    (value_before Is NULL Or value_before = 'U') And
                    value_after = 'V' And
                    record_key0 = t.test_number
                    ),
            s.login_date)                                                  As Date_Test_Available,
            (SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    value_before IN ('V', 'P') And
                    value_after = 'C' And
                    record_key0 = t.test_number)                            As Date_Test_Completed,
            (SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    value_before IN ('V', 'P') And
                    value_after = 'C' And
                    record_key0 = t.test_number)
            -(SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    (value_before Is NULL Or value_before = 'U') And
                    value_after = 'V' And
                    record_key0 = t.test_number)            As WaitTime_Completed,
            s.dateresreq-s.login_date                        As DeadlineTerm,

    FROM sample s
    JOIN test t
      ON s.id_numeric = t.sample
    JOIN versioned_analysis va
      ON t.analysis = va.identity
  WHERE va.group_id = :parameter1 And
            (SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    value_before IN ('V', 'P') And
                    value_after = 'C' And
                    record_key0 = t.test_number)
            BETWEEN to_date(:parameter2, 'DD-MON-YY') AND to_date(:parameter3, 'DD-MON-YY')+1

---

Thanks,

Jimmy

Evening Jimmy.

I don't know of any way to make that single statement any shorter or quicker, but when you are reusing that type of Sub-Query so many times, I would create a pre-defined Query. Then, you should be able to reference that instead of having it hard-coded.

Take care.
Scott

Hello Scott

Do you, by 'predefined Query', mean a view? Or a stored procedure?

Jimmy

Good Morning Jimmy.

I wasn't sure what actual software you were using, but that statement leads me to believe you are working with a legitimate SQL Server rather than Access or the likes.

In that case, I would mean a View.

For example, your first Sub-Query (the blue one), would look like:

Code:

---

CREATE VIEW [Before Null] AS
SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    (value_before Is NULL Or value_before = 'U') And
                    value_after = 'V' And
                    record_key0 = t.test_number

---

Hope this helps.
Scott

Quote:

---

Originally Posted by Demosthine

Good Morning Jimmy.

---

Funny, it's almost midnight :) Good night, Scott.

Quote:

---

I wasn't sure what actual software you were using, but that statement leads me to believe you are working with a legitimate SQL Server rather than Access or the likes.

---

Actually, it is an Oracle database.

Quote:

---

For example, your first Sub-Query (the blue one), would look like:

Code:

---

CREATE VIEW [Before Null] AS
SELECT audit_date
            FROM audit_values
            WHERE table_name = 'TEST' And
                    field_name = 'STATUS' And
                    (value_before Is NULL Or value_before = 'U') And
                    value_after = 'V' And
                    record_key0 = t.test_number

---

---

I'm a bit confused about that. What will Oracle do with t.test_number? I've never heard of such use of a column. Is that something like a "conditional view"?

Anyway, in the meantime I checked the execution plan of the query and found that Oracle was executing the same sub-queries over and over again, in nested loops. That's why it was so slow.

Then I did a little research concerning the usage of WITH clause and based on my findings I reconstructed the query. It was quite successful, the execution time was reduced by about 80%. I will post the result here, in a day or so. (I don't have it with me right now.)

The only problem is, that the whole work had been in vain. :banghead:
I develop my reports with Infomaker, but they (meaning the reports) are, in daily use, called from another application. That other application uses a database driver which can not compile the SQL query if it contains a WITH clause. :angry:
And there is no driver update available. So I must stick with the slow version.
Such is life:dunno

Nevertheless, thanks for your support.

Jimmy

As promised, the modified SQL query was:

Code:

---

WITH
Test_Availability_Dates As
  (SELECT audit_date, record_key0         
  FROM audit_values       
  WHERE table_name = 'TEST' And field_name = 'STATUS' And (value_before Is NULL Or value_before = 'U') And value_after = 'V'),

Test_Completion_Dates As 
  (SELECT audit_date, record_key0
  FROM audit_values
  WHERE table_name = 'TEST' And field_name = 'STATUS' And value_before IN ('V', 'P') And value_after = 'C')
 
SELECT s.id_numeric, s.sample_name, s.login_date, s.dateresreq, t.analysis, t.test_number,
s.dateresreq-s.login_date As DeadlineTerm,

NVL2((SELECT audit_date FROM Test_Availability_Dates WHERE record_key0 = t.test_number),
    (SELECT audit_date FROM Test_Availability_Dates WHERE record_key0 = t.test_number),
    s.date_started)                                                          As Date_Test_Available,

(SELECT audit_date FROM Test_Completion_Dates WHERE record_key0 = t.test_number)              As Date_Test_Completed,

(SELECT audit_date  FROM Test_Completion_Dates WHERE record_key0 = t.test_number) -
NVL2((SELECT audit_date FROM Test_Availability_Dates WHERE record_key0 = t.test_number),
    (SELECT audit_date FROM Test_Availability_Dates WHERE record_key0 = t.test_number),
    s.date_started)                                      As WaitTime,
   
FROM sample s
JOIN test t
  ON s.id_numeric = t.sample
JOIN versioned_analysis va
  ON t.analysis = va.identity
WHERE t.status <> 'X' And
        va.group_id = :parameter1 And
        (SELECT audit_date FROM Test_Completion_Dates WHERE record_key0 = t.test_number)
          BETWEEN to_date(:parameter2, 'DD-MON-YY') AND to_date(:parameter3, 'DD-MON-YY')+1

---

And, actually, the execution time improvement was about 95%.