Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, May 12, 2011 1:43 PM
Hi,
First i want you to know that in this problem we have lots of users, using a type of event. and they will delete several rows.
I have a question: its is better to delete these rows, or having a state by row, that i can set it to false, and at the end of the they when users trafic is slow i then delete all rows that have this state to false.
If this case dont have performance problem, remember that this table is going to have lots of rows (the indexer is a bigint), and maybe i will have a select performance problem, with a select with some conditions.
Best Regards,
JP.
Thursday, May 12, 2011 4:38 PM ✅Answered | 2 votes
Let me give you some insight into the differences.
When you do a DELETE, the engine has to lock the row. Then it will delete the row in the table, and it will delete the key in each index.
So if you have 3 indexes, it will immediately update the table and these 3 indexes.
When you do an UPDATE, the engine has to lock the row. Then it will update the table data, and it will have to update all indexes that include the updated column.
So let's assume you a "deleted" column that is in one index, and that you have 3 other indexes (4 indexes in total). Then the row needs to be locked, the table and one index needs to be updated and the immediate action is done. So in this example, the soft delete would be faster than a hard delete.
Once you have soft deleted the row, you basically have junk data in your 3 other indexes. Whenever those indexes are used to find qualifying rows, the table data will always have to be retrieved in order to find out whether the "deleted" bit was set. So that makes Selection slower. And you will have to modify all your queries to take this deleted column into consideration.
Adding the bit column to all the indexes is not an option, because then the UPDATE would be even more costly than a DELETE.
If (on average) you perform a delete more often than a SELECT, and you have sufficient low activity time, then you could consider a soft delete mechanism.
So in general I would not advice this. In general I'd suggest you simply DELETE the row. It is quite unlikely that any performance problem would start there.
Gert-Jan
Friday, May 13, 2011 4:48 AM ✅Answered | 2 votes
A lot of you are stating that a hard delete will have little impact on performance. This is not always the case. Performance can be affected and sometime with great impact as updating/inserting/deleting data can affect query optimization statistics. Whatever the decision is, I suggest you read http://msdn.microsoft.com/en-us/library/ms190397.aspx Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Thursday, May 12, 2011 1:58 PM | 2 votes
If this case dont have performance problem, remember that this table is going to have lots of rows (the indexer is a bigint), and maybe i will have a select performance problem, with a select with some conditions.
You will have performance problems with some conditions if your condition does not use the index and uses complex text searches. So what answer do you expect?
Regarding the first point, deleting records from a table (indexed) should not pose problems unless you have a large number concurrent users (how many are they?) and they delete records while others try to access them.
The simpler the solution the stronger it is
If this post answers you, please mark it as answer..
If this post is useful, please vote it as useful..
Thursday, May 12, 2011 2:06 PM | 2 votes
For your first point, regardling giving the row a state. Searching for the row and Updating will take about the same time as search for a row and deleting it. I do not think that is going to help. Do you know if the DELETE is being blocked by anything? That could be causing the performance issue. Do you have a number of indexes on this table that are causing the performance issue? Without more information about the database setup, I can only guess at what the problem could be.
Thursday, May 12, 2011 2:09 PM
Regarding the first point, deleting records from a table (indexed) should not pose problems unless you have a large number concurrent users (how many are they?) and they delete records while others try to access them.
Hi,
Yes i can have lots of users accessing the same table, imagine that this table have values inserted and at same time deleted always a user uses a search control. I will have lots of users, and probably lots of searchs.
At this point i will certain have insert, but my question is if i use delete or update state.
But i see your point, and with delete i have concurrency problems with lots of users, and maybe i ill have a performance problem.
What is faster, delete a row, or update a bit column?
Thank you,
Best Regards,
JP.
Thursday, May 12, 2011 2:22 PM | 3 votes
Try doing a hard DELETE instead of SOFT DELETE as updating the flag will take more or less the same time as deleting that row. Secondly, HARD deleting will be more performance friendly, as suppose thousands of rows are deleted by users in a day....if you soft delete them, the users will have to live with that extra data until they are physically deleted and when your DELETE job will execute, it will use some server resources as well.....so I would go with HARD delete as compared to SOFT delete.-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
Thursday, May 12, 2011 2:35 PM
Try doing a hard DELETE instead of SOFT DELETE as updating the flag will take more or less the same time as deleting that row. Secondly, HARD deleting will be more performance friendly, as suppose thousands of rows are deleted by users in a day....if you soft delete them, the users will have to live with that extra data until they are physically deleted and when your DELETE job will execute, it will use some server resources as well.....so I would go with HARD delete as compared to SOFT delete.
Hi,
Ok, in your opinion hard delete (using DELETE command) is better then soft delete (usind UPDATE command in flag).
But have you got an example and have experimented a delete command used for lots of users in the same table? Like thousand of users deleting values on a table that can grow up fast and will have millons and more values? I am using SQL Server 2008.
Best Regards,
JP.
Thursday, May 12, 2011 3:20 PM
For your first point, regardling giving the row a state. Searching for the row and Updating will take about the same time as search for a row and deleting it. I do not think that is going to help. Do you know if the DELETE is being blocked by anything? That could be causing the performance issue. Do you have a number of indexes on this table that are causing the performance issue? Without more information about the database setup, I can only guess at what the problem could be.
Hi,
Here my problem is if in future i will have a concurrency/performance problem with lots of users deleting values in the same table. And if the solution is using updating a state flag, to have a better performance.
Best Regards,
JP.
Thursday, May 12, 2011 5:48 PM | 1 vote
I would look at a hard delete first. Try to figure out where the bottleneck is first. If the problem is based on currency, then you can determine if a soft delete/batch process is better for your environment. In my experience, slow deletes are mostly caused by lack of indexes, large chuncks of data being deleted at once (causing table locks due to lock escalation), and concurrency problems.
The best place to start looking is the execution plan of the problematic delete
Friday, May 13, 2011 4:21 AM
As others have rightly pointed out that it's a general practice to HARD delete the data unless the business requirement is preventing it. If you want to be more specific into it, I would request you to please post the Table structure you are talking about, the existing indexes and the details of the DELETE which is being talked about.....i.e. How frequent it is, what all columns are involved to identify the rows to be deleted and what is the size of traffic you are expecting for the delete operation.-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
Friday, May 13, 2011 2:16 PM
Hi,
The table i am using is:
CREATE TABLE [T_Package](
[PackageId] [bigint] IDENTITY(1,1) NOT NULL,
[LoginDetailId] [varchar](12) NOT NULL,
[Id] [int] NULL,
[Duration] [int] NULL,
[Name] [nvarchar](255) NULL,
[ShortName] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[StartingPrice] [nvarchar](255) NULL,
[PhotoURL] [nvarchar](255) NULL,
[CurrencyCode] [char](3) NULL,
[IsRecommended] [bit] NULL,
[IsPromotion] [bit] NULL,
[Status] [char](1) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[CreateUserId] [int] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedUserId] [int] NOT NULL,
[Version] [timestamp] NOT NULL,
CONSTRAINT [PK_Package] PRIMARY KEY NONCLUSTERED
(
[PackageId] DESC
)
In my problem i have one event on client side, that will do: one delete, multiple (foreach) insert and one select, at this table.
I will consider this problem with the bad scenario, lots of users using this event.
I will read this article.
Best Regards,
JP.
Friday, May 13, 2011 3:12 PM
Please make a note of the following points and won't face much issues afa performance is concerned -
- Make sure there exists a covering index for your DELETE operation.
- If you are using SQL Server 2008, make use of MERGE statement instead of looping for INSERT to gain performance. And if you are using any other version of SQL, go for SQLAdapter.Update() and set its INSERT command.
- Make sure there exists a covering index for your SELECT operation.
If you take care of these point, it won;t create much performance issues. However, could you please define LOTS OF USERS in some number. Actually, this will be great help in analyzing the situation better.-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia