How to improve performance of a UNION QUERY

[MLZ]

Hi,

I am trying to improve the performance of a SQL Pass-Through query that supposed to retrieve two sets of data:

1) Newly added documents
2) Newly added change documents

The query below executes under 5 minutes for small number of records that are being compared. But it runs for half an hour, if the number of records are being queried is over 10,000.

There is an index on the table being searched. The indexed columns are the load_date and reportgroup.

Instead of UNION, I tried "OR"-in the two NOT EXISTS clause but the performance for did not improve.

Is there another way I can re-write this query ? Any suggestions would be appreciate.

Here's the query:


SELECT PREFIX, REVISION, DOC_TYPE, ISSUED_DATE, CHANGE_DOCUMENT,
CHANGE_REVISION, LOAD_DATE, 'Added Documents' AS Delta_Type
from wtp_techdelta_view new
where REPORTGROUP='ABCDE'
and load_date = to_date('14-JAN-2004','DD-MON-YYYY')
and not exists ( SELECT 'X' FROM wtp_techdelta_view old
WHERE old.load_date = to_date('13-JAN-2004','DD-MON-YYYY') AND REPORTGROUP='ABCDE'
AND old.prefix = new.prefix and old.revision = new.revision
and nvl(old.doc_type,'X') = nvl(new.doc_type,'X')
and nvl(old.issued_date,'X') = nvl(new.issued_date,'X') )
UNION
SELECT new.PREFIX, new.REVISION, new.DOC_TYPE, new.ISSUED_DATE, new.CHANGE_DOCUMENT,
new.CHANGE_REVISION, new.LOAD_DATE,'Added Change Docs' as Delta_Type
from wtp_techdelta_view new
WHERE REPORTGROUP='ABCDE'
and load_date = to_date('14-JAN-2004','DD-MON-YYYY')
and new.change_document is not null
AND NOT EXISTS ( SELECT 'X' FROM wtp_techdelta_view old
WHERE old.load_date = to_date('13-JAN-2004','DD-MON-YYYY') AND REPORTGROUP='ABCDE'
and old.prefix = new.prefix and old.revision = new.revision
and nvl(old.doc_type,'X') = nvl(new.doc_type,'X')
and nvl(old.issued_date,'X') = nvl(new.issued_date,'X')
and nvl(new.change_document,'X') = nvl(old.change_document,'X')
and nvl(new.change_revision,'X') = nvl(old.change_revision,'X') )

 

[dhookom]

If you don't need to worry about duplicates between the two selects, you can use UNION ALL
...
and nvl(old.issued_date,'X') = nvl(new.issued_date,'X') )
UNION ALL
SELECT new.PREFIX, new.REVISION, new.DOC_TYPE, new.ISSUED_DATE, new.CHANGE_DOCUMENT,
...



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MLZ]

Thanks Duane. I tried but it is still running slow. In fact, half an hour had passed and it was still running.

By change you have other suggestions?

--mlz

 

[dhookom]

The NOT EXISTS is really slow. I am not a guru when it comes to left and right joins but you might be able to get rid of the NOT EXISTS and replace it with a RIGHT JOIN and check for NULL in one of the fields. You also have lots of functions that we have no idea what they are doing and why.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[r937]

MLZ, you will stand a better chance of getting answers in the oracle forum

it's a pass-through query; by definition, it's not access



rudy

http://r937.com/

SQL Consulting

 

[dhookom]

r937,
Great idea. I missed the "pass-through" and don't work with Oracle where I assume "to_date" etc are built-in functions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MLZ]

Thanks to all your responses. I have gone to the Oracle forum, but after some testing last night, I realized that the SQL Pass-Through query was not the one running slow but the fact that my application is running the query as a regular SELECT query. The application is running it as a SELECT query because I have to incorporate the real-time parameters (i.e. dates, etc.).

So the query I should have posted instead is:

" Select PREFIX, REVISION, DOC_TYPE, ISSUED_DATE, CHANGE_DOCUMENT,CHANGE_REVISION, LOAD_DATE,
""Added Documents"" AS Delta_Type " & _
" from " & qryValue3 & " new where load_date = #" & qryValue1 & "#" & _
" and new.prefix not in ( SELECT old.prefix FROM " & qryValue3 & " old " & _
" WHERE old.load_date = #" & qryValue2 & "# AND old.prefix = new.prefix and old.revision = new.revision " & _
" and nz(old.doc_type,'X') = nz(new.doc_type,'X')
and nz(old.issued_date,'X')
= nz(new.issued_date,'X') ) " & _
" UNION " & _
" SELECT new.PREFIX, new.REVISION, new.DOC_TYPE, new.ISSUED_DATE, new.CHANGE_DOCUMENT,new.CHANGE_REVISION, new.LOAD_DATE,""Added Change Docs"" as Delta_Type " & _
" from " & qryValue3 & " new where load_date = #" & qryValue1 & "#" & _
" and new.change_document is not null AND new.prefix not in ( SELECT old.prefix FROM " & qryValue3 & " old " & _
" WHERE old.load_date = #" & qryValue2 & "# and old.prefix = new.prefix and old.revision = new.revision " & _
" and nz(old.doc_type,'X') = nz(new.doc_type,'X')
and nz(old.issued_date,'X') = nz(new.issued_date,'X')
and nz(new.change_document,'X') =
nz(old.change_document,'X')
and nz(new.change_revision,'X') =
nz(old.change_revision,'X') ) "

WHERE qryValue3 is the name of a SQL Pass-Through query that retrieves only the interesting records from an Oracle table; qryValue1 is the real-time New Date parameter and qryValue2 is the real-time Old Date parameter.

It appears that if the SELECT query processes > 1,000 records, the user's workstation uses up 100% of the CPU and thus causing the slow performance. Is there a way I can make the SELECT query run faster with such volume of records ?

When I ran the above query as a SQL Pass-Through query, it run for 4 min. retrieving the same amount of records ( > 1,000). To incorporate the real-time parameters, I used QueryDef. If I continue this path and since > 1 user runs my application, is there not a chance that > 1 user can execute the code that does the QueryDef and thus overwrite each other and get unpredictable results ? Can I avoid this from happening ? Please advise.

Thanks,
mlz

 

[dhookom]

I always recommend that each user has his/her own copy of your front end mdb. You can then modify the sql of the pass-through query to include your parameters. In its simplest form (using DAO)

Dim strSQL as String
strSQL = "SELECT.... WHERE SomeDate >='" & Me.txtStart & _
&quot;' AND SomeDate<='&quot; & me.txtEnd & &quot;'&quot;
CurrentDb.QueryDefs(&quot;qsptYours&quot.SQL = strSQL


Duane
MS Access MVP
Find out how to get great answers faq219-2884.