This is more of a SQL Server question, but I was motivated to set up foreign key relationships so LLBLGen could detect the relationships. The problem is that it seems to make deletes extremely slow. Does anyone have suggestions? My database has millions of records and 'delete from table' is far slower than 'truncate table'.

benles wrote:

This is more of a SQL Server question, but I was motivated to set up foreign key relationships so LLBLGen could detect the relationships. The problem is that it seems to make deletes extremely slow. Does anyone have suggestions? My database has millions of records and 'delete from table' is far slower than 'truncate table'.

That's because delete from table inserts for every row a rollback statement in the transaction log, so you can rollback the delete later on even when the transaction is committed . Truncate table doesn't do that, it just throws away the data.

So with millions of rows, delete from table is pretty slow, as it has to store a lot of transaction log messages. if you don't need to rollback the action, just use truncate table, though when there might be a possibility you need it, use delete from table.

Also if you are trying to delete a row from a table that is the pk side of a fk-pk relationship (and you have referential integrity checking on), sql has to check that there are no records with that pk in the fk table. This is can be slow if the fk table isn't indexed on the fk, as sql has to do a table scan for eack pk delete.

I had this situation yesterday, adding the proper index made the delete go much quicker.

Otis wrote:

That's because delete from table inserts for every row a rollback statement in the transaction log, so you can rollback the delete later on even when the transaction is committed . Truncate table doesn't do that, it just throws away the data.

So with millions of rows, delete from table is pretty slow, as it has to store a lot of transaction log messages. if you don't need to rollback the action, just use truncate table, though when there might be a possibility you need it, use delete from table.

Is there any way to specify that LLBLGen should turn off transaction logging before a DELETE? If foreign keys are set up to cascade deletes, then truncate will always fail with an error that an FK constraint would be violated, so the DELETE statement is the only option.

You can't switch off transaction logging for DELETE statements.