I know it's not friday but... bad cursor time

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)

I know it's not friday but... bad cursor time

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-10 : 13:51:18

Looking at a proc that uses cursors and saying bad. unfortunately the table design is not quite full, but I'll try to be thourough with what I post.

CREATE TABLE [dbo].[Resource_Interpretation_Event_DTS] (
[Resource_Interpretation_Seq] [int] NOT NULL ,
[Resource_Entity_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_Source] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Interpretation_Depth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_Method] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_Remarks] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_Date_Time] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Interpretation_By] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Revised_Date_Time] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Revised_By] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Well_UWI] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Oil_Sands_Hole_UWI] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Resource_Study_Id] [int] NULL ,
[Coal_Hole_Id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Excavation_Approval_Number] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field_Station_Id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bwddrl_Dbkey] [numeric](11, 0) NULL ,
[Bwdgfrm_Dbkey] [numeric](11, 0) NULL ,
[Bwdgtmk_Dbkey] [numeric](11, 0) NULL ,
[New_Interpretation_Date_Time] [datetime] NULL ,
[New_Revised_date_Time] [datetime] NULL
)
GO

sorry if it looks a little funky, this is what I was provided. I think it's just what sql 2000 auto scripts look like, for the most part the data columns in there really aren't needed... I just posted then entire code so noone will ask me to post it up anyway ;). Anyway, theres a second temp table thats create on the fly that has 2 columns
create #tempaction (
action varchar(10)
well_ID int
)
The action column is filled using values from an IDMS DB with values like 'Update', 'Insert' , 'Delete','No change' (IDMS's suck... but we use a cobol programmer to pull data from it so I don't have to put up with it). Well_ID is used to join Resource_Interpretation_Event_DTS on Resource_Interpretation_Seq

Currently theres a cursor that goes through each record in #tempaction and reads (some psuedocode for ya)
if @action = 'insert' then insert into Resource_Interpretation_Event_DTS values (select from different table where well_ID = @well_ID)
if @action = 'Delete' then delete from Resource_Interpretation_Event_DTS where Resource_Interpretation_Seq= @well_ID
if @action = 'update' = ..... goes through a large update.

Now I dislike cursors as much as anyone else, but I'm struggling to find an alternative to this procedure. anyhelp would be nice, but I'm really not sure if there is a possible solution.
Actually, to be honest I'm looking for a good reason to tell them that doing it this way is silly. So if there isn't a good answer to this one, good enough.

-----------------------
The best answer = just do as rob or page47 say.

Edited by - M.e. on 06/10/2002 13:52:01

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-10 : 13:55:56

First, when generating DDL check the 'SQL 7.0 compatible scripts' checkbox so you don't get the COLLATE column modifiers.

Second, I don't see why you couldn't run three DML statements for the three actions ( insert/update/delete ). It looks like a simple join of #tempaction to Resource_Interpretation_Event_DTS for the three actions.

setBasedIsTheTruepath
<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-10 : 14:05:17

Ahh, thanks... I'll generate my own with that checked in the future.

so if the #tempaction table has values like
delete 4
insert 8
update 9
delete 11

how would I run a single delete stament to delete all records with actions of delete:

delete from Resource_Interpretation_Event_DTS where Resource_Interpretation_Seq in (select well_ID from #tempaction where action = 'delete'

Hmm, answer my own question... but how about an insert or update.
would
insert into Resource_Interpretation_Event_DTS from Resource_Interpretation_Event_IDMS where Resource_Interpretation_Seq in (select well_ID from #tempaction = 'insert'
work the same way? hmm.. will have to test. The update one would be a bit complicated too... but if that insert works it would pretty much be the same

-----------------------
The best answer = just do as rob or page47 say.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-10 : 14:08:03

You've got the idea. As a general performance tip you should avoid the WHERE IN ( {subquery} ) construct in favor of the WHERE NOT EXISTS ( {correlated subquery} ). The correlated version is almost always faster.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources