SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


performance in sql server


performance in sql server

Author
Message
vantuan02t1
vantuan02t1
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 47
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87307 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


vantuan02t1
vantuan02t1
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 47
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87307 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


vantuan02t1
vantuan02t1
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 47
Thank you very much
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search