Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

performance in sql server Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 4:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:44 PM
Points: 18, Visits: 34
Hi every one,
I have 3 table as below:

Table Attachment
IDAttachment, Name
1 | A
2 | B

Table ActionPerformedAttachment
IDActionPerformedAttachment IDActionPerformed IDAttachment
1 | A | 1
2 | A | 1
3 | A | 1
4 | B | 2
5 | B | 2

Table ActionPerformed
IDActionPerformed IDProject
A | P1
B | P2

Now I have IDProject = P1, and I want to delete data in table Attachment and ActionPerformedAttachment
So I can do it by 2 case:
Case 1: I use temp table for delete
Case 2: I use cursor for delete.
Please help me, what is the best case.
Thank you very much.
Post #1454476
Posted Monday, May 20, 2013 4:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 42,476, Visits: 35,544
Probably neither. Cursors should be avoided wherever possible, and I don't see a reason for a temp table here.

You just want to delete the data relating to Project P1? If so, please can you post actual table definitions (with foreign keys) so we can see how the tables are related



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454477
Posted Monday, May 20, 2013 7:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:44 PM
Points: 18, Visits: 34
Thanks for your reply, and I think user temp table is the best case.
Sample:
DECLARE @TmpIDAttachment TABLE(
IDActionPerformedAttachment INT,
IDAttachment INT)
INSERT INTO @TmpIDAttachment( IDActionPerformedAttachment ,IDAttachment)
SELECT ActionPerformedAttachment.IDActionPerformedAttachment, dbo.ActionPerformedAttachment.IDAttachment
FROM dbo.ActionPerformed
INNER JOIN dbo.ActionPerformedAttachment ON dbo.ActionPerformed.IDActionPerformed = dbo.ActionPerformedAttachment.IDActionPerformed
WHERE dbo.ActionPerformed.IDProject = P1
Post #1454786
Posted Tuesday, May 21, 2013 2:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 42,476, Visits: 35,544
What are you using a table variable for? There's no need for table variable, temp table or cursor that I can see here.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454876
Posted Tuesday, May 21, 2013 3:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:44 PM
Points: 18, Visits: 34
Thank you very much
Post #1454897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse