Hot answers tagged

32

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.


28

You have a few options. The best option is to run a batch delete so that triggers are not hit. Disable the triggers before deleting, then re-enable them. This saves you a very large amount of time. For example: ALTER TABLE tablename DISABLE TRIGGER ALL; DELETE ...; ALTER TABLE tablename ENABLE TRIGGER ALL; A major key here is you want to minimize the ...


26

Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are going to be better off letting PostgreSQL do the work here. In general, I have found updates on tens of thousands of rows in the manner you are describing to ...


24

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...


21

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...


20

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis. 1 Backup all of the databases on your postgresql server ...


20

This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results 2560 2120 2080 2130 2140 But with the PK I found a similar pattern in my tests as well with typical results below. +--------+--------+---------+-------------------+ | @table | #table | ##table | [...


17

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though. CAUTION (As noted in the comments by @erny): Note that high CPU load due to I/O operations may be expected. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of ...


17

In general it is better to know the specific requirements and not make design decisions based on what works best in most situations. Either could be preferable. Here are some specifics to gather: How fast do deletes need to be? How fast do un-deletes need to be? How often will deleted data be queried and will it be queried with data that has not been ...


17

I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous. So, is cascading delete a correct choice here? It would certainly do what you are looking for it to do: remove related records when ...


17

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances. Bold emphasis mine. Using information that is not contained in another table ...


16

You can delete databases with DBCA which takes care of most of it. Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually. sqlplus / as sysdba startup mount exclusive restrict exit rman target / drop database including backups noprompt; exit After this, you still have to remove the entry that belongs ...


15

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index. This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...


15

There is no feature built in to delete rows automatically on a time-based regime (that I would know of). You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose. Or you could use partitioning with weekly partitions that inherit from a master table, lets call it log. That would make deleting very cheap: ...


14

The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes. Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML. The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are: TRUNCATE can change storage parameters (the NEXT parameter), and those are part ...


14

The easiest method to solve the problem is to query detailed timing from the PostgreSQL: EXPLAIN. For this you need to find at minimum a single query that does complete but takes longer than expected. Let's say that this line would look like delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6'; Instead of really running that command you can ...


14

Here is one case where it would be hard to write it without: DELETE /* FROM */ t1 -- this FROM is optional FROM dbo.t1 -- this FROM is mandatory INNER JOIN dbo.t2 AS t2 ON t1.key = t2.key WHERE t2.key IN (1,2,3); Or: DELETE /* FROM */ t1 -- this FROM is optional FROM dbo.t1 -- this FROM is mandatory WHERE EXISTS ( ...


13

Very simple indeed, but you do need to include the other WHERE clause as well: DELETE FROM batch bp USING sender_log sl WHERE bp.log_id = sl.id AND bp.protocol = 'someprotocol' RETURNING bp.*, sl.*; And to actually return what your question outlines, you need to include both tables in the RETURNING clause.


13

Should be something like this: DELETE A FROM table_example1 AS A INNER JOIN table_example2 AS B ON A.COLUMN1 =B.COLUMN1 AND A.COLUMN2 = B.COLUMN2 WHERE COLUMN_DATETIME > @Period; Alternatively: DELETE FROM A FROM dbo.table_example1 AS A WHERE EXISTS ( SELECT * FROM dbo.table_example2 AS B WHERE B.COLUMN1 = A....


13

First of all, check the SQL errorlog to see if it actually hit a max size for the log. If it did, then the query has no hope of completing, it is probably already in a rollback state. Even if it is, I always prefer to kill the spid manually (use sp_who2 or sp_WhoIsActive to find the spid, then do a kill 59 or whatever). You also can't check the rollback ...


13

The only query in what you're showing above appears to be this one, repeated a few times: IF EXISTS ( select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE) where trd.HeaderId = @HeaderId ) DELETE from dbo.FinanceDetail where HeaderId = @...


12

The row-versioning framework introduced in SQL Server 2005 is used to support a number of features, including the new transaction isolation levels READ_COMMITTED_SNAPSHOT and SNAPSHOT. Even when neither of these isolation levels are enabled, row-versioning is still used for AFTER triggers (to facilitate generation of the inserted and deleted pseudo-tables), ...


12

Just to add to the existing answers. The SQL Server 2008 Internals Book (pp 175-177) implies that detaching the database, deleting the log file and reattaching the mdf file ought to be quite safe as it says. Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. ...


12

Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...


12

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity. There are two options: Delete the rows from INVENTORY_ITEMS first, then the rows from STOCK_ARTICLES. Use ON DELETE CASCADE for the in the key definition. 1: Deleting In Correct Order The most ...


12

SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, SQL Server will return an error. Are you seeing errors? The far more likely problem is the query itself is not targeting the rows you think it is, or those rows ...


11

You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing. I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the ...


11

Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space. Otherwise, you're looking at: ALTER TABLE (mytablename) REBUILD - which takes it offline Doing deletes with TABLOCK hints TRUNCATE TABLE (mytablename) I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have ...


11

For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example. DECLARE @BATCHSIZE INT = 4000 , @ITERATION INT = 0 , @TOTALROWS INT = 0 , @MSG VARCHAR(500) , @STARTTIME DATETIME , @ENDTIME DATETIME , @StartValue int = ...


10

You can force a ROLLBACK though by switching the DB to RESTRICTED using WITH ROLLBACK IMMEDIATE. See ALTER DATABASE. You can't force COMMIT (as per your SO question) Then... If you delete the LDF (SQL Server shut down), your database comes back "suspect". If you detach+delete, attach the MDF by itself, the LDF is re-created. Note: for the millionth time, ...


Only top voted, non community-wiki answers of a minimum length are eligible