Link to Oracle Server SLOOOOW

Hi All,

I am having some problems getting a link to an Oracle server working properly. Here is my environment:

MS SQL Server 7.0 on Windows 2000
Oracle 8.1.7 on HPUX
MDAC 2.5

The link is working. I can query data and update date but it is incredibly slow.

Anyone run into this before?

Thanks...That's pretty vague...

What are you doing?

SELECT * FROM myMillionRowTable a FULL JOIN myMillionRowTable b
ON a.NonkeyedColumn = b.NonkeyedColumn

???|||I have a stored procedure that uses a cursor that queries data on the SQL Server and on average does about 300 inserts into the Oracle server. Takes 18 minutes. I modified the procedure to insert into a local table on SQL Server with the exact same format and it took less than 5 seconds. I would expect it to be somewhat slower going across a link, but not that much slower.|||Post the Cursor...

I bet a set based solution will fly...

Are you joining tables across servers?

Remeber the network gets involved...

Let's see what you're doing...

The "Miracle" of linked servers has limits, regadless what MS says...|||The link is not in any involved with the cursor. I only use the cursor to gather data to insert into a table across the link. The cursor query executes quickly (few seconds).

Here is the procedure I am using:

Thanks...

CREATE PROC lp_Send_ASN_Data_To_Oracle
as
DECLARE
@.ASN_NoticeID INT,
@.ASN_SHPMNT_ID VARCHAR(15),
@.ASN_DTE_SHPD VARCHAR(6),
@.ASN_TME_SHPD VARCHAR(4),
@.ASN_GRSS_WGHT DECIMAL(9,1),
@.ASN_BOL_NBR VARCHAR(15),
@.ASN_PO_NBR VARCHAR(8),
@.ASN_SKU_CODE VARCHAR(16),
@.ASN_PCKG_LP_CDE VARCHAR(9),
@.ASN_SBPCK_LP_CDE VARCHAR(9),
@.ASN_DYELOT VARCHAR(10),
@.ASN_QTY DECIMAL(9,2),
@.ASN_OVRL_WDTH DECIMAL(18,2),
@.NEXT_SQNC_NBR_LDED INT

DECLARE ASN_CURSOR SCROLL CURSOR FOR
select
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
-- GRSS_WGHT_UOM = 'LB',
BOL_NBR = SBOL.BillOfLadingID,
-- MTHD_OF_PYMNT = 'PP',
-- VNDR_UCC_NBR = '662082',
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
-- QTY_UOM = 'YD',
OVRL_WDTH = INSM.InspectedWidth
-- OVRL_WDTH_UOM = 'IN'
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF

OPEN ASN_CURSOR

SET NOCOUNT ON

FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH

WHILE @.@.FETCH_STATUS = 0
BEGIN

BEGIN DISTRIBUTED TRANSACTION
-- BEGIN TRANSACTION

SELECT
@.NEXT_SQNC_NBR_LDED = MAX(SQNC_NBR_LDED) + 1
FROM
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN

IF (@.NEXT_SQNC_NBR_LDED IS NULL)
BEGIN
SELECT @.NEXT_SQNC_NBR_LDED = 1
END

INSERT INTO
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SQNC_NBR_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
VALUES
(
GETDATE(),
@.NEXT_SQNC_NBR_LDED,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
'LB',
@.ASN_BOL_NBR,
'PP',
'662082',
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
'YD',
@.ASN_OVRL_WDTH,
'IN'
)

update
Shipping_EDINotice
set
DateSent = GETDATE(),
EDIStatus = 'DN'
where
NoticeID = @.ASN_NoticeID

COMMIT TRANSACTION

FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH

END

CLOSE ASN_CURSOR

DEALLOCATE ASN_CURSOR

SET NOCOUNT OFF|||WOW...

A world of pain...brother (sister?)

How many rows are we talking about...

BEGIN DISTRIBUTED TRANSACTION...whoa

you got a lot of things going on...I don't think it's the linked server...

And what's to say an insert hasn't occured since you got the last id?|||A lot of things going on?

One cursor, traversing the cursor with a while loop with one select to get the next sequence number, an insert and an update.

The cursor on average will return 300 rows.

There will be an insert for each row from the cursor.

That's not really very much I don't think.|||But you're comitting on every row...what happens if you fail for a row?

You're not checking @.@.ERROR or @.@.ROWCOUNT...and even if you did, how would you know where you left off?

And I can't remeber (not that I use them much), but doesn't Oracle (which version btw) use some form of identity?

If just not sure about the remote transactions...my guess that's the problem (Sorry)...is the Next Id column nullabe? Is it unique..

Can you test it as a set based operation? Something like below?

(Columns not in the correct order):

INSERT INTO
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
select GetDate(),
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
BOL_NBR = SBOL.BillOfLadingID,
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
OVRL_WDTH = INSM.InspectedWidth
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)|||One addition I would make is it looks like there is a sequenced or identity column in the destination table. You will probably have to create a temp table with an identity column, then insert the results of the monster query in the temp table. The to get to the Oracle table, you will just need to add the max(id) from the oracle table to the identity value.

select @.next_id = max(SQNC_NBR_LDED)
from ORACDV..SLI.FRANK_EDI_ASN

insert into ORACDV..SLI.FRANK_EDI_ASN
select id + @.next_id, (other fields)
from #tempresult.

A bit abbreviated, but it would be worlds better than a (shudder) cursor.