Optimize Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimize Update Query

Author

Topic

Rimsky
Starting Member

12 Posts

Posted - 2012-07-20 : 04:12:40

Hi All,
A long time ago I wrote a query to update a table. Anyone up for optimzing it?
I have a gut feeling there must be a more efficient way, but don't have a clue.


UPDATE mcmain.hrreg
		SET hr_tot1 = hrdet.hdthrs
		,	hr_d1	=	hrdet.hdhrs1
		,	hr_d2	=	hrdet.hdhrs2
		,	hr_d3	=	hrdet.hdhrs3
		,	hr_d4	=	hrdet.hdhrs4
		,	hr_d5	=	hrdet.hdhrs5
		,	hr_d6	=	hrdet.hdhrs6
		,	hr_d7	=	hrdet.hdhrs7
		,	hr_d8	=	hrdet.hdhrs8
		,	hr_d9	=	hrdet.hdhrs9
		,	hr_d0	=	hrdet.hdhrs0
		,	hr_km	=	hrdet.hdkm
	FROM mcmain.hrreg
	INNER JOIN
	(
		SELECT hd_week, hd_craft, SUM(IsNull(hd_thrs,#)) AS hdthrs, SUM(IsNull(hd_hrs1,#)) AS hdhrs1, SUM(IsNull(hd_hrs2,#)) AS hdhrs2, SUM(IsNull(hd_hrs3,#)) AS hdhrs3, SUM(IsNull(hd_hrs4,#)) AS hdhrs4
		, SUM(IsNull(hd_hrs5,#)) AS hdhrs5, SUM(IsNull(hd_hrs6,#)) AS hdhrs6, SUM(IsNull(hd_hrs7,#)) AS hdhrs7, SUM(IsNull(hd_hrs8,#)) AS hdhrs8, SUM(IsNull(hd_hrs9,#)) AS hdhrs9
		, SUM(IsNull(hd_hrs0,#)) AS hdhrs0, SUM(IsNull(hd_km,#)) AS hdkm
		FROM mcmain.hrdet 
		INNER JOIN mcmain.hrreg ON hr_craft = hd_craft AND hr_week = hd_week
		WHERE hd_stat < #
		GROUP BY hd_week, hd_craft
	) hrdet
	ON hd_craft = hr_craft AND hd_week = hr_week

Table definitions:
hrreg:


CREATE TABLE [mcmain].[hrreg](
	[hr_craft] [char](8) NULL,
	[hr_stat] [char](4) NULL,
	[hr_site] [char](20) NULL,
	[hr_week] [char](7) NULL,
	[hr_tot] [numeric](9, 2) NULL,
	[hr_tot1] [numeric](9, 2) NULL,
	[hr_d1] [numeric](5, 2) NULL,
	[hr_d2] [numeric](5, 2) NULL,
	[hr_d3] [numeric](5, 2) NULL,
	[hr_d4] [numeric](5, 2) NULL,
	[hr_d5] [numeric](5, 2) NULL,
	[hr_d6] [numeric](5, 2) NULL,
	[hr_d7] [numeric](5, 2) NULL,
	[hr_d8] [numeric](5, 2) NULL,
	[hr_d9] [numeric](5, 2) NULL,
	[hr_d0] [numeric](5, 2) NULL,
	[hr_comm] [text] NULL,
	[hr_km] [numeric](6, 0) NULL,
	[hr_km1] [numeric](6, 0) NULL,
	[hr_km2] [numeric](6, 0) NULL,
	[hr_km3] [numeric](6, 0) NULL,
	[hr_km4] [numeric](6, 0) NULL,
	[hr_km5] [numeric](6, 0) NULL,
	[hr_km6] [numeric](6, 0) NULL,
	[hr_km7] [numeric](6, 0) NULL,
	[hr_decl] [numeric](6, 2) NULL,
	[hr_hrerr] [numeric](4, 0) NULL,
	[hr_kmerr] [numeric](4, 0) NULL,
	[hr_dcerr] [numeric](4, 0) NULL,
	[hr_project] [bit] NULL,
	[hr_storing] [bit] NULL,
	[hr_crcok] [bit] NULL,
	[hr_crccr] [char](8) NULL,
	[hr_crcdate] [datetime] NULL,
	[hr_crctime] [char](8) NULL,
	[hr_crlok] [bit] NULL,
	[hr_crlcr] [char](8) NULL,
	[hr_crldate] [datetime] NULL,
	[hr_crltime] [char](8) NULL,
	[hr_cruser] [char](8) NULL,
	[hr_crcrnmr] [char](8) NULL,
	[hr_crsrc] [char](254) NULL,
	[hr_crdate] [datetime] NULL,
	[hr_crtime] [char](8) NULL,
	[hr_chuser] [char](8) NULL,
	[hr_chcrnmr] [char](8) NULL,
	[hr_chsrc] [char](254) NULL,
	[hr_chdate] [datetime] NULL,
	[hr_chtime] [char](8) NULL,
	[hr_cluser] [char](8) NULL,
	[hr_clcrnmr] [char](8) NULL,
	[hr_clsrc] [char](254) NULL,
	[hr_cldate] [datetime] NULL,
	[hr_cltime] [char](8) NULL,
	[hr_numprt] [numeric](6, 0) NULL,
	[hr_fpuser] [char](8) NULL,
	[hr_fpcrnr] [char](8) NULL,
	[hr_fpdate] [datetime] NULL,
	[hr_fptime] [char](8) NULL,
	[hr_lpuser] [char](8) NULL,
	[hr_lpcrnr] [char](8) NULL,
	[hr_lpdate] [datetime] NULL,
	[hr_lptime] [char](8) NULL,
	[hr_sptmp] [char](254) NULL,
	[hr_guid] [char](36) NULL,
	[hr_rowid] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [mcmain].[hrreg] ADD  DEFAULT ((0)) FOR [hr_project]
GO

ALTER TABLE [mcmain].[hrreg] ADD  DEFAULT ((0)) FOR [hr_storing]
GO

ALTER TABLE [mcmain].[hrreg] ADD  DEFAULT ((0)) FOR [hr_crcok]
GO

ALTER TABLE [mcmain].[hrreg] ADD  DEFAULT ((0)) FOR [hr_crlok]
GO

Hrdet:


CREATE TABLE [mcmain].[hrdet](
	[hd_craft] [char](8) NULL,
	[hd_stat] [char](4) NULL,
	[hd_site] [char](20) NULL,
	[hd_week] [char](7) NULL,
	[hd_wonmr] [char](8) NULL,
	[hd_cc] [char](16) NULL,
	[hd_pj] [char](16) NULL,
	[hd_type] [char](4) NULL,
	[hd_ptype] [char](4) NULL,
	[hd_kmtype] [char](4) NULL,
	[hd_presenh] [char](6) NULL,
	[hd_hrs1] [numeric](5, 2) NULL,
	[hd_hrs2] [numeric](5, 2) NULL,
	[hd_hrs3] [numeric](5, 2) NULL,
	[hd_hrs4] [numeric](5, 2) NULL,
	[hd_hrs5] [numeric](5, 2) NULL,
	[hd_hrs6] [numeric](5, 2) NULL,
	[hd_hrs7] [numeric](5, 2) NULL,
	[hd_hrs8] [numeric](5, 2) NULL,
	[hd_hrs9] [numeric](5, 2) NULL,
	[hd_hrs0] [numeric](5, 2) NULL,
	[hd_thrs] [numeric](9, 2) NULL,
	[hd_thrs1] [numeric](9, 2) NULL,
	[hd_ovr] [bit] NULL,
	[hd_ovrtm] [numeric](1, 0) NULL,
	[hd_ctyp] [char](2) NULL,
	[hd_stime1] [char](5) NULL,
	[hd_stime2] [char](5) NULL,
	[hd_stime3] [char](5) NULL,
	[hd_stime4] [char](5) NULL,
	[hd_stime5] [char](5) NULL,
	[hd_stime6] [char](5) NULL,
	[hd_stime7] [char](5) NULL,
	[hd_stime8] [char](5) NULL,
	[hd_stime9] [char](5) NULL,
	[hd_stime0] [char](5) NULL,
	[hd_km] [numeric](4, 0) NULL,
	[hd_km1] [numeric](4, 0) NULL,
	[hd_km2] [numeric](4, 0) NULL,
	[hd_km3] [numeric](4, 0) NULL,
	[hd_km4] [numeric](4, 0) NULL,
	[hd_km5] [numeric](4, 0) NULL,
	[hd_km6] [numeric](4, 0) NULL,
	[hd_km7] [numeric](4, 0) NULL,
	[hd_km8] [numeric](4, 0) NULL,
	[hd_km9] [numeric](4, 0) NULL,
	[hd_km0] [numeric](4, 0) NULL,
	[hd_kmtot] [numeric](8, 0) NULL,
	[hd_uitruk] [bit] NULL,
	[hd_uitruk1] [bit] NULL,
	[hd_uitruk2] [bit] NULL,
	[hd_uitruk3] [bit] NULL,
	[hd_uitruk4] [bit] NULL,
	[hd_uitruk5] [bit] NULL,
	[hd_uitruk6] [bit] NULL,
	[hd_uitruk7] [bit] NULL,
	[hd_uitruk8] [bit] NULL,
	[hd_uitruk9] [bit] NULL,
	[hd_uitruk0] [bit] NULL,
	[hd_km1bij] [bit] NULL,
	[hd_km2bij] [bit] NULL,
	[hd_km3bij] [bit] NULL,
	[hd_km4bij] [bit] NULL,
	[hd_km5bij] [bit] NULL,
	[hd_km6bij] [bit] NULL,
	[hd_km7bij] [bit] NULL,
	[hd_hstflg1] [bit] NULL,
	[hd_hstflg2] [bit] NULL,
	[hd_hstflg3] [bit] NULL,
	[hd_hstflg4] [bit] NULL,
	[hd_hstflg5] [bit] NULL,
	[hd_hstflg6] [bit] NULL,
	[hd_hstflg7] [bit] NULL,
	[hd_meetxt] [char](254) NULL,
	[hd_utrtot] [numeric](3, 0) NULL,
	[hd_comm] [text] NULL,
	[hd_text] [char](254) NULL,
	[hd_extra] [bit] NULL,
	[hd_cruser] [char](8) NULL,
	[hd_crcrnmr] [char](8) NULL,
	[hd_crsrc] [char](254) NULL,
	[hd_crdate] [datetime] NULL,
	[hd_crtime] [char](8) NULL,
	[hd_chuser] [char](8) NULL,
	[hd_chcrnmr] [char](8) NULL,
	[hd_chsrc] [char](254) NULL,
	[hd_chdate] [datetime] NULL,
	[hd_chtime] [char](8) NULL,
	[hd_cluser] [char](8) NULL,
	[hd_clcrnmr] [char](8) NULL,
	[hd_clsrc] [char](254) NULL,
	[hd_cldate] [datetime] NULL,
	[hd_cltime] [char](8) NULL,
	[hd_desc] [char](254) NULL,
	[hd_hrchk] [numeric](1, 0) NULL,
	[hd_kmchk] [numeric](1, 0) NULL,
	[hd_crlok] [bit] NULL,
	[hd_crlcr] [char](8) NULL,
	[hd_crldate] [datetime] NULL,
	[hd_crltime] [char](8) NULL,
	[hd_guid] [char](36) NULL,
	[hd_rowid] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-20 : 06:12:24

What indexes do you have?
I hope there's a unique one on hr_craft, hr_week

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Rimsky
Starting Member

12 Posts

Posted - 2012-07-20 : 06:27:48

quote:
Originally posted by nigelrivett

What indexes do you have?
I hope there's a unique one on hr_craft, hr_week

Sorry, forgot to script the indexes. Yes, there's a PK on hr_craft, hr_week.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources