To slow with cursor - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

To slow with cursor

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-06-05 : 04:56:13

I have this procedure which takes 6 rows out of a table and appends them into a temp table to form one single row which is returned to the user (this process can happen many times hence my use of a cursor). The problem is that it can take around 30 secs to complete which is to long in my book. Does anyone know an alternative/faster way of doing it ? Many thanks

The code..........

ALTER PROCEDURE usp_ShareHolders_FDC_Inst_History_sel

@cusipCode varchar(8),
@SortBy tinyint,
@SortOrder varchar(1)

AS

/****************************************************************************************
* This SP is used for extracting shreholder history data on a
* monthly basis and displaying current, 1, 2, 3, 4, 5 quarterly intervals
* Data is stored on a monthly basis but only show the user quarterly intervals
*****************************************************************************************/

DECLARE
@intErrorCode int,
@TransactionCountOnEntry int,
@InstitutionName varchar(150),
@InstCode int,
@SharesOutstanding float,
@IntervalDate datetime,
@CurrentFMCode int,
@CurrentFMName varchar(150),
@intInstCount int,
@GetCurrentRow int

DECLARE @CrsrInst Cursor

-- create temporary table for storing results
CREATE TABLE #HistoryList
(
Row_Id int identity(1,1),
InstName varchar(150),
InstCode int,
IntervalShares1 decimal(5, 2),
IntervalDate1 datetime,
IntervalShares4 decimal(5, 2),
IntervalDate4 datetime,
IntervalShares7 decimal(5, 2),
IntervalDate7 datetime,
IntervalShares10 decimal(5, 2),
IntervalDate10 datetime,
IntervalShares13 decimal(5, 2),
IntervalDate13 datetime,
IntervalShares16 decimal(5, 2),
IntervalDate16 datetime
)

-- MONTHLY DATAFEED DISPLAYED AS QUARTERLY INTERVALS

If @intErrorCode = 0
Begin
Set @CrsrInst = Cursor For

SELECT
M.name as Institution,
M.org_key as Institution_Code,
I.perc_shout as Shares_Outstanding,
I.Period as Interval_Date
FROM
FDC_Institutional_Ownership_Archive I
INNER JOIN
FDC_Organization_Mapping_Record O
ON
I.org_key = O.id_1
INNER JOIN
FDC_Management_Company_Record M
ON
O.id_2 = M.org_key
WHERE
I.cusip = @cusipCode
ORDER BY
Institution ASC, Interval_Date DESC

Open @CrsrInst

-- get first record if there is one

Fetch Next From @CrsrInst
Into @InstitutionName, @InstCode, @SharesOutstanding, @IntervalDate

Set @CurrentFMName = @InstitutionName
Set @CurrentFMCode = @InstCode
Set @intInstCount = 0

While (@@FETCH_STATUS = 0)

Begin
Set @intInstCount = @intInstCount + 1

-- next institution ?
If @CurrentFMCode <> @InstCode
Set @intInstCount = 1
Set @CurrentFMName = @InstitutionName
Set @CurrentFMCode = @InstCode

------------------------------ It only displays quarterly intervals so must skip 2 months of stored data each time

If @intInstCount = 2 OR @intInstCount = 3 OR @intInstCount = 5
OR @intInstCount = 6 OR @intInstCount = 8 OR @intInstCount = 9
OR @intInstCount = 11 OR @intInstCount = 12 OR @intInstCount = 14
OR @intInstCount = 15 OR @intInstCount > 16
GoTo NextMonth

------------------------------ Do the inserting parts

If @intInstCount = 1
INSERT into #HistoryList
(
InstName,
InstCode,
IntervalShares1,
IntervalDate1
)
VALUES
(
@InstitutionName,
@InstCode,
@SharesOutstanding,
@IntervalDate
)
Set @GetCurrentRow = @@IDENTITY
If @intInstCount = 4
UPDATE #HistoryList
SET
IntervalShares4 = @SharesOutstanding,
IntervalDate4 = @IntervalDate
WHERE
Row_Id = @GetCurrentRow
If @intInstCount = 7
UPDATE #HistoryList
SET
IntervalShares7 = @SharesOutstanding,
IntervalDate7 = @IntervalDate
WHERE
Row_Id = @GetCurrentRow
If @intInstCount = 10
UPDATE #HistoryList
SET
IntervalShares10 = @SharesOutstanding,
IntervalDate10 = @IntervalDate
WHERE
Row_Id = @GetCurrentRow
If @intInstCount = 13
UPDATE #HistoryList
SET
IntervalShares13 = @SharesOutstanding,
IntervalDate13 = @IntervalDate
WHERE
Row_Id = @GetCurrentRow
If @intInstCount = 16
UPDATE #HistoryList
SET
IntervalShares16 = @SharesOutstanding,
IntervalDate16 = @IntervalDate
WHERE
Row_Id = @GetCurrentRow

-------------------- Loop round for the next record

NextMonth: Fetch Next From @CrsrInst -- get next record
Into @InstitutionName, @InstCode, @SharesOutstanding, @IntervalDate
End
Close @CrsrInst
Deallocate @CrsrInst

The SELECT bit for the user goes here...........

Edited by - KnooKie on 06/05/2001 05:06:09

Edited by - KnooKie on 06/05/2001 05:06:46

Edited by - KnooKie on 06/05/2001 05:08:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources