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.
Author |
Topic |
Rliss
Starting Member
31 Posts |
Posted - 2006-10-24 : 12:13:08
|
I have a SQL Server database in which data is inserted every 15 minutes via Bulk Insert. I have a batch procedure for daily summing that is scheduled to run once a day. The batch procedure runs the entire table where the data is inserted every 15 minutes. On slower machines, this batch takes longer than 15 minutes. When our application tries to insert, it gets hung and sometimes times out, losing data. How can I SET TRANSACTION to enable me to insert into the table while this batch is running?RLiss |
|
X002548
Not Just a Number
15586 Posts |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-24 : 12:40:21
|
I would stop the import process while the batch job is running.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-24 : 12:48:44
|
Or create the steps in a job, have the batch run first and check for the time when it's suppose to run, if it's not the right time, then run step 2 to do the import, if it is the right time run the batch, when it's complete then run step 2 for the import...that way it would be serialized....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
Rliss
Starting Member
31 Posts |
Posted - 2006-10-24 : 15:29:55
|
More details, you got it, I'm just going to unload on you. This database stores usage data from telephone network switch monitors. .csv files are recieved every fifteen minutes from multiple locations. These files get bulk inserted into a raw data table, LINKSET_RAW, that looks like this:USE [LinkOccupancy]GO/****** Object: Table [dbo].[LINKSET_RAW] Script Date: 10/24/2006 13:58:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[LINKSET_RAW]( [UNIT_ID] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LINKSET_NAME] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [START_DATE_TIME] [datetime] NOT NULL, [HOUR_OF_DAY] [smallint] NULL, [SEQUENCE_IN_HOUR] [smallint] NULL, [LINK_COUNT] [smallint] NULL, [TRANS_ISUP_OCT] [numeric](7, 0) NULL, [TRANS_SCCP_OCT] [numeric](7, 0) NULL, [TRANS_OTHER_OCT] [numeric](7, 0) NULL, [RECV_ISUP_OCT] [numeric](7, 0) NULL, [RECV_SCCP_OCT] [numeric](7, 0) NULL, [RECV_OTHER_OCT] [numeric](7, 0) NULL, [TRANS_BANDWIDTH] [numeric](7, 0) NULL, [RECV_BANDWIDTH] [numeric](7, 0) NULL, [TRANS_PCT_OCC] [numeric](6, 2) NULL, [RECV_PCT_OCC] [numeric](6, 2) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFwith the primary key:USE [LinkOccupancy]GO/****** Object: Index [LINKSET_RAW_PK] Script Date: 10/24/2006 13:59:23 ******/CREATE UNIQUE NONCLUSTERED INDEX [LINKSET_RAW_PK] ON [dbo].[LINKSET_RAW] ( [UNIT_ID] ASC, [LINKSET_NAME] ASC, [START_DATE_TIME] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]The insert fires the following trigger:USE [LinkOccupancy]GO/****** Object: Trigger [TR_Linkset_Raw] Script Date: 10/24/2006 14:00:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE TRIGGER [TR_Linkset_Raw] ON [dbo].[LINKSET_RAW] AFTER INSERTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE [LinkOccupancy].[dbo].[LINKSET_INTERVAL] SET link_count = li.link_count + ins.link_count, trans_isup_oct = li.trans_isup_oct + ins.trans_isup_oct, trans_sccp_oct = li.trans_sccp_oct + ins.trans_sccp_oct, trans_other_oct = li.trans_other_oct + ins.trans_other_oct, recv_isup_oct = li.recv_isup_oct + ins.recv_isup_oct, recv_sccp_oct = li.recv_sccp_oct + ins.recv_sccp_oct, recv_other_oct = li.recv_other_oct + ins.recv_other_oct, trans_bandwidth = li.trans_bandwidth + ins.trans_bandwidth, recv_bandwidth = li.recv_bandwidth + ins.recv_bandwidth, high_pct_occupancy = case when ((ins.trans_bandwidth + li.trans_bandwidth = 0) and (ins.recv_bandwidth + li.recv_bandwidth = 0)) then 0 when ((ins.trans_bandwidth + li.trans_bandwidth = 0) and (ins.recv_bandwidth + li.recv_bandwidth <> 0)) then (((li.Recv_ISUP_Oct + li.Recv_SCCP_Oct + li.Recv_Other_Oct) + (ins.Recv_ISUP_Oct + ins.Recv_SCCP_Oct + ins.Recv_Other_Oct)) / (li.RECV_Bandwidth + ins.RECV_bandwidth))*100 when ((ins.trans_bandwidth + li.trans_bandwidth <> 0) and (ins.recv_bandwidth + li.recv_bandwidth = 0)) then (((li.Trans_ISUP_Oct + li.Trans_SCCP_Oct + li.Trans_Other_Oct) + (ins.Trans_ISUP_Oct + ins.Trans_SCCP_Oct + ins.Trans_Other_Oct)) / (li.TRANS_Bandwidth + ins.tRANS_bandwidth))*100 else case when ((((li.Trans_ISUP_Oct + li.Trans_SCCP_Oct + li.Trans_Other_Oct) + (ins.Trans_ISUP_Oct + ins.Trans_SCCP_Oct + ins.Trans_Other_Oct)) / (li.TRANS_Bandwidth + ins.tRANS_bandwidth))*100) > ((((li.Recv_ISUP_Oct + li.Recv_SCCP_Oct + li.Recv_Other_Oct) + (ins.Recv_ISUP_Oct + ins.Recv_SCCP_Oct + ins.Recv_Other_Oct)) / (li.RECV_Bandwidth + ins.RECV_bandwidth))*100) then (((li.Trans_ISUP_Oct + li.Trans_SCCP_Oct + li.Trans_Other_Oct) + (ins.Trans_ISUP_Oct + ins.Trans_SCCP_Oct + ins.Trans_Other_Oct)) / (li.TRANS_Bandwidth + ins.tRANS_bandwidth))*100 else (((li.Recv_ISUP_Oct + li.Recv_SCCP_Oct + li.Recv_Other_Oct) + (ins.Recv_ISUP_Oct + ins.Recv_SCCP_Oct + ins.Recv_Other_Oct)) / (li.RECV_Bandwidth + ins.RECV_bandwidth))*100 end end, high_trans_or_recv = case when ((ins.trans_bandwidth + li.trans_bandwidth = 0) and (ins.recv_bandwidth + li.recv_bandwidth = 0)) then 'T' when ((ins.trans_bandwidth + li.trans_bandwidth = 0) and (ins.recv_bandwidth + li.recv_bandwidth <> 0)) then 'R' when ((ins.trans_bandwidth + li.trans_bandwidth <> 0) and (ins.recv_bandwidth + li.recv_bandwidth = 0)) then 'T' else case when ((((li.Trans_ISUP_Oct + li.Trans_SCCP_Oct + li.Trans_Other_Oct) + (ins.Trans_ISUP_Oct + ins.Trans_SCCP_Oct + ins.Trans_Other_Oct)) / (li.TRANS_Bandwidth + ins.tRANS_bandwidth))*100) > ((((li.Recv_ISUP_Oct + li.Recv_SCCP_Oct + li.Recv_Other_Oct) + (ins.Recv_ISUP_Oct + ins.Recv_SCCP_Oct + ins.Recv_Other_Oct)) / (li.RECV_Bandwidth + ins.RECV_bandwidth))*100) then 'T' else 'R' end end FROM LINKSET_INTERVAL li INNER JOIN inserted ins ON li.linkset_name = ins.linkset_name AND li.start_date_time = ins.start_date_time; INSERT INTO [LinkOccupancy].[dbo].[LINKSET_INTERVAL] SELECT ins.[Linkset_Name] ,ins.[Start_Date_Time] ,CONVERT(CHAR(8), ins.[Start_Date_Time], 112) ,ins.[Hour_Of_Day] ,ins.[Sequence_In_Hour] ,ins.[Link_Count] ,ins.[Trans_ISUP_Oct] ,ins.[Trans_SCCP_Oct] ,ins.[Trans_Other_Oct] ,ins.[Recv_ISUP_Oct] ,ins.[Recv_SCCP_Oct] ,ins.[Recv_Other_Oct] ,ins.[Trans_Bandwidth] ,ins.[Recv_Bandwidth] ,case when (ins.trans_bandwidth = 0 and ins.recv_bandwidth = 0) then 0 when (ins.trans_bandwidth = 0 and ins.recv_bandwidth <> 0) then (((ins.[Recv_ISUP_Oct]+ins.[Recv_SCCP_Oct]+ins.[Recv_Other_Oct])/ins.[Recv_Bandwidth])*100) when (ins.trans_bandwidth <> 0 and ins.recv_bandwidth = 0) then (((ins.[Trans_ISUP_Oct]+ins.[Trans_SCCP_Oct]+ins.[Trans_Other_Oct])/ins.[Trans_Bandwidth])*100) else case when (((ins.[Trans_ISUP_Oct]+ins.[Trans_SCCP_Oct]+ins.[Trans_Other_Oct])/ins.[Trans_Bandwidth])*100) > (((ins.[Recv_ISUP_Oct]+ins.[Recv_SCCP_Oct]+ins.[Recv_Other_Oct])/ins.[Recv_Bandwidth])*100) then (((ins.[Trans_ISUP_Oct]+ins.[Trans_SCCP_Oct]+ins.[Trans_Other_Oct])/ins.[Trans_Bandwidth])*100) else (((ins.[Recv_ISUP_Oct]+ins.[Recv_SCCP_Oct]+ins.[Recv_Other_Oct])/ins.[Recv_Bandwidth])*100) end end ,case when (ins.trans_bandwidth = 0 and ins.recv_bandwidth = 0) then 'T' when (ins.trans_bandwidth = 0 and ins.recv_bandwidth <> 0) then 'R' when (ins.trans_bandwidth <> 0 and ins.recv_bandwidth = 0) then 'T' else case when (((ins.[Trans_ISUP_Oct]+ins.[Trans_SCCP_Oct]+ins.[Trans_Other_Oct])/ins.[Trans_Bandwidth])*100) > (((ins.[Recv_ISUP_Oct]+ins.[Recv_SCCP_Oct]+ins.[Recv_Other_Oct])/ins.[Recv_Bandwidth])*100) then 'T' else 'R' end end FROM inserted ins left join linkset_interval lsint on lsint.linkset_name = ins.linkset_name and lsint.start_date_time = ins.start_date_time where lsint.linkset_name is null;ENDThis trigger either INSERTS or UPDATES records in the LINKSET_INTERVAL table, which looks like this: USE [LinkOccupancy]GO/****** Object: Table [dbo].[LINKSET_INTERVAL] Script Date: 10/24/2006 14:03:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[LINKSET_INTERVAL]( [LINKSET_NAME] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [START_DATE_TIME] [datetime] NOT NULL, [START_DATE_TEXT] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [HOUR_OF_DAY] [smallint] NULL, [SEQUENCE_IN_HOUR] [smallint] NULL, [LINK_COUNT] [smallint] NULL, [TRANS_ISUP_OCT] [numeric](7, 0) NULL, [TRANS_SCCP_OCT] [numeric](7, 0) NULL, [TRANS_OTHER_OCT] [numeric](7, 0) NULL, [RECV_ISUP_OCT] [numeric](7, 0) NULL, [RECV_SCCP_OCT] [numeric](7, 0) NULL, [RECV_OTHER_OCT] [numeric](7, 0) NULL, [TRANS_BANDWIDTH] [numeric](7, 0) NULL, [RECV_BANDWIDTH] [numeric](7, 0) NULL, [HIGH_PCT_OCCUPANCY] [numeric](9, 6) NULL, [HIGH_TRANS_OR_RECV] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFwith a primary key of:USE [LinkOccupancy]GO/****** Object: Index [LINKSET_INTERVAL_PK] Script Date: 10/24/2006 14:04:08 ******/CREATE UNIQUE NONCLUSTERED INDEX [LINKSET_INTERVAL_PK] ON [dbo].[LINKSET_INTERVAL] ( [LINKSET_NAME] ASC, [START_DATE_TIME] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]and an alternate index:USE [LinkOccupancy]GO/****** Object: Index [LINKSET_INTERVAL_INDX2] Script Date: 10/24/2006 14:04:36 ******/CREATE NONCLUSTERED INDEX [LINKSET_INTERVAL_INDX2] ON [dbo].[LINKSET_INTERVAL] ( [LINKSET_NAME] ASC, [START_DATE_TEXT] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]To give you an idea of how much data this adds up to. We monitor about 200 linksets X 96 times intervals per day. The data is kept for 3 years. For forcasting purposes we need to be able to caculate in terms of current bandwidth. Because this is a complex query that would take a couple of minutes on demand. I run it every night and store the results. Here is the procudure to take care of that:USE [LinkOccupancy]GO/****** Object: StoredProcedure [dbo].[DAILY_FORCAST_GENERATOR] Script Date: 10/24/2006 14:14:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE [dbo].[DAILY_FORCAST_GENERATOR] -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DELETE FROM [LinkOccupancy].[dbo].[LINKSET_YEAR_MONTH_TOP10]; INSERT INTO DBO.LINKSET_YEAR_MONTH_TOP10 SELECT TOP (100) PERCENT A.LINKSET_NAME, SUBSTRING(A.START_DATE_TEXT, 1, 6) AS YEAR_MONTH, SUBSTRING(A.START_DATE_TEXT, 1, 4) + CONVERT(CHAR(1),DATEPART(Qq,A.START_DATE_TIME)) AS YEAR_QTR, CASE [high_Trans_or_recv] WHEN 'T' THEN ([Trans_ISUP_Oct] + [Trans_SCCP_Oct] + [Trans_Other_Oct]) ELSE ([Recv_ISUP_Oct] + [Recv_SCCP_Oct] + [Recv_Other_Oct]) END AS HIGH_OCTET_COUNT, CASE [high_Trans_or_recv] WHEN 'T' THEN B.TRANS_BANDWIDTH ELSE B.RECV_BANDWIDTH END AS TODAYS_BANDWIDTH FROM dbo.LINKSET_DAILY_HIGH_VIEW AS A INNER JOIN dbo.LINKSET_CURRENT_BANDWIDTH_VIEW AS B ON A.LINKSET_NAME = B.LINKSET_NAME WHERE ((SELECT COUNT(1) AS Expr1 FROM dbo.LINKSET_DAILY_HIGH_VIEW WHERE (LINKSET_NAME = A.LINKSET_NAME) AND (CONVERT(CHAR(6), START_DATE_TIME, 112) = CONVERT(CHAR(6), A.START_DATE_TIME, 112)) AND (HIGH_PCT_OCCUPANCY >= A.HIGH_PCT_OCCUPANCY)) <= 10) ORDER BY A.LINKSET_NAME, year_month, HIGH_OCTET_COUNT DESC ; UPDATE [LinkOccupancy].[dbo].[AUTOMATED_PROCESSES] SET LAST_RUN_DATE = GETDATE() WHERE AUTO_PROCESS_ID = '1';ENDThis procedure stores the top10 records for each month for each linkset into the table LINKSET_YEAR_MONTH_TOP10, which looks like this:USE [LinkOccupancy]GO/****** Object: Table [dbo].[LINKSET_YEAR_MONTH_TOP10] Script Date: 10/24/2006 14:18:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[LINKSET_YEAR_MONTH_TOP10]( [LINKSET_NAME] [nchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [YEAR_MONTH] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [YEAR_QTR] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [HIGH_OCTET_COUNT] [numeric](9, 0) NULL, [TODAYS_BANDWIDTH] [numeric](7, 0) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFusing INSERT SELECT from two views to the LINKSET_INTERVAL table. The two views and their criteria are:LINKSET_DAILY_HIGH_VIEWCREATE VIEW [dbo].[LINKSET_DAILY_HIGH_VIEW]ASSELECT TOP (100) PERCENT LINKSET_NAME, START_DATE_TIME, START_DATE_TEXT, TRANS_ISUP_OCT, TRANS_SCCP_OCT, TRANS_OTHER_OCT, RECV_ISUP_OCT, RECV_SCCP_OCT, RECV_OTHER_OCT, TRANS_BANDWIDTH, RECV_BANDWIDTH, HIGH_PCT_OCCUPANCY, HIGH_TRANS_OR_RECVFROM dbo.LINKSET_INTERVAL AS aWHERE (HIGH_PCT_OCCUPANCY = (SELECT MAX(HIGH_PCT_OCCUPANCY) AS Expr1 FROM dbo.LINKSET_INTERVAL WHERE (LINKSET_NAME = a.LINKSET_NAME) AND (START_DATE_TEXT = a.START_DATE_TEXT)))ORDER BY LINKSET_NAME, START_DATE_TEXTand LINKSET_CURRENT_BANDWIDTH_VIEWCREATE VIEW [dbo].[LINKSET_CURRENT_BANDWIDTH_VIEW]ASSELECT TOP (100) PERCENT LINKSET_NAME, START_DATE_TIME, TRANS_BANDWIDTH, RECV_BANDWIDTHFROM dbo.LINKSET_INTERVAL AS aWHERE (START_DATE_TIME = (SELECT MAX(START_DATE_TIME) AS Expr1 FROM dbo.LINKSET_INTERVAL WHERE (LINKSET_NAME = a.LINKSET_NAME)))ORDER BY LINKSET_NAMEThere, I think that's everything. I can't stop the data from generating every 15 minutes, but I could possibly delay inserting until the batch is complete, but I would much rather be able to insert concurrently...Thanks for listening...RLiss |
|
|
Rliss
Starting Member
31 Posts |
Posted - 2006-10-25 : 15:25:54
|
I've been grinding this one out and have found "SET TRANSACTION ISOLATION LEVEL SNAPSHOT". To use this, you have to, at the database level, set ALLOW_SNAPSHOT_ISOLATION to "ON". Preliminary tests are positive, tomorrow, I plan to install it into a full blown production DB. Thanks to all who pondered. I'll let you know the results. Feel free to share any thoughts...RLiss |
|
|
Rliss
Starting Member
31 Posts |
Posted - 2006-10-27 : 12:01:37
|
Yes, it worked |
|
|
|
|
|
|
|