OPENQUERY Question. How to update using 2 tables

[addi]

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi

[Simon Hayes]

"addi" <ad****@hotmail.com> wrote in message
news:6f**************************@posting.google.c om...

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi

I don't really understand your question - OPENQUERY() executes entirely on
the linked server, so it would not be possible to join to a local MSSQL
table.

It's hard to say what the issue is without knowing more about how many rows
are involved, but you might want to look at the REMOTE join hint, to see if
a remote join is faster. This may help if the MSSQL table is much smaller
than the Oracle one.

See also this post:

http://groups.google.com/groups?hl=e....microsoft.com

Simon

[Erland Sommarskog]

addi (ad****@hotmail.com) writes:

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

What you could do is to first insert the data in the temptable into a
table on the Oracle side. Then data in the target table does not have
to move forth and back across the network.

Then you would run the UPDATE statement in Oracle (with Oracle syntax).
The best method would be to so through a stored procedure, but I don't
know exactly what Oracle offers in this area.

OPERQUERY? It may work, but OPENQUERY is not intended for update statements,
but is a rowset provider. You could try:

SELECT 1 FROM OPENQUERY(ORACLE_HBCPRD04, 'UPDATE SITEADDRESS ....')

But if you try the same operation against SQL Server, this will fail
with the messages "...indicates that .... does not return any records".
And I would guess something similar will happen with Oracle. For SQL
Server I know of a poor workaround, but if there is something similar
for Oracle I don't know. Of course you could throw in a dummy SELECT
into the batch you pass to Oracle. And in any case, you will produce
a result set, which might be what calling program might expect. (You
could hide with INSERT EXEC though.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp