Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 More Execution Time

Author  Topic 

hemantkumar2380
Starting Member

5 Posts
Posted - 2009-06-24 : 08:34:39
Hello ,
This is my Cousor To Insert Update the Table .Its taking more time.If there are any other technique for manupulating this cursor so that its take less timm.

DECLARE @INSTRUMENTFO VARCHAR(250)
DECLARE @SYMBOLFO VARCHAR(250)
DECLARE @EXPIRY_DTFO VARCHAR(250)
DECLARE @STRIKE_PRFO VARCHAR(250)
DECLARE @OPTION_TYPFO VARCHAR(250)
DECLARE @OPENFO VARCHAR(250)
DECLARE @HIGHFO VARCHAR(250)
DECLARE @LOWFO VARCHAR(250)
DECLARE @CLOSEFO VARCHAR(250)
DECLARE @SETTLE_PRFO VARCHAR(250)
DECLARE @CONTRACTSFO VARCHAR(250)
DECLARE @VAL_INLAKHFO VARCHAR(250)
DECLARE @OPEN_INTFO VARCHAR(250)
DECLARE @CHG_IN_OIFO VARCHAR(250)
DECLARE @TIMESTAMPFO VARCHAR(250)
DECLARE @SeriesIdFO int
DECLARE @PID INT
DECLARE @PCID INT
DECLARE @PSNAME VARCHAR(250)
DECLARE @POTYPE VARCHAR(250)
DECLARE @PESTYLE VARCHAR(250)
DECLARE @PTYPEID INT
DECLARE @PSTYPEID INT
DECLARE @PDFRMID INT
DECLARE @TPIDT INT
DECLARE DS_FO_IMP CURSOR FOR

SELECT INSTRUMENT, SYMBOL,EXPIRY_DT,STRIKE_PR,OPTION_TYP,[OPEN],HIGH,
LOW,[CLOSE],SETTLE_PR,CONTRACTS,VAL_INLAKH,OPEN_INT,CHG_IN_OI,
[TIMESTAMP] FROM TempTableFO

OPEN DS_FO_IMP
FETCH NEXT FROM DS_FO_IMP INTO @INSTRUMENTFO,@SYMBOLFO,@EXPIRY_DTFO,@STRIKE_PRFO,
@OPTION_TYPFO,@OPENFO,@HIGHFO,@LOWFO,@CLOSEFO,@SETTLE_PRFO,@CONTRACTSFO,
@VAL_INLAKHFO,@OPEN_INTFO,@CHG_IN_OIFO,@TIMESTAMPFO

WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @TESI INT
SET @TESI=(SELECT Equity_SeriesID FROM Master_Equity WHERE
Equity_FOIdentifier=@INSTRUMENTFO AND Equity_TickerSymbol=@SYMBOLFO AND Equity_EffectUntil=@EXPIRY_DTFO AND
Equity_StrikePrice=@STRIKE_PRFO AND Equity_Series=@OPTION_TYPFO)
IF @TESI IS NULL
BEGIN

IF @INSTRUMENTFO='FUTIDX'
BEGIN
SET @PDFRMID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='ID')
SET @PTYPEID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='DI')
SET @PSTYPEID=(SELECT ProductSubType_ID FROM Master_ProductSubTypes
WHERE ProductSubType_ApplicableType=@PTYPEID AND ProductSubType_Name='Futures')
END

IF @INSTRUMENTFO='OPTIDX'
BEGIN
SET @PDFRMID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='ID')
SET @PTYPEID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='DI')
SET @PSTYPEID=(SELECT ProductSubType_ID FROM Master_ProductSubTypes
WHERE ProductSubType_ApplicableType=@PTYPEID AND ProductSubType_Name='Options')
END


IF @INSTRUMENTFO='FUTSTK'
BEGIN
SET @PDFRMID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='EQ')
SET @PTYPEID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='DE')
SET @PSTYPEID=(SELECT ProductSubType_ID FROM Master_ProductSubTypes
WHERE ProductSubType_ApplicableType=@PTYPEID AND ProductSubType_Name='Futures')
END

