Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: 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....

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's pretty vague....

    What are you doing?

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

    ?????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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):

    Code:
    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.ActualShipDate)),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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •