Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    25

    Unanswered: Query processing time drastically increased in sybase 12.5 version.

    Hi All,
    All our procs are working fine in version 12.0 but when they are installed on version 12.5, they took more than 2 hours to get executed. Whereas on previous version they get executed only in couple of minutes.

    What could be the reason of such differences?
    I have checked the plans on both the servers, they are identical.

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    Table statics may needs to be updated on 12.5
    Please always reply to the post if it was helpful. Others may find it helpful.

  3. #3
    Join Date
    Jan 2010
    Posts
    25
    Thanks for the suggestion, I will try doing this as well.
    But changes I did is that there were many sub-queries involved in the proc which I removed with alternate logic. The procs is now taking only 4 misn to complete.

    Not sure whether sub-queries could be the culprit ?

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    We will only be speculating if say sub queries are the culprit. Even though we should avoid them.

    If you can post your queries/sps then we will be able to help you out.

    Thanks,
    Meet
    Please always reply to the post if it was helpful. Others may find it helpful.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Also have a look at the recommendations for how to run update statistics in ASE 15 in this whitepaper
    Sybase ASE 15 Best Practices: Query Processing & Optimization White Paper-Technical: Database Management - Sybase Inc

  6. #6
    Join Date
    Jan 2010
    Posts
    25
    Hi,
    This procs works fine on 12.0 , but when executed on 12.5 takes more than 2 hours.

    Code:
    CREATE PROCEDURE GORgetKondorFutureDeal
    				(
    					@dealId				INT			= NULL,
    					@startTradeDate		DATETIME	= NULL,
    					@endTradeDate		DATETIME	= NULL,
    					@dealStatus 		VARCHAR(10)	= NULL,
    					@inputModes 		VARCHAR(10)	= NULL,
    					@fund				VARCHAR(30)	= NULL,
    					@includeCorpActs	BIT			= 0,
    					@showtimes			CHAR(1)		= "N"
    				)
    AS
    BEGIN
    	SET FORCEPLAN ON
    	
    	DECLARE @RowCount INT
    	IF ( @showtimes = "Y")
    	BEGIN
    		DECLARE -- Timers
    			@StartTime DATETIME,@StageStartTime DATETIME,@Elapsed INT
    			SELECT @StartTime = GETDATE(),@StageStartTime = GETDATE()
    			SELECT "started at " + CONVERT(VARCHAR,@StageStartTime,8)
    		END
    
    	SELECT
    		Commission 				= deal.ClearingFees + Brokerage,
    		KondorDealId 			= deal.FuturesDeals_Id,
    		CurrencyOfTrade 		= ccy.Currencies_ShortName, 
    		CurrencyOfSecurity 		= ccy.Currencies_ShortName,
    		CaptureDate 			= CONVERT(CHAR(8),deal.CaptureDate,112),
    		NetAmount 				= deal.ContractValue,
    		TransactionIndicator	= deal.DealType,
    		Counterparty			= cpty.Cpty_ShortName,
    		SecurityDescription		= future.Futures_Name + maturity.Maturity,
    		Security 				= future.Futures_ShortName + maturity.Maturity,
    		CashAccount 			= CASE 
    									WHEN
    									(
    										(
    												deal.TypeOfEvent = "M" 
    											AND 
    												deal.InputMode = "G"
    										) 
    										OR
    					     				(
    												deal.TypeOfEvent = "M" 
    											AND 
    												deal.InputMode = "G" 
    											AND
    	 											deal.BlockNumber != 0
    										)
    									)
    					     				THEN (	SELECT subcfp.CashAccount
    												FROM   kplus..FuturesDeals subswap (1),
    						  							   Kustom..futuresdealsFT subcfp (1)
    	                                    		WHERE  subswap.BlockNumber = deal.BlockNumber
    						  						AND    subswap.FuturesDeals_Id != deal.FuturesDeals_Id
    						  						AND    subcfp.DealId = subswap.FuturesDeals_Id) 
    					      				ELSE (	SELECT CashAccount 
    												FROM   Kustom..futuresdealsFT (1)
    												WHERE  DealId = deal.FuturesDeals_Id) 
    								  END,
    		Quantity 				= deal.Quantity,
    		Price 					= CASE 
                                        WHEN ( SELECT f.Functions_ShortName 
                                        FROM Kustom..functions f,
                                             Kustom..futuresdealsFT ft
                                        WHERE ft.DealId = deal.FuturesDeals_Id 
                                        AND ft.Functions_Id = f.Functions_Id) = 'FUTNEGPRIC'
                                            THEN deal.Price * -1
                                        ELSE deal.Price
                                      END,
    		KondorDealMnemonic		= "FUT",
    		KondorInputMode			= deal.InputMode,
    		LastModifiedTime 		= CONVERT(CHAR(8),deal.LastModifDate,108),
    		LastModifiedDate		= CONVERT(CHAR(8),deal.LastModifDate,112),
    		CaptureTime 			= CONVERT(CHAR(8),deal.CaptureDate,108),
    		Comments 				= deal.Comments,
    		TradeDate 				= CONVERT(CHAR(8),deal.TradeDate,112),
    		Custodian 				= CASE 
    									WHEN
    									(
    										(
    												deal.TypeOfEvent = "M" 
    											AND 
    												deal.InputMode = "G"
    										) 
    										OR
    					     				(
    												deal.TypeOfEvent = "M" 
    											AND 
    												deal.InputMode = "G" 
    											AND
    	 											deal.BlockNumber != 0
    										)
    									)
    					     				THEN (	SELECT subcfp.Custodian
    												FROM   kplus..FuturesDeals subswap (1),
    						  							   Kustom..futuresdealsFT subcfp (1)
    	                                    		WHERE  subswap.BlockNumber = deal.BlockNumber
    						  						AND    subswap.FuturesDeals_Id != deal.FuturesDeals_Id
    						  						AND    subcfp.DealId = subswap.FuturesDeals_Id) 
    					      				ELSE (	SELECT Custodian 
    												FROM   Kustom..futuresdealsFT (1)
    												WHERE  DealId = deal.FuturesDeals_Id) 
    								  END,
    		Folder 					= folders.Folders_ShortName,
    		Portfolio 				= portfolios.Portfolios_ShortName,
    		Futures_ShortName 		= future.Futures_ShortName,
    		Maturity				= maturity.Maturity,
    		KondorTypeOfEvent 		= deal.TypeOfEvent,
            'Function'              = ( SELECT f.Functions_ShortName 
                                        FROM Kustom..functions f,
                                             Kustom..futuresdealsFT ft
                                        WHERE ft.DealId = deal.FuturesDeals_Id 
                                        AND ft.Functions_Id = f.Functions_Id)
    	FROM
    		 kplus..Futures 					future 		(INDEX FuturesIdx1)
    		,kplus..FuturesMaturities 			maturity 	(INDEX FuturesMaturitiesIdx2)
    		,kplus..Currencies 					ccy 		(INDEX CurrenciesIdx1)
    		,kplus..FuturesDeals 				deal 		(INDEX FuturesDealsIdx2)
    	    ,kplus..Folders      				folders 	(INDEX FoldersIdx1)
    	    ,kplus..Portfolios   				portfolios 	(INDEX PortfoliosIdx1)
    	    ,kplus..Branches     				branches 	(INDEX BranchesIdx1)
    		,kplus..Cpty 						cpty 		(INDEX CptyIdx1)
    	WHERE 
    		future.Futures_Id	= maturity.Futures_Id	
    	AND 
    		maturity.FuturesMaturities_Id = deal.FuturesMaturities_Id
    	AND 
    		deal.Folders_Id		         = folders.Folders_Id
    	AND
    		folders.Portfolios_Id       = portfolios.Portfolios_Id
    	AND
    	    portfolios.Branches_Id       = branches.Branches_Id
    	AND
    		future.Currencies_Id 		  = ccy.Currencies_Id
    	AND
    		deal.Cpty_Id		 		  = cpty.Cpty_Id
    	AND
    	    branches.Branches_ShortName  = ISNULL(@fund,branches.Branches_ShortName)
    	AND  
    		branches.Branches_ShortName != NULL
    	AND 
    		deal.FuturesDeals_Id       	= ISNULL(@dealId,deal.FuturesDeals_Id)
    	AND 
    		deal.TradeDate >= ISNULL(@startTradeDate,deal.TradeDate )
    	AND 
    		deal.TradeDate <= ISNULL(@endTradeDate,deal.TradeDate )
    	AND 
    		CHARINDEX(deal.DealStatus,ISNULL(@dealStatus,deal.DealStatus)) > 0
    	AND 
    	(
    		CHARINDEX(deal.InputMode,ISNULL(@inputModes,"CI")) > 0 
    		OR
    		(
    				deal.InputMode = "G" 
    			AND 
    				deal.TypeOfEvent IN ("2","3","M")
    		)
    	)
    	AND
    	(
    			@includeCorpActs = 1
    		OR
    			cpty.Cpty_ShortName NOT IN ("REORG","CORPACT")	
    	)
    
    
    	SELECT @RowCount = @@rowcount
    	IF ( @showtimes = "Y")
    	BEGIN
    		SELECT "Total Rows Retrieved = "+ CONVERT(VARCHAR,@RowCount)
    		SELECT "Seconds Taken To Run "+CONVERT(VARCHAR,CONVERT(FLOAT,DATEDIFF(MS,@StageStartTime,GETDATE()))/1000)
    		SELECT "Total Minutes Taken To Run "+CONVERT(VARCHAR,CONVERT(FLOAT,DATEDIFF(SS,@StartTime,GETDATE()))/60)
    	END
    
    	SET FORCEPLAN OFF
    
    	RETURN 0
    	
    END

  7. #7
    Join Date
    Jun 2010
    Posts
    51
    Ok...I have only 2 suggestions:

    1. Why are you using "set forceplan on". Why not let sybase decide the best way to execute.

    2. Don't do everything in one query. Break them into multiple queries.
    I would suggest:
    • Insert straight forward columns into #temp along with NULL for columns that are being derived using multiple/complicated case statements.
    • Create index on the temp table and then update each column separately in different query.


    Let us know how it goes.

    -Meet
    Please always reply to the post if it was helpful. Others may find it helpful.

Posting Permissions

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