PDA

View Full Version : Solved: Simplify SQL



JimmyTheHand
12-12-2008, 06:39 AM
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.


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
12-13-2008, 07:05 PM
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
12-15-2008, 01:03 AM
Hello Scott

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

Jimmy

Demosthine
12-15-2008, 09:26 AM
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
12-16-2008, 11:43 AM
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. :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

JimmyTheHand
12-17-2008, 02:50 AM
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%.