Hello I am trying to optimize a Stored Procedure and am having no luck
It comes from using a Cursor Inserting a record then using that records
ID to insert another record. Here is the code



OPEN _cursor
FETCH NEXT FROM _cursor
INTO @Program, @Year,  @JobID, @EmpID

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM _cursor
    INTO @Program, @Year,  @JobID, @EmpID


    SET @msg = 'Job# : ' + CAST(@JobID as nvarchar(20)) + ' Has no
Installation Rate for the Following Combination. '
    SET @msg = @msg + 'Program : ' + @Program + ', Year : ' + @Year


    SELECT @isNEW = WFTID FROM tblWorkFlowTasks
    WHERE ARID = @AREA AND WFTTID=@TT AND ID = @JobID

    -- Only Insert New Records
    if(isnull(@isNEW, 0) = 0)
    BEGIN
        INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
VALUES (@AREA, @JobID, @STOpen, @msg, @TT)
        SET @WFTID = @@IDENTITY


        -- Assign To User
        INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID) VALUES
(@WFTID,@EmpID)
    END
END

CLOSE _cursor
DEALLOCATE _cursor

I was wondering I could use an insert statement inside of an insert
statement
or specify SP VAlues from a SELECT Statement?

What exactly is the business requirement here? Inserting master and detail
rows in one step?

BTW: that piece of code is actually made up from examples of not bad, but
*terrible* practices.


ML

Any Help would be nice thanks.

Yes I know there are bad practices there. Hence trying to optimize it
takes
Over 3 minutes to run but if run each separately by hand it takes 20
secs.

I am trying to Insert a Master Records based on a Query/Temp Table.
And then Insert a Detail record for the Inserted Records.  All In one
step with out cursors
Evil little devils.

Thanks Again

That's the way to go!

Insert the master row, then in an appropriate way (of which "set @master_id
= @@identity" is the worst) get the master key (be it primary key or any
other kandidate key), then use that key when inserting detail rows.

To improve data integrity you can wrap it all up into a transaction, too.

We could provide more help, but you'll have to provide more data. Post your
DDL, DML and sample data and we can come up with a solution.


ML

Chandra - ML -
THanks for your help so far. But When I execute the Above Code In the
Assignees/Detials
I just get the Same ID for All the Records. So I'm going to give you a
really striped down version okay. here we go.

FROMTABLE
PKID  [int]
Field1 [int]
FIeld2 [int]

MASTERTABLE
PKID [int]
Field1 [int]    -- This should match Field1 from FROMTABLE

DETAILTABLE
PKID [int]
ParentID [int]  -- This should match the PKID Field from MASTERTABLE
Field2 [int]    -- This should match Field2 from FROMTABLE

I wish to insert all records from FROMTABLE INTO MASTERTABLE
ONLY Field Field1

THEN CREATE A DETAILS FOR THE MASTERTABLE in DETAILTABLE
With Field2 FROM FROMTABLE and PKID from MASTERTABLE.


The reason this is not in one table is so I can insert other details at
a later time.
Did I make that a bit easier to understand? Thanks.
I'm really starting to dig this groups.

I would like the output to look like this

FROMTABLE
1, 11, 111
2, 22, 222
3, 33, 333
4, 44, 444

MASTERTABLE
1, 11
2, 22
3, 33
4, 44

DETAILTABLE
1, 1, 111
2, 2, 222
3, 3, 333
4, 4, 444

WOW.
Who would've thought that A trigger could take it from 3 Min. 24 sec.
Thanks.

I was just wondering if all of you were in agreement on if this a a
correct practice and a good way to do this kinda thing from now on?

Are you using the code I put up Ezra?  If so, that's the way I would prefer. 
But as you know with SQL, there are so many options.  Glad to hear others
opinions on it.
Show quoteHide quote

Adam -

Yes I am Using the code you up put. I thank you for your input. I just
wanted to tell all of you thanks for your help. I look at multiple
groups before finding one whose members answered questions is good
detail.  so THANKS PPL. Keep up the good work.

