Slow Query?

[Smartin]

My Query:

UPDATE HISTORY_TABLE SET ROLLOFF_DATE = DATE()
WHERE SOURCE_SYSTEM = 'TAS' AND
HISTORY_TABLE.REC_ID NOT IN (SELECT REC_ID FROM ODCOH_TAS);

When I run just the subquery portion, it returns records instantly.

When I try to run the query above it takes forever (I have killed it
after 30 minutes several times in a row).

For what it's worth, the subquery does a select on a ODBC Oracle table,
and HISTORY_TABLE is local in the MDB. Both HISTORY_TABLE and the
subquery have only a dozen records or so.

Any ideas why this thing takes so long?

 

[John Vinson]

My Query:

UPDATE HISTORY_TABLE SET ROLLOFF_DATE = DATE()
WHERE SOURCE_SYSTEM = 'TAS' AND
HISTORY_TABLE.REC_ID NOT IN (SELECT REC_ID FROM ODCOH_TAS);

When I run just the subquery portion, it returns records instantly.

When I try to run the query above it takes forever (I have killed it
after 30 minutes several times in a row).

For what it's worth, the subquery does a select on a ODBC Oracle table,
and HISTORY_TABLE is local in the MDB. Both HISTORY_TABLE and the
subquery have only a dozen records or so.

Any ideas why this thing takes so long?

I'm surprised it's THAT long even with a cross-platform link! Try an
inner join instead:

UPDATE HISTORY_TABLE
LEFT JOIN ODCOH_TAS
ON ODCOH_TAS.Rec_ID = HISTORY_TABLE.Rec_ID
SET HISTORY_TABLE.ROLLOFF_DATE = DATE()
WHERE HISTORY_TABLE.SOURCE_SYSTEM = 'TAS' AND
ODCOH_TAS.Rec_ID IS NULL;

John W. Vinson[MVP]

 

[giorgio rancati]

Hi Smartin,

try this query
----
UPDATE DISTINCTROW HISTORY_TABLE
LEFT JOIN
ODCOH_TAS
ON HISTORY_TABLE.REC_ID = ODCOH_TAS.REC_ID
SET HISTORY_TABLE.ROLLOFF_DATE = DATE()
WHERE ODCOH_TAS.REC_ID IS NULL
AND HISTORY_TABLE.SOURCE_SYSTEM = 'TAS' ;

 

[Smartin]

My Query:

UPDATE HISTORY_TABLE SET ROLLOFF_DATE = DATE()
WHERE SOURCE_SYSTEM = 'TAS' AND
HISTORY_TABLE.REC_ID NOT IN (SELECT REC_ID FROM ODCOH_TAS);

When I run just the subquery portion, it returns records instantly.

When I try to run the query above it takes forever (I have killed it
after 30 minutes several times in a row).

For what it's worth, the subquery does a select on a ODBC Oracle table,
and HISTORY_TABLE is local in the MDB. Both HISTORY_TABLE and the
subquery have only a dozen records or so.

Any ideas why this thing takes so long?

It didn't occur to me at the time that another query, which is identical
to the first except that it selects from a different Oracle database,
runs pretty quickly. So perhaps my problem is rooted in what's going on
with the Oracle server, or database schema... or something.

I will try the join constructions you (John, giorgio) have suggested.
Thanks for the responses!

 

[giorgio rancati]

My Query:

UPDATE HISTORY_TABLE SET ROLLOFF_DATE = DATE()
WHERE SOURCE_SYSTEM = 'TAS' AND
HISTORY_TABLE.REC_ID NOT IN (SELECT REC_ID FROM ODCOH_TAS);

When I run just the subquery portion, it returns records instantly.

When I try to run the query above it takes forever (I have killed it
after 30 minutes several times in a row).

For what it's worth, the subquery does a select on a ODBC Oracle table,
and HISTORY_TABLE is local in the MDB. Both HISTORY_TABLE and the
subquery have only a dozen records or so.

Any ideas why this thing takes so long?

Hi Smartin,

with subquery Jet invoke to oracle a "SELECT REC_ID .." for any
HISTORY_TABLE rows.
So if HISTORY_TABLE has 100.000 rows, Jet invoke 100.000 SELECT to oracle.

With left join query, Jet invoke only one "SELECT REC_ID .." then resolve
the left Join.
The Left Join query is better than subquery

If you can open a profiler track on oracle server, you can see it.

Bye

 

[Smartin]

Hi Smartin,

with subquery Jet invoke to oracle a "SELECT REC_ID .." for any
HISTORY_TABLE rows.
So if HISTORY_TABLE has 100.000 rows, Jet invoke 100.000 SELECT to oracle.

With left join query, Jet invoke only one "SELECT REC_ID .." then resolve
the left Join.
The Left Join query is better than subquery

If you can open a profiler track on oracle server, you can see it.

Bye

Thanks again for the feedback!