CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005indexing
DanBennett
DanBennett

Reputation: 448

Very Slow Update despite Indexing MSSQL 2005

EDIT: We're in the process of moving server and I've just tested this on the new server. There's no performance problem there. This seems to be down to an underpowered, badly organised server.

One of our processes suddenly ran very slowly last night. The slow step was tracked down to an update statement on a table that was admittedly not too cleverly indexed.

So today I've added indexes to all the tables involved, but I'm still getting terrible performance. I really don't understand it - possibly I'm still doing something less than smart. Any suggestions welcomed.

update is as follows:

update test_HDM_RTT
set patient_district_no = b.legacy_number
from test_HDM_RTT a
inner join PHD.migration.PatScope b
on a.patient_pas_no = b.TrustNumber

patscope is 2474147 rows, test_hdm_rtt is 815278

definition of tables:

CREATE TABLE [dbo].[test_HDM_RTT](
    [pk_episode_id] [int] NULL,
    [pk_event_id] [int] NOT NULL,
    [activity_date] [datetime] NULL,
    [activity_datetime] [datetime] NULL,
    [activity_subtype1] [nvarchar](50) NULL,
    [activity_subtype1_code] [nvarchar](50) NULL,
    [activity_subtype2] [nvarchar](50) NULL,
    [activity_subtype2_code] [nvarchar](50) NULL,
    [activity_type] [nvarchar](50) NULL,
    [activity_type_code] [nvarchar](50) NULL,
    [clock_start_date] [datetime] NULL,
    [clock_stop_date] [datetime] NULL,
    [dir_code] [nvarchar](10) NULL,
    [div_code] [nvarchar](10) NULL,
    [episode_id_ext] [nvarchar](50) NULL,
    [episode_id_appt] [nvarchar](50) NULL,
    [episode_id_ref] [nvarchar](50) NULL,
    [episode_id_ref_medway] [nvarchar](50) NULL,
    [episode_id_wl] [nvarchar](50) NULL,
    [erod] [datetime] NULL,
    [nhs_number] [nvarchar](20) NULL,
    [patient_id] [int] NULL,
    [patient_district_no] [nvarchar](20) NULL,
    [patient_pas_no] [nvarchar](50) NULL,
    [pathway_id] [nvarchar](50) NULL,
    [pct_code] [nvarchar](10) NULL,
    [ref_source_code] [nvarchar](10) NULL,
    [rtt_episode_id] [nvarchar](50) NULL,
    [rtt_outcome_code] [nvarchar](50) NULL,
    [rtt_outcome_desc] [nvarchar](50) NULL,
    [rtt_start_date] [datetime] NULL,
    [rtt_start_ind] [nvarchar](10) NULL,
    [rtt_stop_date] [datetime] NULL,
    [site_code] [nvarchar](10) NULL,
    [spec_natcode] [nvarchar](10) NULL,
    [spec_pascode] [nvarchar](10) NULL,
    [transfer_text] [nvarchar](100) NULL,
    [op_rtt_count] [int] NULL,
    [app_rec_date] [datetime] NULL,
    [cons_code] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [PIP]
/****** Object:  Index [pk_event_id_clustered]    Script Date: 03/06/2013 14:46:52 ******/
CREATE CLUSTERED INDEX [pk_event_id_clustered] ON [dbo].[test_HDM_RTT] 
(
    [pk_event_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PIP]
/****** Object:  Index [idx_episode_id_appt]    Script Date: 03/06/2013 14:46:52 ******/
CREATE NONCLUSTERED INDEX [idx_episode_id_appt] ON [dbo].[test_HDM_RTT] 
(
    [episode_id_appt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PIP]
/****** Object:  Index [idx_episode_id_ref]    Script Date: 03/06/2013 14:46:52 ******/
CREATE NONCLUSTERED INDEX [idx_episode_id_ref] ON [dbo].[test_HDM_RTT] 
(
    [episode_id_ref] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PIP]
/****** Object:  Index [idx_episode_id_wl]    Script Date: 03/06/2013 14:46:52 ******/
CREATE NONCLUSTERED INDEX [idx_episode_id_wl] ON [dbo].[test_HDM_RTT] 
(
    [episode_id_wl] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PIP]
/****** Object:  Index [patient_pas_no]    Script Date: 03/06/2013 14:46:52 ******/
CREATE NONCLUSTERED INDEX [patient_pas_no] ON [dbo].[test_HDM_RTT] 
(
    [patient_pas_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and

USE [PHD]
GO

/****** Object:  Table [migration].[PatScope]    Script Date: 03/06/2013 14:47:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [migration].[PatScope](
    [RID] [varchar](7) NOT NULL,
    [Number] [varchar](17) NOT NULL,
    [TrustNumber] [varchar](10) NULL,
    [NumberType] [nvarchar](10) NULL,
    [legacy_number] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [PHD]
/****** Object:  Index [TrustNoClustered]    Script Date: 03/06/2013 14:47:57 ******/
CREATE CLUSTERED INDEX [TrustNoClustered] ON [migration].[PatScope] 
(
    [TrustNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PHD]
/****** Object:  Index [TrustNo]    Script Date: 03/06/2013 14:47:57 ******/
CREATE NONCLUSTERED INDEX [TrustNo] ON [migration].[PatScope] 
(
    [TrustNumber] ASC,
    [Number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [PHD]
/****** Object:  Index [TrustNumber_legacy_lookup]    Script Date: 03/06/2013 14:47:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [TrustNumber_legacy_lookup] ON [migration].[PatScope] 
(
    [TrustNumber] ASC,
    [legacy_number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Upvotes: 0

Views: 1249

Answers (2)

David T. Macknet
David T. Macknet

Reputation: 3162

Show Execution Plan & see if the plan uses indexes. If it doesn't, you can force it to use a particular index:

SET ANSI_NULLS OFF
GO

update test_HDM_RTT
set patient_district_no = b.legacy_number
from test_HDM_RTT a
inner join PHD.migration.PatScope b WITH (INDEX(TrustNumber_legacy_lookup))
on a.patient_pas_no = b.TrustNumber
where a.patient_district_no <> b.legacy_number

You'll want to see if you need to force an index on test_HDM_RTT instead because it looks like it's already going to do an index scan of TrustNumber_legacy_lookup to get its data.

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

personally, I would only update if the value is not the same as existing. That should speed up the query

update test_HDM_RTT
set patient_district_no = b.legacy_number
from test_HDM_RTT a
 inner join PHD.migration.PatScope b
 on a.patient_pas_no = b.TrustNumber
where a.patient_district_no <> b.legacy_number

I would also check out the EXPLAIN results (ctrl + l) your query may be using the wrong index.

Upvotes: 3

Related Questions