Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41

    Unanswered: stored procedure works but very slow (was "Optimization")

    I have a big table A_newHistory (more than 2 million rows) with primary key fund_id + date_price . This table has to be updated every 2 hours from XML.
    Every row in XML must be inserted or updated (if current id and date already exist in the table) in the A_newHistory.
    The following procedure works but very slow...
    How can I optimize that?

    ================================================== =======
    CREATE PROCEDURE spSaveFundsAdjustedClose
    @XML ntext
    AS
    DECLARE @fund_id int
    DECLARE @date_price datetime
    DECLARE @adj_closed float
    DECLARE @XMLDoc int

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION

    EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML

    DECLARE MutualFunds_Cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT *
    FROM OPENXML (@XMLDoc , '/xml/a', 1)
    WITH ([id] INT,[date] datetime, price float)
    OPEN MutualFunds_Cursor
    FETCH NEXT FROM MutualFunds_Cursor
    INTO @fund_id, @date_price, @adj_closed

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF EXISTS (SELECT * FROM A_newHistory
    WHERE id_fund = @fund_id AND date_price = @date_price)
    BEGIN
    UPDATE A_newHistory
    SET adj_close = @adj_closed
    WHERE id_fund = @fund_id AND date_price = @date_price
    END
    ELSE
    BEGIN
    INSERT INTO A_newHistory
    VALUES(@fund_id, @date_price, @adj_closed)
    END

    IF @@Error <> 0
    BEGIN
    ROLLBACK TRANSACTION
    SELECT -1
    RETURN
    END

    FETCH NEXT FROM MutualFunds_Cursor
    INTO @fund_id, @date_price, @adj_closed
    END

    EXEC sp_xml_removedocument @XMLDoc
    CLOSE MutualFunds_Cursor
    DEALLOCATE MutualFunds_Cursor

    COMMIT TRANSACTION
    SELECT 0
    GO
    ================================================== =======

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try loading your all of your data into a temporary table and then processing it in two steps, rather than processing every record independently:

    Code:
    CREATE PROCEDURE spSaveFundsAdjustedClose
    @XML ntext 
    AS
    DECLARE @fund_id int
    DECLARE @date_price datetime
    DECLARE @adj_closed float
    DECLARE @XMLDoc int
    DECLARE @MutualFunds table (FundID INT, date_price datetime, adj_closed float)
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
    
    EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML
    
    DECLARE MutualFunds_Cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT	*
    FROM	OPENXML (@XMLDoc , '/xml/a', 1) WITH ([id] INT,[date] datetime, price float)
    
    OPEN MutualFunds_Cursor
    
    FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed 
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		INSERT INTO @MutualFunds (FundID, date_price, adj_closed) VALUES (@fund_id, @date_price, @adj_closed)
    		FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed
    	END 
    
    UPDATE	A_newHistory
    SET	adj_close = MutualFunds.adj_closed
    FROM	A_newHistory
    	INNER JOIN @MutualFunds MutualFunds
    		ON A_newHistory.id_fund = MutualFunds.FundID
    		and A_newHistory.date_price = MutualFunds.date_price
    
    INSERT INTO A_newHistory
    	(id_fund,
    	date_price,
    	adj_close)
    SELECT	MutualFunds.FundID,
    	MutualFunds.date_price,
    	MutualFunds.adj_close
    FROM	@MutualFunds MutualFunds
    	LEFT OUTER JOIN A_newHistory
    		ON MutualFunds.FundID = A_newHistory.id_fund
    		and MutualFunds.date_price = A_newHistory.date_price
    WHERE	A_newHistory.id_fund is null
    
    EXEC sp_xml_removedocument @XMLDoc
    CLOSE MutualFunds_Cursor
    DEALLOCATE MutualFunds_Cursor
    
    COMMIT TRANSACTION
    SELECT 0 
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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