IF @INSTRUMENTFO='OPTSTK'
BEGIN
SET @PDFRMID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='EQ')
SET @PTYPEID=(SELECT ProductType_ID FROM Master_ProductTypes WHERE ProductType_Code='DE')
SET @PSTYPEID=(SELECT ProductSubType_ID FROM Master_ProductSubTypes
WHERE ProductSubType_ApplicableType=@PTYPEID AND ProductSubType_Name='Options')
END

DECLARE @TPID INT
SET @TPID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
IF @TPID IS NULL
BEGIN

SET @PSNAME=(SELECT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PDFRMID,@PCID)
END

IF @OPTION_TYPFO='CA'
BEGIN
SET @POTYPE='CALL'
SET @PESTYLE='AMERICAN'


SET @TPIDT=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
IF @TPIDT IS NULL
BEGIN
SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
SET @PSNAME=(SELECT DISTINCT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT DISTINCT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_ProductSubTypeID,
Products_DerivedFromID,Products_OptionType,Products_ExerciseStyle,
Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PTYPEID,@PSTYPEID,@PID,@POTYPE,@PESTYLE,@PCID)

SET @PID=@@IDENTITY

END

ELSE
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
END

END

IF @OPTION_TYPFO='CE'
BEGIN
SET @POTYPE='CALL'
SET @PESTYLE='EUROPEAN'

SET @TPIDT=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
IF @TPIDT IS NULL
BEGIN
SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
SET @PSNAME=(SELECT DISTINCT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT DISTINCT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_ProductSubTypeID,
Products_DerivedFromID,Products_OptionType,Products_ExerciseStyle,
Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PTYPEID,@PSTYPEID,@PID,@POTYPE,@PESTYLE,@PCID)

SET @PID=@@IDENTITY

END

ELSE
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
END

END

IF @OPTION_TYPFO='PA'
BEGIN
SET @POTYPE='PUT'
SET @PESTYLE='AMERICAN'

SET @TPIDT=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
IF @TPIDT IS NULL
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
SET @PSNAME=(SELECT DISTINCT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT DISTINCT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_ProductSubTypeID,
Products_DerivedFromID,Products_OptionType,Products_ExerciseStyle,
Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PTYPEID,@PSTYPEID,@PID,@POTYPE,@PESTYLE,@PCID)

SET @PID=@@IDENTITY

END

ELSE
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
END

END

IF @OPTION_TYPFO='PE'
BEGIN
SET @POTYPE='PUT'
SET @PESTYLE='EUROPEAN'


SET @TPIDT=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
IF @TPIDT IS NULL
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
SET @PSNAME=(SELECT DISTINCT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT DISTINCT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_ProductSubTypeID,
Products_DerivedFromID,Products_OptionType,Products_ExerciseStyle,
Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PTYPEID,@PSTYPEID,@PID,@POTYPE,@PESTYLE,@PCID)

SET @PID=@@IDENTITY

END

ELSE
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
END

END

IF @OPTION_TYPFO='XX'
BEGIN
SET @POTYPE=''
SET @PESTYLE=''



SET @TPIDT=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
IF @TPIDT IS NULL
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PDFRMID)
SET @PSNAME=(SELECT Products_ShortName FROM Master_Products WHERE Products_Name=@SYMBOLFO)
SET @PCID=(SELECT Products_CompanyID FROM Master_Products WHERE Products_Name=@SYMBOLFO)
IF @PSNAME IS NULL
BEGIN
SET @PSNAME=@SYMBOLFO
END

INSERT INTO Master_Products(Products_Name,Products_ShortName,
Products_ProductTypeID,Products_ProductSubTypeID,
Products_DerivedFromID,Products_OptionType,Products_ExerciseStyle,
Products_CompanyID)
VALUES(@SYMBOLFO,@PSNAME,@PTYPEID,@PSTYPEID,@PID,@POTYPE,@PESTYLE,@PCID)

SET @PID=@@IDENTITY

END

ELSE
BEGIN

SET @PID=(SELECT Products_ID FROM Master_Products WHERE Products_Name=@SYMBOLFO AND
Products_ProductTypeID=@PTYPEID AND Products_ProductSubTypeID=@PSTYPEID AND
Products_OptionType=@POTYPE AND Products_ExerciseStyle=@PESTYLE)
END

END



SET @TIMESTAMPFO=(convert(varchar,cast( REPLACE(@TIMESTAMPFO,',','') as datetime),120))

INSERT INTO Master_Equity(Equity_ProductID,Equity_ExchSegmentID,Equity_EffectUntil,
Equity_TickerSymbol,Equity_FOIdentifier,Equity_Series,Equity_StrikePrice,
Equity_TradingLot,Equity_TradingUOM,Equity_DeliveryLot,Equity_DeliveryUOM,
Equity_QuoteCurrencyID,Equity_PriceUnits,Equity_PriceUOM,Equity_PriceTick)
VALUES(@PID,1,@TIMESTAMPFO,@SYMBOLFO,@INSTRUMENTFO,@OPTION_TYPFO,@STRIKE_PRFO,
111,'Number',111,'Number',1,1,'Number',0.05)

SET @VAL_INLAKHFO=CAST(@VAL_INLAKHFO AS DECIMAL) * 100000

INSERT INTO Trans_DailyStatistics(DailyStat_ExchangeSegmentID,DailyStat_ProductSeriesID,
DailyStat_DateTime,DailyStat_Type,DailyStat_Open,DailyStat_High,DailyStat_Low,
DailyStat_Close,DailyStat_SettlementPrice,DailyStat_TradedQuantity,
DailyStat_TradedValue,DailyStat_OpenInterest,DailyStat_ChangeInOpenInterest,
DailyStat_LastTradedDate,DailyStat_UpdateDateTime)
VALUES(1,1,@TIMESTAMPFO,@OPTION_TYPFO,@OPENFO,@HIGHFO,@LOWFO,@CLOSEFO,@SETTLE_PRFO,
@CONTRACTSFO,@VAL_INLAKHFO,@OPEN_INTFO,@CHG_IN_OIFO,@TIMESTAMPFO,GETDATE())
END

ELSE
BEGIN
SET @SeriesId=(SELECT Equity_SeriesID FROM Master_Equity WHERE
Equity_FOIdentifier=@INSTRUMENTFO AND Equity_TickerSymbol=@SYMBOLFO AND Equity_EffectUntil=@EXPIRY_DTFO AND
Equity_StrikePrice=@STRIKE_PRFO AND Equity_Series=@OPTION_TYPFO)

SET @VAL_INLAKHFO=CAST(@VAL_INLAKHFO AS DECIMAL) * 100000
SET @TIMESTAMPFO=(convert(varchar,cast( REPLACE(@TIMESTAMPFO,',','') as datetime),120))
UPDATE Trans_DailyStatistics SET DailyStat_Open=@OPENFO,
DailyStat_High=@HIGHFO,DailyStat_Low=@LOWFO,
DailyStat_Close=@CLOSEFO,DailyStat_SettlementPrice=@SETTLE_PRFO,
DailyStat_TradedQuantity=@CONTRACTSFO,DailyStat_TradedValue=@VAL_INLAKHFO,
DailyStat_OpenInterest=@OPEN_INTFO,DailyStat_ChangeInOpenInterest=@CHG_IN_OIFO,
DailyStat_DateTime=@TIMESTAMPFO
WHERE DailyStat_ProductSeriesID=@SeriesId
END

FETCH NEXT FROM DS_FO_IMP INTO @INSTRUMENTFO,@SYMBOLFO,@EXPIRY_DTFO,@STRIKE_PRFO,
@OPTION_TYPFO,@OPENFO,@HIGHFO,@LOWFO,@CLOSEFO,@SETTLE_PRFO,@CONTRACTSFO,
@VAL_INLAKHFO,@OPEN_INTFO,@CHG_IN_OIFO,@TIMESTAMPFO

END

CLOSE DS_FO_IMP
DEALLOCATE DS_FO_IMP

hemant kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-24 : 09:16:17
You should rewrite your logic to SET-based, as proven to you here!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128082

You stated time went down from 60 minutes to 1 minute.
Learn from what given to you before. It is exactly the same.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site