How about using an Insert trigger to populate your second table.  That way
you could remove the inefficient cursor.  If this sounds like a viable option
for you I'll supply some example code if required.
Show quoteHide quote

Adam -
I know how to do cursors, But if I took that route how would I get the
UserID Values without requerying the Database? Thanks I'll keep it in
mind.

I do not claim to be the best sql Programmer. I can do what need to be
done. But I would like to now the best ways to do things so feel free
to pick apart my code. Thanks once agian

No problem Ezra,  I've copied some code in below.  Just paste this in a test
db and run the code in.  Then execute the proc to see it work.  You can still
use @@identity within a trigger.

----------------CODE START

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TRG_INS_TEST]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRG_INS_TEST]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[InsertMasterAndChild]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[InsertMasterAndChild]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableChild]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableChild]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableMaster]
GO

CREATE TABLE [dbo].[TableChild] (
    [id] [int] NOT NULL ,
    [col2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TableMaster] (
    [col1] [int] IDENTITY (1, 1) NOT NULL ,
    [col2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE InsertMasterAndChild AS


insert into TableMaster (col2)
values ('a')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER TRG_INS_TEST ON [dbo].[TableMaster]
FOR INSERT

AS

INSERT INTO TableChild
values(@@IDENTITY,'Anything you want')


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------CODE END
Show quoteHide quote

Hi EzraB
You can avoid using the cursor to increase the performance.

You can re-write whole thing as:


INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
SELECT @AREA, @JobID, @STOpen, 'Job# : ' + CAST(@JobID as nvarchar(20)) + '
Has no Installation Rate for the Following Combination. ' + 'Program : ' +
@Program + ', Year : ' + @Year, @TT
FROM <condition in cursor>
where isnull(@isNEW, 0) = 0

INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID)
SELECT @IDENTITY, @EmpID
FROM <condition in cursor>
where isnull(@isNEW, 0) = 0

Please let me know if u have any questions


Show quoteHide quote

Chandra-

Will that put the inserted ID from the Tasks Table into the Assignees
Table?

I doesn't look like it would but I'll Give it a try.

Thanks for all the help so far people. First time in groups never
expected responses this fast.

Cursors, IDENTITY instead of keys, "tbl-" prefixes, underscore as first
character in a name, confuse rows and records use flags and just about
everything else that can go wrong.

If I used this as an example in one of my books, people would thilnk I
was trying to do get as much bad stuff inoneplace as physically
possible.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.  You need to start over with a relational schema instead of
this mess.

No, INSERT INTO works on one and only one base table at a  time. You
need one for the master and one for the details.

I have no idea what that means.  You can "INSERT INTO.. SELECT.."

I understand what you mean to say, but you act as if you are personally
offended when people do these things.

Why do you reply to these newsgroups? If it is to help and/or teach people,
you (as guru) must understand that implying that people are wrong, incapable
or plain ignorant never helps....
People here are doing their best to learn (otherwise they wouldn't bother
asking questions) and occasionally are new to programming/databases and/or
not fluent in english (which makes it that much harder)

Lee-Z

Actually, we get a lot of what Chris Date calls "Do my homework/my job
for me" postings.  These guys do not bother to read a books, Google or
even learn the terms.  We, the gurus, ask them please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in their schema are. But they
expect that we will read their minds.

A lot of the time, you get a posting liket his which is sooooo bad that
you cannot answer it in a newsgroup.  Erza will require about two
quarters of database classes or reading to clean up the mindset of the
guy that wrote code and schema.

I tell them the truth, give them pointers to books.  They can either
take advice that would cost them $1000-$2000 per day or they can whine
about how I hurt their feelings.  I personally do not think that
New-Age touchy-feeling" stuff gives a kid the right to hurt or kill
people with their bad programming.

But sometimes I give in and do something really evil; I give them a
kludge instead of an answer.

Concurrent select is locked while another transaction executes Ins
Store Procedure hits performance?
Cascading path problem
Cannot insert the value NULL into column 'USERDEF1'
Trigger Help!