avoid 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 2005 Forums

Transact-SQL (2005)

avoid cursor

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-15 : 05:26:32

Hi,
I have created a stored procedure (SP) which uses cursor to retrieve the correct data from several tables. The issue with this SP is that it is slow.
Now I am trying to achieve the same result without the use of cursors.
Below descibes what the cursor does. I have not copied the whole code here because it is too long.

Hope you can understand what I am trying to do here...

1) This is the initial table with data

declare @tblPricesFinal table
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Source varchar(1000),
Ask_Price_Best_Latest decimal(12, 4),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Source varchar(1000),
Bid_Price_Best_AllDay decimal(12, 4),
Ask_Price_Best_AllDay decimal(12, 4)
)

@tblPricesFinal is populated with a record for each Security_ID
This table can have many records.i.e.

Security_ID Security_Name Bid_Price_Best_Latest Bid_Size_Best_Latest Bid_Source Ask_Price_Best_Latest Ask_Size_Best_Latest Ask_Source
51 Evaz 99.0000 0.50 B NULL NULL NULL
150 Finan 100.5000 0.50 B NULL NULL NULL
365 Forum NULL NULL NULL 101.5000 0.50 L
...
...
...

This table is the one the current cursor loops though.
The cursor loops through each record in the table i.e.

select
Security_ID,
Security_Name,
Bid_Price_Best_Latest,
Bid_Size_Best_Latest,
Bid_Source,
Ask_Price_Best_Latest,
Ask_Size_Best_Latest,
Ask_Source
from
@tblPricesFinal
where
(Bid_Price_Best_Latest is null) or (Ask_Price_Best_Latest is null)

The idea is to find figures with the latest dates for each of the null fields from the history table below and then put the figures inside the first table i.e. @tblPricesFinal

There is also a second table which is a History table.
This history table holds all the historical information which is/was in the first table throughout history i.e. at present or before... i.e. @tblPricesFinal

This history table is with the following structure:
Please note because of business rules we have to keep this structure as it is for this table

CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)

The next step is to get the max(Import_Date) for the Security_ID which has a null field
i.e.
see above where I mentioned
The cursor loops through each record in the table i.e. Security_ID
where
(Bid_Price_Best_Latest is null) or (Ask_Price_Best_Latest is null)

Then IF there is an Import_Date for that security we are looping through, find the
Bid_Price_Best_Latest or Ask_Price_Best_Latest which is null.

This is the query I use for this bit:

select
@Bid_Price_History =
case
when dbo.isreallynumeric(ltrim(rtrim(Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(Bid_Price)))
end,
@Bid_Size_History =
case
when charindex('A', ltrim(rtrim(Bid_Size))) > 0 then replace(Bid_Size, 'A', '')
when charindex('E', ltrim(rtrim(Bid_Size))) > 0 then replace(Bid_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(Bid_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(Bid_Size)))
end,
@Bid_Source = Source_Code
from
tblPricesSourcesImportHistory
where
Source_ID in (select Source_ID from tblSources where Security_ID = @Security_ID)
and len(rtrim(ltrim(Bid_Price))) > 1

and Import_Date = @Import_Date_Max

As the final stage:

update
@tblPricesFinal
set
Bid_Price_Best_Latest = @Bid_Price_History,
Bid_Size_Best_Latest = @Bid_Size_History,
Bid_Source = isnull(dbo.fnSourceShortCode(@Bid_Source),'') + ' - ' + convert(varchar(20), @Import_Date_Max, 103) + ' ' + convert(varchar(5), @Import_Date_Max, 108)
where
Security_ID = @Security_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 05:53:35

Can you please explain second part in detail?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-15 : 06:03:13

quote:
Originally posted by visakh16

Can you please explain second part in detail?

Which part in the post do you mean please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 06:26:13

quote:
Originally posted by arkiboys

quote:
Originally posted by visakh16

Can you please explain second part in detail?

Which part in the post do you mean please?

Then IF there is an Import_Date for that security we are looping through, find the
Bid_Price_Best_Latest or Ask_Price_Best_Latest which is null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources