manipulating data using Cursor

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

manipulating data using Cursor

Author

Topic

hemantkumar2380
Starting Member

5 Posts

Posted - 2009-06-23 : 07:53:27

I am using Cursor to manipulating data in more than three table. In temp table there are more then 4000000 Records available .my execution time is approximately 60 min .
Please suggest me how to reduce my execution time.
If there any technique to manipulate the data in quickly /less time.
My cursor Is As Flow:

DECLARE @timestampD varchar(250)
DECLARE @openD varchar(250)
DECLARE @highD varchar(250)
DECLARE @lowD varchar(250)
DECLARE @lastD varchar(250)
DECLARE @closeD decimal(18,2)
DECLARE @prevcloseD varchar(250)
DECLARE @tottrdvalD varchar(250)
DECLARE @trnoverinKD varchar(250)
DECLARE @totAVGPriceD varchar(250)
DECLARE @SymbolD varchar(250)
DECLARE @SeriesD varchar(250)
DECLARE @DailyStatRatioD varchar(250)
DECLARE @ProductSeriesIDD varchar(250)
DECLARE @tottrdqtyD varchar(250)
DECLARE @SeriesIdD int
DECLARE @SFS varchar(250)
DECLARE Stat_Cursor CURSOR FOR
SELECT SYMBOL,SERIES,[OPEN],HIGH,LOW,[CLOSE],LAST,PREVCLOSE,
TOTTRDQTY,TOTTRDVAL,[TIMESTAMP] from TempTable
OPEN Stat_Cursor
FETCH NEXT FROM Stat_Cursor INTO @SymbolD,@SeriesD,@openD,@highD,@lowD,@closeD,@lastD,@prevcloseD,@tottrdqtyD, @tottrdvalD,@timestampD
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SFS=NULL
SET @SFS=@SeriesD
IF @SFS='BE'
BEGIN
SET @SFS='EQ'
END
SET @SeriesIdD=(SELECT top(1) Equity_SeriesID FROM Master_Equity where Equity_TickerSymbol=@SymbolD and Equity_Series=@SFS)
IF @SeriesIdD IS NULL
BEGIN
INSERT INTO LOG_TABLE(TableName,symbol,series,DATE) VALUES('Trans_DailyStatistics',@SymbolD,@SeriesD,GETDATE())
END

ELSE
BEGIN
INSERT INTO Trans_DailyStatistics(DailyStat_ExchangeSegmentID,DailyStat_ProductSeriesID,DailyStat_DateTime,DailyStat_Type,DailyStat_Open,DailyStat_High,DailyStat_Low,DailyStat_LTP,DailyStat_Close,DailyStat_PreviousClose,DailyStat_TradedQuantity,DailyStat_TradedValue,DailyStat_UpdateDateTime)
VALUES(1,@SeriesIdD,@timestampD,'Final',@openD,@highD,@lowD,@lastD,@closeD,@prevcloseD,@tottrdqtyD,@tottrdvalD,GETDATE())

END
END
FETCH NEXT FROM Stat_Cursor INTO @SymbolD,@SeriesD,@openD,@highD,@lowD,@closeD,@lastD,@prevcloseD,@tottrdqtyD, @tottrdvalD,@timestampD
END
CLOSE Stat_Cursor
DEALLOCATE Stat_Cursor

hemant kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-23 : 08:16:50

no cursor. Try this


INSERT INTO LOG_TABLE
(
	TableName, 
	symbol, 
	series, 
	[DATE]
) 
SELECT 	'Trans_DailyStatistics', 
	t.SYMBOL, 
	t.SERIES, 
	GETDATE()
FROM	TempTable t
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	Master_Equity m	
		WHERE	m.Equity_TickerSymbol	= t.SYMBOL
		AND	m.Equity_Series		= CASE WHEN t.SERIES = 'BE' THEN 'EQ' ELSE t.SERIES END
	)


INSERT INTO Trans_DailyStatistics 
(
	DailyStat_ExchangeSegmentID, 
	DailyStat_ProductSeriesID, 
	DailyStat_DateTime, 
	DailyStat_Type,
	DailyStat_Open,
	DailyStat_High,
	DailyStat_Low,
	DailyStat_LTP,
	DailyStat_Close,
	DailyStat_PreviousClose,
	DailyStat_TradedQuantity,
	DailyStat_TradedValue,
	DailyStat_UpdateDateTime
)
SELECT	1,
	m.[Equity_SeriesID],
	t.[timestamp],
	'Final',
	t.[OPEN],
	t.[HIGH],
	t.[LOW],
	t.[LAST],
	t.[CLOSE],
	t.[PREVCLOSE],
	t.[TOTTRDQTY],
	t.[TOTTRDVAL],
	GETDATE())
FROM	TempTable t
	INNER JOIN 
	(
		SELECT	Equity_TickerSymbol, Equity_Series, Equity_SeriesID = MIN(Equity_SeriesID)
		FROM	Master_Equity 
		GROUP BY Equity_TickerSymbol
	) m	ON	m.Equity_TickerSymbol	= t.SYMBOL
		AND	m.Equity_Series		= CASE WHEN t.SERIES = 'BE' THEN 'EQ' ELSE t.SERIES END

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:29:06

[code]-- First insert
INSERT LOG_TABLE
(
TableName,
Symbol,
Series,
[Date]
)
SELECT 'Trans_DailyStatistics',
tt.Symbol,
tt.Series,
GETDATE()
FROM TempTable AS tt
WHERE NOT EXISTS ( SELECT *
FROM Master_Equity AS me
WHERE me.Equity_TickerSymbol = tt.Symbol
AND me.Equity_Series = CASE tt.Series
WHEN 'BE' THEN 'EQ'
ELSE tt.Series
END
)

-- Second insert
INSERT Trans_DailyStatistics
(
DailyStat_ExchangeSegmentID,
DailyStat_ProductSeriesID,
DailyStat_DateTime,
DailyStat_Type,
DailyStat_Open,
DailyStat_High,
DailyStat_Low,
DailyStat_LTP,
DailyStat_Close,
DailyStat_PreviousClose,
DailyStat_TradedQuantity,
DailyStat_TradedValue,
DailyStat_UpdateDateTime
)
SELECT 1,
me.Equity_SeriesID,
tt.[TIMESTAMP],
'Final',
tt.[OPEN],
tt.HIGH,
tt.LOW,
tt.[LAST],
tt.[CLOSE],
tt.PREVCLOSE,
tt.TOTTRDQTY,
tt.TOTTRDVAL,
GETDATE()
FROM TempTable AS tt
INNER JOIN (
SELECT Equity_TickerSymbol,
Equity_Series,
MAX(Equity_SeriesID) AS Equity_SeriesID
FROM Master_Equity
GROUP BY Equity_TickerSymbol,
Equity_Series
) AS me ON me.Equity_TickerSymbol = tt.Symbol
AND me.Equity_Series = CASE tt.Series
WHEN 'BE' THEN 'EQ'
ELSE tt.Series
END[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:29:49

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-23 : 08:40:26

Strangely how similar is both solution. And the formatting also.

It is not my usual style of writing INSERT INTO by having each of the column name in a different line.

I must be "pesofied" after reading 24K of your posts.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:59:06

Oh, I have used it for years. Since 1993 or so I believe.
This "standard" of mine developed over the years when teaching SQL to beginners.
It is more easy to debug when all columns are on one line of their own.
It also mostly makes the lines shorter.

E 12°55'05.63"
N 56°04'39.26"

hemantkumar2380
Starting Member

5 Posts

Posted - 2009-06-24 : 02:37:37

Thanks man Its realy Working fine .till now my execution time is less then 60 sec.Thanks again.

hemant kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources