August 17, 2010 at 3:58 am
Hi,
My delete statement looks like as mention below..
DELETE FROM <tablename> WHERE Id in (10)
but its shows near by 30 Reads and 5 Writes in the profiler. Need to know why such high Reads?
Your suggestions are appreciated.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 4:03 am
I'm just guessing that it is caused by the IN clause.
Why don't you use DELETE FROM <tablename> WHERE Id =10 instead?
If there is more than one value, use a subquery and join on it instead of using IN.
August 17, 2010 at 4:47 am
LutzM (8/17/2010)
...If there is more than one value, use a subquery and join on it instead of using IN.
That it is no longer gurateeed to be the better way in SQL2008 as I found yesterday looking (and testing) some Gail's blog (http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/).
Actually what I found is: adding CLUSTERED index on a "Looked-up" column to the main (BigTable) table, makes SQL to use exactly the same plan (merge join) in both query types (using IN and JOIN), resulting in equal performance for both...
August 17, 2010 at 4:52 am
Okay, i agree with you both of you but though I tried with equal clause it shows me Reads. logically it should not show any reads in profiler. please correct me if point is not valid.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 5:08 am
Logical read shows the total number of data pages needed to be accessed from data cache to process query. It is quite possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table.
Can you please provide DDL for table you're deleting from together with definition for all its indices.
August 17, 2010 at 5:56 am
Here is the DDL for which m working on...
CREATE TABLE [dbo].[Tier](
[TierRId] [int] IDENTITY(1,1) NOT NULL,
[ScheduleRId] [int] NOT NULL,
[Duration] [float] NULL,
[ModifiedReason] [nvarchar](100) NULL,
[ModifiedBy] [nvarchar](20) NOT NULL,
[ModifiedDateTime] [datetime] NOT NULL
CONSTRAINT [Tier_PK] PRIMARY KEY CLUSTERED([TierRId] ASC)
) ON [PRIMARY]
GO
INSERT [Tier]([ScheduleRId], [Duration], [ModifiedReason], [ModifiedBy], [ModifiedDateTime]) VALUES (1, 5, 'Added', 'Me', GETDATE())
INSERT [Tier]([ScheduleRId], [Duration], [ModifiedReason], [ModifiedBy], [ModifiedDateTime]) VALUES (2, 10, 'Added', 'Me', GETDATE())
INSERT [Tier]([ScheduleRId], [Duration], [ModifiedReason], [ModifiedBy], [ModifiedDateTime]) VALUES (3, 15, 'Added', 'Me', GETDATE())
INSERT [Tier]([ScheduleRId], [Duration], [ModifiedReason], [ModifiedBy], [ModifiedDateTime]) VALUES (4, 20, 'Added', 'Me', GETDATE())
We have nonclustered indexes on ScheduleRId only and as TierRId is Primary Key it has clustered index by default.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 6:35 am
Does your delete uses indexed column in WHERE clause?
High number of logical reads in your case may be due to fragmented indices. Try to defrag them using DBCC INDEXDEFRAG or DBCC DBREINDEX.
Actually what you mean by "nonclustered indexes on ScheduleRId"? Do you have multiple indices on the same column?
August 17, 2010 at 7:03 am
Yes, delete statement uses index column i.e. ScheduleRId in where clause.
I have fragmented the indices in the morning (i.e. 8 hour before). current the table hold few records(50), and seems like will not impact the indices.
ScheduleRId is a foreign key from some other entity (Schedule). as it is FK in Tier entity we have created the nonclustered index on all FKs.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 7:39 am
Ok, the number of logical reads will also depend on how many rows is affected by your query and how many indexes it should update.
As you have index on ScheduleRId, when the row deleted this index will need to be updated. You can try do select query and see. You whould expect may be couple of ligical reads.
If you remove index on ScheduleRId, you will see reducing in number of logical reads as no update of index will be performed. However, INDEX SEEK will not be possible and will be replaced by SCAN. If your table is going to have just around 50 rows always, you will see no difference in performance (as for this number of records, SQL most likely will do SCAN for SELECT query anyway). However, if your table is going to be large, then SCAN will be much more slower than SEEK.
Having INDEX does bring some benefits for SELECT, but it is "offsetted" by negative impact on INSERT/UPDATE/DELETE operations.
August 17, 2010 at 8:02 am
thanks man! i think i got the answer what i was looking for. I do understand the index part.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 1:57 pm
Any foreign keys referencing the table will result in extra reads before the delete.
The probability of survival is inversely proportional to the angle of arrival.
August 17, 2010 at 8:47 pm
Eugene Elutin (8/17/2010)
Ok, the number of logical reads will also depend on how many rows is affected by your query and how many indexes it should update.As you have index on ScheduleRId, when the row deleted this index will need to be updated. You can try do select query and see. You whould expect may be couple of ligical reads.
If you remove index on ScheduleRId, you will see reducing in number of logical reads as no update of index will be performed. However, INDEX SEEK will not be possible and will be replaced by SCAN. If your table is going to have just around 50 rows always, you will see no difference in performance (as for this number of records, SQL most likely will do SCAN for SELECT query anyway). However, if your table is going to be large, then SCAN will be much more slower than SEEK.
Having INDEX does bring some benefits for SELECT, but it is "offsetted" by negative impact on INSERT/UPDATE/DELETE operations.
You beat me to it and "spot on", Eugene! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply