Problem with delete query across two databases
-
I am trying to run delete queries from one database to another (deletes are done in a copy of the database that is used as the source for data warehouse). The queries are in a DTS package, which used to work fine. When the DTs package fell over I tried breaking up the components into simple steps and running them in Query Analyzer, for example:
DELETE FROM database2.dbo.transactions
WHERE oid IN
(SELECT tr.oid
FROM database1.dbo.transactions TR)
Running the select query on database1 takes approx 1 minute, but running the whole delete query takes up to 17 hours, and locks up the system preventing users from accessing other applications that use SQL Server. There is plenty of space on the server and I rebooted it as well. The only index on the table in database2 is the primary key field oid. There are several views and stored procedures dependent on the table in database1.
Can anyone tell me why this relatively simple query might be causing so many problems and taking so long to run?
Mark.
-
Is column oid indexed in database 1 ? If so, it might run better as an EXISTS subquery:
DELETE FROM database2.dbo.transactions del
WHERE EXISTS
(SELECT * FROM database1.dbo.transactions TR WHERE del.oid = TR.oid)
Also, are there any foreign key constraints that reference the table being deleted from ? That can seriously impact performance if SqlServer has to ensure RI is maintained for each row deleted.
-
Hai,
The entire delete is considered as a single transaction. So the log might be busy. Did you check the transaction log activity. The log cannot get itself stabilized inbetween. Please check that
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
How many records are in each table? The main problem I see with queries using IN and EXISTS is the number of reads that are being performed.
You could try the Non-ANSI double FROM,
DELETE FROM database2.dbo.transactions del FROM database2.dbo.transactions del INNER JOIN database1.dbo.transactions TR ON del.oid = TR.oid
--------------------
Colt 45 - the original point and click interface
-
Thank you PW. However the exists query does not work. It does not allow you to use an alias for the table to be deleted, as this gives the error message "Line 1: Incorrect syntax near 'del'."
I tried removing the alias and changing the query to the following:
DELETE FROM database1.dbo.transactions
WHERE EXISTS
(SELECT * FROM database1.dbo.transactions TR WHERE transactions.oid = TR.oid)
This parses OK but when you try and run it gives the following error message:
"The column prefix 'transactions' does not match with a table name or alias name used in the query."
Any ideas?
-
Hi guys. Thanks for your ideas. Phill, I tried the double FROM and couldn't get that to work either - it gives me the syntax error near 'del' as it did for PW's method.
The queries I am actually running are a bit more complicated than the one I posted - I cut it down to the basics just to get the concepts of what I am trying to do. For example, one of the actual queries I am trying to run is as follows:
DELETE FROM statsDBWarehouse.dbo.trackeditem
WHERE oid IN
(SELECT TI.oid
FROM Statsdb.dbo.trackeditem TI
INNER JOIN Statsdb.dbo.Transactions TS ON (TI.oid = TS.TrackedItem OR TI.oid = TS.OtherTrackedItem)
INNER JOIN Statsdb.dbo.InvoiceLine IL ON TS.oid = IL.transaction_oid
INNER JOIN Statsdb.dbo.Invoice IV ON IL.Invoice = IV.oid
WHERE Convert(VARCHAR,GLPeriod) + '/' + Convert(VARCHAR,GLYear) IN
(
SELECT Convert(VARCHAR,GLPeriod) + '/' + Convert(VARCHAR,GLYear)
FROM Statsdb.dbo.DataWarehouseHistory
WHERE FlagToImport = 1
)
)
This may look a bit ugly, but it did used to work fine. Running the Select statement by itself will run in about 1 minute. However adding the delete part causes it to run for hours.
Mark.
-
Ummm ... sorry ... just remove the "del" from the first line.
--------------------
Colt 45 - the original point and click interface -
Don't know why you have
WHERE Convert(VARCHAR,GLPeriod) + '/' + Convert(VARCHAR,GLYear) IN ( SELECT Convert(VARCHAR,GLPeriod) + '/' + Convert(VARCHAR,GLYear)
As you're doing the concatenation in the where and the select, couldn't the GLPeriod and GLYear fields be used seperately in a join?
Don't know which table those two fields come from, but how does this go?
DELETE FROM statsDBWarehouse.dbo.trackeditem FROM statsDBWarehouse.dbo.trackeditem Trk INNER JOIN Statsdb.dbo.trackeditem TI ON Trk.oid = TI.oid INNER JOIN Statsdb.dbo.Transactions TS ON (TI.oid = TS.TrackedItem OR TI.oid = TS.OtherTrackedItem) INNER JOIN Statsdb.dbo.InvoiceLine IL ON TS.oid = IL.transaction_oid INNER JOIN Statsdb.dbo.Invoice IV ON IL.Invoice = IV.oid INNER JOIN Statsdb.dbo.DataWarehouseHistory Hst ON ??.GLPeriod = Hst.GLPeriod AND ??.GLYear = Hst.GLYear AND Hst.FlagToImport = 1
--------------------
Colt 45 - the original point and click interface -
Hello Phil, I'm curious about your double from. You mentioned that it is not ANSI (89/92 - or not at all?)... Is there an ANSI statement that deletes based on joins? If so, is there a disadvantage to using it?
-
Phill, thank you so much for that. The query ran perfectly in 10 seconds!
Thank you everyone for your contributions.
-
The double FROM syntax is a T-SQL extension to the ANSI standard. The ANSI standard only provides for specifying criteria in the WHERE clause of a DELETE statment. That probably means that it won't work in another DBMS.
As I mentioned in an earlier post, on most occasions when there is poor performance when using EXISTS and IN, the trouble can usually be pinpointed to the number of reads SQL Server is having to do for the comparisson. I think using the JOIN allows for a lot better optimisation.
--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply