Board index » delphi » What is the best way to connect two queries which has Not Exists/In Relation with an OK performance?

2006-07-27 04:38:30 AM
delphi238
I try to join two fast running queries (first one has inner and left outer
join) with NOT EXIST.
The first query has inner and left outer join and runs in 1/4 of a second.
The other query is a simple one table query which runs less than a second.
But together they run about 60 seconds with Not Exists.
The Not IN never ends.
What is the least expensive way to join to queries within NOT EXIST/IN
relation?
 

I suspect union or union all will do what you want here. Here's the
basic template:
query 1
union [all]
query 2
This assumes both queries return the same fields.
Mehmet F. Erten writes:

What are the queries?
What are the query plans?
What do you mean by "together"?
--
Bill Todd (TeamB)
 

Hi,
I have three queries.
The first one runs 170 milliseconds with 20K/200K header/detail inner join.
The second query itself runs less than a second
The third a little less than a second.
But 2nd and 3rd must be Not Exist relation with the 1st one.
But if I use not exists (or not in) it hangs for ever.
So I decided (a feasible solution) to merge them first and if not a null
value I eliminate the row later in the code.
It reduces the waiting time 10 fold.
Until I create the temp tables I will create two work-tables to insert the
query results into them and merge the 1s query with two work files.
I took the selected columns out to make it easy to read the following query
except to be checked columns (if not null eliminate: sku and item_length)
It runs in 172 seconds with IB selected PLANs and indexes.
I hope a Stored Procedure will help me drop the work tables (user name
suffixed), run the queries (1.2 seconds), insert the results into
work-tables
and run the following query (172 seconds) less than 3 to 5 seconds which is
very acceptable (currently 5K it can be more than a minute)
SELECT *, fandiunfilledorders.sku, unfilledorders.item_length
FROM ORDER_detail INNER JOIN order_header
ON ORDER_DETAIL.CLIENT=ORDER_HEADER.CLIENT
AND ORDER_DETAIL.STORE=ORDER_HEADER.STORE AND
ORDER_DETAIL.ORDERS=ORDER_HEADER.ORDERS
left outer join unfilledorders on ORDER_DETAIL.CLIENT=unfilledorders.CLIENT
AND ORDER_DETAIL.STORE=unfilledorders.STORE AND
ORDER_DETAIL.ORDERS=unfilledorders.ORDERS
left outer join fandiunfilledorders on
ORDER_DETAIL.CLIENT=fandiunfilledorders.CLIENT
AND ORDER_DETAIL.STORE=fandiunfilledorders.STORE AND
ORDER_DETAIL.ORDERS=fandiunfilledorders.ORDERS
left outer join comment on ORDER_HEADER.SURROGATE_NO = comment.surrogate_no
and comment.line = 7
WHERE ORDER_HEADER.WAREHOUSE = 'Wilmington_MA' AND
(ORDER_DETAIL.INSTALLATION_DATE_YYMMDD
IS NULL OR ORDER_DETAIL.INSTALLATION_DATE_YYMMDD<='000000') AND
ORDER_DETAIL.ORDER_ITEM_TYPE = 'L'
AND ORDER_DETAIL.SKU <>'CHARGE BACK' AND (ORDER_DETAIL.ORDER_STATUS
IS NULL OR ORDER_DETAIL.ORDER_STATUS=''OR ORDER_DETAIL.ORDER_STATUS='Stand
By'
OR ORDER_DETAIL.ORDER_STATUS='To Be Done At' OR
ORDER_DETAIL.ORDER_STATUS='Quick Install')
AND (ORDER_DETAIL.ADDITIONALPOFLAG IS NULL OR ORDER_DETAIL.ADDITIONALPOFLAG
<>'Y') AND
(ORDER_DETAIL.ADDITIONALPOINFLAG IS NULL OR
ORDER_DETAIL.ADDITIONALPOINFLAG<>'Y')
PLAN SORT (JOIN (JOIN (JOIN (JOIN (ORDER_DETAIL INDEX
(ORDER_DETAIL_INSTALLATION_DATE_YYMMDD_INDEX,ORDER_DETAIL_INSTALLATION_DATE_YYMMDD_INDEX),ORDER_HEADER
INDEX (RDB$PRIMARY35)),UNFILLEDORDERS INDEX
(UNFILLEDORDERS_INDEX)),FANDIUNFILLEDORDERS INDEX
(FANDIUNFILLEDORDERS_INDEX)),COMMENT INDEX (RDB$PRIMARY7)))
 

If you cannot convert the LEFT OUT JOINs to inner joins then I think
that a stored procedure is the most likely way to improve performance.
--
Bill Todd (TeamB)