Performance: Cursor vs Temp Table

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)

Performance: Cursor vs Temp Table

Author

Topic

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-20 : 15:31:41

I have the following tables:

|tickets
|------------------------
|id (PK)
|title
|etc

|activities
|------------------------
|id (PK)
|details
|etc

|ticket_activity_j
|------------------------
|ticketid (FK)
|activityid (FK)

Tickets contain activities, joined to them via ticket_activity_j. Pretty self explanatory.

I am currently writing a stored procedure to delete a ticket via ticketid as a parameter and all of that ticket's associated activities. To do this, I need to first delete applicable rows from the join table b/c of foreign key restraints. I was wondering what would have better performance:

METHOD A:
1. Create a cursor holding all of the activity ids joined to the ticket
2. Delete the rows in the join table (delete from ticket_activity_j where ticketid = @ticketid)
3. Loop through the cursor and delete the activities one at a time
4. Delete the ticket from the tickets table

METHOD B:
1. Create a temp table containing all of the activity ids joined to the ticket
2. Delete the rows in the join table (delete from ticket_activity_j where ticketid = @ticketid)
3. Delete all the activities in a single query (delete from activities a inner join temptable t on a.activityid = t.activityid)
4. Drop temp table
5. Delete the ticket from the tickets table

OR if there is a completely different and better way to do this, I'm all ears!

Thanks,
Adam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 15:35:27

You could use CASCADED deletes turned on...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 15:40:18

[code]CREATE PROCEDURE dbo.uspTicketDelete
(
@TicketID INT
)
AS

SET NOCOUNT ON

DELETE a
FROM activities AS a
WHERE EXISTS (SELECT * FROM ticket_activity_j AS taj WHERE taj.activityid = a.id AND taj.ticketid = @TicketID)

DELETE taj
FROM ticket_activity_j AS taj
WHERE taj.ticketid = @TicketID

DELETE
FROM Tickets
WHERE ID = @TicketID[/code]

E 12°55'05.25"
N 56°04'39.16"

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-20 : 16:57:26

quote:

Originally posted by Peso

CREATE PROCEDURE dbo.uspTicketDelete
(
	@TicketID INT
)
AS

SET NOCOUNT ON

DELETE	a
FROM	activities AS a
WHERE	EXISTS 
        (SELECT * 
           FROM ticket_activity_j AS taj 
          WHERE taj.activityid = a.id 
            AND taj.ticketid = @TicketID)

DELETE	taj
FROM	ticket_activity_j AS taj
WHERE	taj.ticketid = @TicketID

DELETE
FROM	Tickets
WHERE	ID = @TicketID

E 12°55'05.25"
N 56°04'39.16"

This won't work because the records in activities are referenced as foreign keys in ticket_activity_j.

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-20 : 17:02:52

quote:
Originally posted by Peso

You could use CASCADED deletes turned on...

E 12°55'05.25"
N 56°04'39.16"

I'm reading about CASCADED DELETES and trying to figure them out...

I have created a simple test case that works:

Categories
-------------------
-id PK

Products
-------------------
-id PK
-categoryid FK

Setting up the Products table to have categories.id as a FK restraint set to CASCADE DELETE is like saying "hey, if a row in categories is deleted, delete all rows in me (products) that have a row referencing the deleted row in categories," right? Ok, I have that working in test tables. Now is where I get confused...

Where do I set the CASCADED deletes in my tables? basically, I want to say "hey, if a ticket is deleted, delete all activities associated with it," but they are connected using a join table.

Hellpppp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 17:06:13

You're right. My bad. Maybe I shouldn't look at StarWars II when coding?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 17:11:04

Method B, with a twist.

CREATE PROCEDURE dbo.uspTicketDelete
(
	@TicketID INT
)
AS

SET NOCOUNT ON

DECLARE	@Activites TABLE (ActivityID INT)

-- Only get ActivityID that are used only for @TicketID
INSERT		@Activities
		(
			ActivityID
		)
SELECT		ActivityID
FROM		Ticket_Activity_J
GROUP BY	ActivityID
HAVING		MIN(CASE WHEN TicketID = @TicketID THEN 1 ELSE 0 END) = 1
-- If ActivityID "a" is used for both Ticket "@TicketID" and "2", you can't delete

DELETE
FROM	Ticket_Activity_J
WHERE	TicketID = @TicketID

DELETE		ta
FROM		Activities AS ta
INNER JOIN	@Activities AS a ON a.ActivityID = ta.ActivityID

DELETE
FROM	Tickets
WHERE	ID = @TicketID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 17:25:31

Why are you deleting Activities?
What if that particular ActivityID should be reused for another Ticket?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 02:02:48

Did the suggestion help?

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-31 : 01:05:12

Nah... method "A" is the best... how else will it take a week long to execute and how else will you get the minimum daily requirement of deadlocks? ;-)

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources