Long running Insert 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)

Long running Insert query

Author

Topic

ceescap
Starting Member

1 Post

Posted - 2012-02-07 : 06:02:27

Hi all,
I have been sniffing out forums and blogs lately, but need some help with a long running query. It's part of a system of stored procedures. This specified statement used to run in about 5 minutes, but lately has been running up to 72 hours!
Here's the setup:
SQL2005 server with 28GB memory. Two mountpoints to a SAN with shared disks consisting of 10 spindles. Data is on one mountpoint, Log on another, tempdb on data space. Just one userdatabase on this server.
Here's two tables, condit and condmod. Condit contains 800K records, condmod is initially empty. I issue a truncate mcmain.condmod before the process starts, for testing purposes.
:


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__condit__con_notm__000AF8CF]') AND type = 'D')
BEGIN
ALTER TABLE [mcmain].[condit] DROP CONSTRAINT [DF__condit__con_notm__000AF8CF]
END

GO

/****** Object:  Table [mcmain].[condit]    Script Date: 02/07/2012 11:57:47 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[mcmain].[condit]') AND type in (N'U'))
DROP TABLE [mcmain].[condit]
GO

/****** Object:  Table [mcmain].[condit]    Script Date: 02/07/2012 11:57:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[mcmain].[condit]') AND type in (N'U'))
BEGIN
CREATE TABLE [mcmain].[condit](
	[con_levgln] [char](13) NULL,
	[con_stat] [char](4) NULL,
	[con_dscgrp] [char](35) NULL,
	[con_levart] [char](20) NULL,
	[con_desc] [char](50) NULL,
	[con_disc1] [numeric](5, 0) NULL,
	[con_disc2] [numeric](5, 0) NULL,
	[con_disc3] [numeric](5, 0) NULL,
	[con_ntprce] [numeric](9, 0) NULL,
	[con_dtstrt] [datetime] NULL,
	[con_dtend] [datetime] NULL,
	[con_volc] [char](8) NULL,
	[con_updnmr] [char](20) NULL,
	[con_notmod] [bit] NULL,
	[con_ascver] [char](5) NULL,
	[con_prddat] [datetime] NULL,
	[con_cusgln] [char](13) NULL,
	[con_cusdeb] [char](40) NULL,
	[con_rowid] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__condit__con_notm__000AF8CF]') AND type = 'D')
BEGIN
ALTER TABLE [mcmain].[condit] ADD  DEFAULT ((0)) FOR [con_notmod]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__condmod__com_not__7E22B05D]') AND type = 'D')
BEGIN
ALTER TABLE [mcmain].[condmod] DROP CONSTRAINT [DF__condmod__com_not__7E22B05D]
END

GO

/****** Object:  Table [mcmain].[condmod]    Script Date: 02/07/2012 11:57:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[mcmain].[condmod]') AND type in (N'U'))
DROP TABLE [mcmain].[condmod]
GO

/****** Object:  Table [mcmain].[condmod]    Script Date: 02/07/2012 11:57:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[mcmain].[condmod]') AND type in (N'U'))
BEGIN
CREATE TABLE [mcmain].[condmod](
	[com_levgln] [char](13) NULL,
	[com_stat] [char](4) NULL,
	[com_dscgrp] [char](35) NULL,
	[com_levart] [char](20) NULL,
	[com_desc] [char](50) NULL,
	[com_disc1] [numeric](5, 0) NULL,
	[com_disc2] [numeric](5, 0) NULL,
	[com_disc3] [numeric](5, 0) NULL,
	[com_ntprce] [numeric](9, 0) NULL,
	[com_dtstrt] [datetime] NULL,
	[com_dtend] [datetime] NULL,
	[com_volc] [char](8) NULL,
	[com_updnmr] [char](20) NULL,
	[com_notmod] [bit] NULL,
	[com_ascver] [char](8) NULL,
	[com_prddat] [datetime] NULL,
	[com_cusgln] [char](13) NULL,
	[com_cusdeb] [char](40) NULL,
	[com_rowid] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__condmod__com_not__7E22B05D]') AND type = 'D')
BEGIN
ALTER TABLE [mcmain].[condmod] ADD  DEFAULT ((0)) FOR [com_notmod]
END

GO

Here's the isolated code that runs a long time:

		
DECLARE @TempIdTable TABLE ([com_rowid]	Int PRIMARY KEY)

		INSERT @TempIdTable
			([com_rowid])
		SELECT cmd.[com_rowid]
		FROM [mcmain].[condmod] AS cmd
		LEFT OUTER JOIN [mcmain].[condit] AS cdt
		  ON con_levgln	= com_levgln
		 AND IsNull(con_dscgrp,'')	= IsNull(com_dscgrp,'')
		 AND IsNull(con_levart,'')	= IsNull(com_levart,'')
		 AND IsNull(con_volc,'')	= IsNull(com_volc,'')
		 AND IsNull(con_cusgln,'')	= IsNull(com_cusgln,'')
		 AND IsNull(con_cusdeb,'')	= IsNull(com_cusdeb,'')
		WHERE con_levgln is NULL


		--select * from @TempIdTable

			INSERT INTO mcmain.condit
					(con_levgln
					,con_stat
					,con_dscgrp
					,con_levart
					,con_desc
					,con_disc1
					,con_disc2
					,con_disc3
					,con_ntprce
					,con_dtstrt
					,con_dtend
					,con_volc
					,con_notmod
					,con_updnmr
					,con_ascver
					,con_cusgln
					,con_cusdeb)
			SELECT	com_levgln
					,com_stat
					,com_dscgrp
					,com_levart
					,com_desc
					,com_disc1
					,com_disc2
					,com_disc3
					,com_ntprce
					,com_dtstrt
					,com_dtend
					,com_volc
					,com_notmod
					,com_updnmr
					,com_ascver
					,com_cusgln
					,com_cusdeb
			FROM [mcmain].[condmod] AS cmd
			INNER JOIN @TempIdTable AS tit
			  ON tit.com_rowid = cmd.com_rowid

The insert in the @TempIdTable takes forever. What can I do to speed up this process?

TIA

Cees Cappelle

--------------------------
Failing to plan is planning to fail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources