Hi,

I am deleting thousands of records from the database inside a transaction and getting "System out of memory" in the transaction!!!  But if remove the transaction the process is completed successfully!  Is this a bug or a limitation?  Can someone explain to me what could be the reason?

Regards,
Abdulaziz Jasser

Abdulaziz

>I am deleting thousands of records from the database inside a transaction and getting "System out of memory" in the transaction!!! But if remove the transaction the process is completed successfully! Is this a bug or a limitation? Can someone explain to me what could be the reason?

Its a limitation and will depend on the amount of memory fitted and addressable.

What a transaction does is to carry out the work in memory and only flushes it to the disk after its completed or COMMIT is called so if there's a LOT of work you fill the memory up with changes waiting to be flushed down to disk.

<lecture mode on>
There are two reasons for using a transaction a) it allows an either all or nothing approach using ROLLBACK if an error is encountered and b) speed (transactions are faster most of the time but not always). The problem (or another benefit depending on viewpoint and what you're doing) with a transaction  is that whilst in progress it locks everyone else out.

Unless you need one of the specific features I wouldn't use a transaction.
<lecture mode off>

Unless you are deleting linked records (eg invoice header and invoice rows) I wouldn't bother with a transaction, just have a mechanism for catching records that couldn't be deleted (say because someone's altering them) and either report or run a second pass.

Depending on how you're doing the deleting you can also insert a counter and commit the changes every (say) 1000 records. I'm guessing you're using some sort of loop.

Roy Lambert [Team Elevate]

<<Unless you are deleting linked records>>

Actually I am deleting linked data inside different tables using SQL.  Therefore, I have to use transactions to guarantee a full deletion or zero deletion otherwise I will end-up with garbage data.  Besides, I cannot use a counter with SQL.  However, at least I know it’s not a bug and will find a way to manage that.  Big thanks.

Regards,
Abdulaziz Jasser

Abdulaziz


>Actually I am deleting linked data inside different tables using SQL. Therefore, I have to use transactions to guarantee a full deletion or zero deletion otherwise I will end-up with garbage data. Besides, I cannot use a counter with SQL. However, at least I know it’s not a bug and will find a way to manage that. Big thanks.

Without seeing your code and understanding the table structure its difficult to comment. However, I'm surprised you can't use a counter. If you can post the SQL you're using I, or someone else, may be able to give you a suggestion.

Roy Lambert [Team Elevate]

Ok..here is one SQL from my code:


DELETE FROM TB_Purchase WHERE BranchSysNo = 1 AND InvoiceSysNo IN
(
SELECT TB_Invoices.InvoiceSysNo FROM TB_Invoices
WHERE (TB_Invoices.BranchSysNo,TB_Invoices.YearSysNo,TB_Invoices.InvoiceType) = (1,1,0)
)


Regards,
Abdulaziz Jasser

Abdulaziz


Does just that one give you an out of memory error or are there a lot of them?

I wonder if the entire subselect is staying in memory for each record in TB_Purchase for each one of those statements until the transaction is complete.

Roy Lambert [Team Elevate]

<<Does just that one give you an out of memory error or are there a lot of them?>>

This is only a sample one.  I have many of these inside the transaction.




<<I wonder if the entire subselect is staying in memory for each record in TB_Purchase for each one of those statements until the transaction is complete.>>

I think so.  That is y we use transactions.

Regards,
Abdulaziz Jasser

Abdulaziz


><<Does just that one give you an out of memory error or are there a lot of them?>>
>
>This is only a sample one. I have many of these inside the transaction.

OK. Is there a reason why you can't perform a COMMIT after each of the statements eg

DELETE FROM TB_Purchase WHERE BranchSysNo = 1 AND InvoiceSysNo IN
(
SELECT TB_Invoices.InvoiceSysNo FROM TB_Invoices
WHERE (TB_Invoices.BranchSysNo,TB_Invoices.YearSysNo,TB_Invoices.InvoiceType) = (1,1,0)
)

COMMIT <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

DELETE FROM TB_Purchase WHERE BranchSysNo = 2 AND InvoiceSysNo IN
(
SELECT TB_Invoices.InvoiceSysNo FROM TB_Invoices
WHERE (TB_Invoices.BranchSysNo,TB_Invoices.YearSysNo,TB_Invoices.InvoiceType) = (9,8,0)
)

So that the deletions are performed correctly for each branch but you don't have to hold everything in memory at once?

Roy Lambert [Team Elevate]

<<OK. Is there a reason why you can't perform a COMMIT after each of the statements eg>>

I've already done that.  My main concern was if it's a bug but now I have no problem.  I made one table to be the last table to be deleted just to check and make sure that all tables before were deleted successfully (Based on my knowledge of the schema).
 
Regards,
Abdulaziz Jasser