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 2000 Forums
 Transact-SQL (2000)
 LOCKS

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

Posted - 2006-10-24 : 12:17:27
You must be escalating to a table lock, have you looked at what type o lock it is and which process is holding the lock

I'm very suprised that a bulk insert is effected by a lock though

You need to post some more details



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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.
Go to Top of Page

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....



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF

with 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [TR_Linkset_Raw]
ON [dbo].[LINKSET_RAW]
AFTER INSERT

AS

BEGIN
-- 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;

END

This 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF

with 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DAILY_FORCAST_GENERATOR]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- 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';


END


This 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF



using INSERT SELECT from two views to the LINKSET_INTERVAL table. The two views and their criteria are:

LINKSET_DAILY_HIGH_VIEW

CREATE VIEW [dbo].[LINKSET_DAILY_HIGH_VIEW]
AS
SELECT 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_RECV
FROM dbo.LINKSET_INTERVAL AS a
WHERE (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_TEXT

and LINKSET_CURRENT_BANDWIDTH_VIEW

CREATE VIEW [dbo].[LINKSET_CURRENT_BANDWIDTH_VIEW]
AS
SELECT TOP (100) PERCENT LINKSET_NAME, START_DATE_TIME, TRANS_BANDWIDTH, RECV_BANDWIDTH
FROM dbo.LINKSET_INTERVAL AS a
WHERE (START_DATE_TIME =
(SELECT MAX(START_DATE_TIME) AS Expr1
FROM dbo.LINKSET_INTERVAL
WHERE (LINKSET_NAME = a.LINKSET_NAME)))
ORDER BY LINKSET_NAME

There, 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
Go to Top of Page

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
Go to Top of Page

Rliss
Starting Member

31 Posts

Posted - 2006-10-27 : 12:01:37
Yes, it worked
Go to Top of Page
   

- Advertisement -