Solved: Simplify SQL

[JimmyTheHand]

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.
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.

 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

[Demosthine]

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

[JimmyTheHand]

Hello Scott

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

Jimmy

[Demosthine]

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:

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

[JimmyTheHand]

Good Morning Jimmy.

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

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.

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

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.
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.
And there is no driver update available. So I must stick with the slow version.
Such is life

Nevertheless, thanks for your support.

Jimmy

[JimmyTheHand]

As promised, the modified SQL query was:

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%.