All geek questions in one place

Long INSERT query on SQL Server 2005

I recently sniffed forums and blogs, but I need help with a long request. This is part of the stored procedure system. This preset operator is used for about 5 minutes, but lately it has been working up to 72 hours!

Here's the setting:

SQL Server 2005 with 28 GB of memory. Two mount points in a SAN with shared drives consisting of 10 spindles. The data is on one mount point, Log on another, tempdb in the data space. Only one user database on this server.

There are two tables here, condit and condmod . condit contains 800K entries, condmod initially empty. I am sending truncated mcmain.condmod before starting the process 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 ******/ 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 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 ******/ 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 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 an isolated code that lasts 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

Insertion in @TempIdTable is done forever. What can I do to speed up this process?

TIA

Cees cappelle

ps I have clustered indexes for both tables, for example:

 /****** Object: Index [condmodTest] Script Date: 02/07/2012 13:24:34 ******/ IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mcmain].[condmod]') AND name = N'condmodTest') CREATE CLUSTERED INDEX [condmodTest] ON [mcmain].[condmod] ( [com_levgln] ASC, [com_dscgrp] ASC, [com_levart] ASC, [com_volc] ASC, [com_cusgln] ASC, [com_cusdeb] 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

If I make a direct choice (just now), it takes 2 seconds.

Here is the code:

 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 LEFT OUTER JOIN mcmain.condit 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

I just made an Actial Execution plan with the following code. It took 3'16 '' ???

 truncate table mcmain.condit CREATE TABLE #TempIdTable ([com_rowid] Int PRIMARY KEY) -- 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 -- AND com_updnmr = @plannummer 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 same statement, but using tablevariable takes 1'39 ''

When I run sp, the same statement takes hours and hours. Still don't get it.

+4
source share
2 answers

If SELECT itself takes a long time

You can use NOT EXISTS rather than OUTER JOIN ... NULL , as this is often more efficient.

Also, I would get rid of comparable ISNULL comparisons.

 SELECT cmd.[com_rowid] FROM [mcmain].[condmod] AS cmd WHERE NOT EXISTS (SELECT * FROM [mcmain].[condit] AS cdt WHERE con_levgln = com_levgln AND EXISTS (SELECT con_dscgrp, con_levart, con_volc, con_cusgln, con_cusdeb INTERSECT SELECT com_dscgrp, com_levart, com_volc, com_cusgln, com_cusdeb))

If SELECT works pretty quickly on its own, but just not when inserting a table into a variable, then check if there is a parallel plan in the quick case.

Queries that are inserted into table variables are not parallelized, so if this is a problem, you might consider modifying the #temp table.

If none of these suggestions help, I suggest you start monitoring the wait types while performing this procedure. See SQL Server 2005 Expectations and Queues.

+5
source

Note: using @tables uses tempdb.

You should try to increase the number of temp db files ... you can start with the number of tempdb files equal to the number of files you processed ... but as a good starting point, just go to 10 ... the problem you might encounter is statement for tempdb files. You should look for a page lock in tempdb.

This article discusses how the generalization that I wrote above is a misconception (but not necessarily wrong), but it explains the problem well. He also points you to other articles that give computer science how to look for page locks.

The myth about SQL Server DBMS per day: (12/30) tempdb should always have one data file for each processor core

0
source

Source: https://habr.com/ru/post/1395218/

More articles:


All Articles