Update query - Performance tuning

Question

Update query - Performance tuning

ivanball_76

Newbie

Points: 2
More actions
December 6, 2012 at 9:34 am

#267800

Good morning,
I am trying to optimize the following query, which executes in 5 minutes and 45 seconds. I added the indexes recommended by Database Engine Tuning Advisor, but it did not improve the execution time. What can I do in order to reduce the query execution time?
UPDATE DWMFRefresh
SET CityTaxableValuePrior =
(SELECT TOP 1 ISNULL(DWPcr_history_tax.tax_val, 0)
FROM DWPcr_history_tax INNER JOIN DWLu_tax_dist
ON DWPcr_history_tax.tax_dist = DWLu_tax_dist.cd
WHERE DWPcr_history_tax.tax_yr = 2012
AND DWPcr_history_tax.Strap = DWMFRefresh.Strap
AND DWPcr_history_tax.tax_val > 0
AND DWLu_tax_dist.cd <> '30'
AND DWLu_tax_dist.icd = 'MUN')
I have attached the query execution plan, and the DDL for the tables involved is:
/****** Object: Table [dbo].[DWMFRefresh] ******/
CREATE TABLE [dbo].[DWMFRefresh](
[Strap] [char](13) NOT NULL,
...
[CityTaxableValuePrior] [decimal](12, 0) NULL,
...,
CONSTRAINT [PK_DWMFRefresh] PRIMARY KEY CLUSTERED
(
[Strap] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DWMFRefresh] ON [dbo].[DWMFRefresh]
(
[Strap] 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
/****** Object: Table [dbo].[DWPcr_history_tax] ******/
CREATE TABLE [dbo].[DWPcr_history_tax](
[tax_yr] [int] NOT NULL,
[acct_tp] [char](1) NOT NULL,
[strap] [char](25) NOT NULL,
[v_id] [int] NOT NULL,
[tax_dist] [varchar](6) NOT NULL,
...
[tax_val] [decimal](12, 0) NULL,
CONSTRAINT [PK_DWPcr_history_tax] PRIMARY KEY CLUSTERED
(
[tax_yr] ASC,
[acct_tp] ASC,
[strap] ASC,
[v_id] ASC,
[tax_dist] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
ALTER TABLE [dbo].[DWPcr_history_tax] WITH CHECK ADD CONSTRAINT [FK_DWPcr_history_tax_DWLu_tax_dist] FOREIGN KEY([tax_dist])
REFERENCES [dbo].[DWLu_tax_dist] ([cd])
GO
ALTER TABLE [dbo].[DWPcr_history_tax] CHECK CONSTRAINT [FK_DWPcr_history_tax_DWLu_tax_dist]
GO
/****** Object: Table [dbo].[DWLu_tax_dist] ******/
CREATE TABLE [dbo].[DWLu_tax_dist](
[cd] [varchar](6) NOT NULL,
...
[dscr] [varchar](150) NULL,
...
[icd] [char](3) NULL,
...,
CONSTRAINT [PK_DWLu_tax_dist] PRIMARY KEY CLUSTERED
(
[cd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DWLu_tax_dist] ON [dbo].[DWLu_tax_dist]
(
[icd] 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
CREATE STATISTICS [_dta_stat_DWLu_tax_dist] ON [dbo].[DWLu_tax_dist]([icd], [cd])
GO
Thanks in advance,
ivanball.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ivanball_76 Newbie Points: 2 More actions · Answer 1

Thanks Celko.

By using MAX() instead of TOP 1, the query execution time got reduced a lot. I do not know if this is because some things are already cached or not. I will try again tomorrow after restarting the sql server service.

Regarding the duplicated indexes (CLUSTERED and NON CLUSTERED), the only one I see duplicated is on the DWMFRefresf.strap column. I added the NON CLUSTERED index because that was a "recommendation" from Database Engine Tuning Advisor tool. I do not know why that tool "recommended" to add that index duplicated, and if that could affect performance.

Thanks again,

ivanball.