August 18, 2008 at 4:13 pm
This code, when profiled in profiler does 22099883 Reads and 152173 Writes.
Kind wondering, Why so many reads?
IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0
UPDATE ptLabResults SET MRN = RTRIM(MRN)
GO
Table has 2469840 records. Here is the schema:
[LabResultID] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) NULL,
[PID] [varchar](25) NOT NULL,
[PName] [varchar](255) NULL,
[PSSN] [varchar](255) NULL,
[SampleID] [varchar](255) NOT NULL,
[Test] [varchar](255) NOT NULL,
[Description] [varchar](255) NULL,
[DateofTest] [datetime] NOT NULL,
[Approvedby] [varchar](255) NULL,
[Performedat] [varchar](255) NULL,
[ValueType] [varchar](255) NULL,
[ObsID] [varchar](255) NOT NULL,
[ObsSubID] [varchar](255) NULL,
[ObsValue] [varchar](4000) NULL,
[Units] [varchar](255) NULL,
[ReferenceRange] [varchar](255) NULL,
[AbnormalFlags] [varchar](255) NOT NULL,
[ObsResultStatus] [varchar](255) NULL,
[ObsDateTime] [datetime] NULL,
[ProducerID] [varchar](255) NULL,
[OrderedBy] [varchar](255) NULL,
[Comment] [text] NULL,
[TestComment] [varchar](4000) NULL,
[EMRApproved] [int] NULL,
[EMRApprovedBy] [char](10) NULL,
[EMRApprovedDate] [datetime] NULL,
[TriggerEvent] [char](10) NULL,
[LineNum] [int] NULL,
[ApprovalComment] [varchar](250) NULL,
[ApprovedDate] [datetime] NULL,
[EditedBy] [varchar](50) NULL,
[EditedDate] [datetime] NULL,
[ReTested] [int] NULL,
[rrSampleID] [int] NULL,
[ptLabTestID] [int] NULL,
[UrgentFlg] [int] NOT NULL,
[CommentBy] [nvarchar](50) NULL,
[CommentDate] [datetime] NULL,
[TestCommentBy] [nvarchar](50) NULL,
[TestCommentDate] [datetime] NULL,
August 18, 2008 at 4:27 pm
Any indexes or keys? Can you post the .SQLPLAN file?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 18, 2008 at 9:28 pm
:blink: that's a very wide table. What indexes are defined on the table?
And, why the IF (SELECT count(*) FROM sys.tables WHERE name = 'ptLabResults') > 0? It's been quite a while since I've seen logic based around whether a table exists, especially a table with nearly 2.5M rows.
August 19, 2008 at 5:48 am
if you only want to check the existence of the table before updating it, use sysobjects instead, and about your question, please update about the existence of indexes.
August 19, 2008 at 6:04 am
No, both sys.tables and sys.objects are better than sysobjects.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 6:11 am
sry, misinterpreted the question:)
August 19, 2008 at 6:12 am
It's probably doing a table scan, which will certainly cause a lot of reads. Are there any triggers on the table? Is that field a part of a primary or foreign key?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2008 at 7:11 am
No Triggers on this table.
Here are the indexes and keys:
Indexes:
[IX_ptLabResults]
columns: ([EMRApproved] ASC)
[IX_ptLabResults_2]
columns: ([MRN] ASC,
[SampleID] ASC,
[Test] ASC,
[DateofTest] ASC)
[IX_ptLabResults_ApprovedBy]
columns: ([Approvedby] ASC)
[IX_ptLabResults_LabResultID]
columns: ([LabResultID] ASC)
[IX_ptLabResults_MRN]
columns : ([MRN] ASC)
[IX_ptLabResults_ObsResultStatus]
columns: ([ObsResultStatus] ASC)
[IX_ptLabResults_ptLabTestsID]
columns: ([ptLabTestID] ASC)
[IX_ptLabResults_rrSampleID]
columns: ([rrSampleID] ASC)
[IX_ptLabResults_SampleID]
columns: ([SampleID] ASC)
[IX_ptLabResults_Test]
columns: ([Test] ASC)
[IX_ptLabResults_UniqueRecord]
columns: ([MRN] ASC,
[SampleID] ASC,
[Test] ASC,
[DateofTest] ASC,
[ObsID] ASC)
[IX_ptLabResults_UrgentFlg]
columns: ([UrgentFlg] ASC)
[PK_ptLabResults_LabResultID]
columns: ([LabResultID] ASC)
Keys:
[PK_ptLabResults_LabResultID]
columns: ([LabResultID] ASC)
[FK_ptLabResults_ptLabTests]
FOREIGN KEY([ptLabTestID]) REFERENCES [dbo].[ptLabTests] ([PtLabTestID])
[FK_ptLabResults_ptSamples]
FOREIGN KEY([rrSampleID]) REFERENCES [dbo].[ptSamples] ([SampleID])
August 19, 2008 at 7:59 am
SQLPlan is attached.
August 19, 2008 at 8:29 am
Is IX_ptLabResults_UniqueRecord the clustered index?
August 19, 2008 at 8:34 am
Well, the total IO is understandable, since you are updating every row in the table as well as every entry in 3 indexes, what surprises me is that the Writes are so low.
Performance-wise, the big hit is coming from the two indexes: IX_ptLabResults_2 and especially, IX_ptLabResults_UniqueRecord. The sorting of the second one apparently does not fit in memory and results in a lot of IO. Since the "_UniqueRecord" index is a superset of the "_2" index, it would seem that you should be able to drop one of them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 19, 2008 at 8:36 am
See image.
August 19, 2008 at 8:44 am
D'oh - missed seeing the "PK_" in the list.
August 19, 2008 at 8:56 am
Yeah, I was just going to say the same thing about the three indexes. That's what I get for doing work instead of posting to the boards.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2008 at 9:43 am
rbarryyoung (8/19/2008)
Well, the total IO is understandable, since you are updating every row in the table as well as every entry in 3 indexes, what surprises me is that the Writes are so low.Performance-wise, the big hit is coming from the two indexes: IX_ptLabResults_2 and especially, IX_ptLabResults_UniqueRecord. The sorting of the second one apparently does not fit in memory and results in a lot of IO. Since the "_UniqueRecord" index is a superset of the "_2" index, it would seem that you should be able to drop one of them.
I'm sorry, that was from a different DB than the one that I was running the script on.
I have placed code in the script to remove IX_ptLabResults_2, anyway.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply