Solved

deleting rows takes more time

Posted on 2010-11-23
6
498 Views
Last Modified: 2013-12-07
Hi,

I have 3 tables A,B,C.......each of them has more then 100000 rows, n I would like to delete all the rows from three tables but it is taking too much of time.

Table A has triggers like, if anything deletes from A, it will add it to B and
second trigger like whatever is deleted from A, those records will be deleted from B also.

How can I solve this problem?
0
Comment
Question by:sridhar_ravva
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 34200189
could you disable the triggers?

or,  truncate each table, that doesn't fire triggers
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34200608
if you want to delete ALL records, best way is to use TRUNCATE.

Downside of using TRUNCATE is that you might have to disable constraints this table is being referred
0
 

Author Comment

by:sridhar_ravva
ID: 34201213
sorry, I missed something:
"second trigger like whatever is deleted from A, those records will be deleted from B also."

is wrong
actually table C has many other data also. I just need to delete all the data from table A and B but delete only those records from C which are deleted from A.
Table C has other data too, other than just table A.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34201430
truncate A,  truncate B,

delete C where not exists (select null from A where a.col1 = c.col1 and a.col2 = b.col2)


expand to the appropriate columns and add NULL checks if necessary
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34421478
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question