November 29, 2012 at 12:03 am
Hi all,
I have a query which inserts calendar information into a table called as tblcob. There are many views which are schema binded to this table. Now when I'm inserting 365 (1 year) rows into this table it is taking around 6 minutes to run. Suppose I run for just 1 month (30 days) it takes 2 seconds. I run for 2 monts it takes 17 seconds. 3 months 45 seconds and so on. In the execution plan I found out that most of the cost was coming from clustered indexed update on the views.
Please let me know how can i improve the performance. Also any more data if you require like the table structure which will be helpful in troubleshooting, I will be able to provide.
Thanks.
November 30, 2012 at 7:53 am
November 30, 2012 at 8:10 am
MOC Ewez (11/30/2012)
Go on then, post the helpful data.
Hi,
What kind of data should I post which would be helpful?
Thanks
November 30, 2012 at 8:22 am
This is the syntax of the table
----------------------------------------------------------------------
USE [CONCENTRIX_DM]
GO
/****** Object: Table [dbo].[tblCob] Script Date: 11/30/2012 23:20:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCob](
[ID] [dbo].[udtID] IDENTITY(1,1) NOT NULL,
[LocationID] [dbo].[udtID] NOT NULL,
[Cob] [dbo].[udtCOBDate] NOT NULL,
[IsMonthEnd] [dbo].[udtYesNo] NOT NULL,
[IsWeekend] [dbo].[udtYesNo] NOT NULL,
[IsYearEnd] [dbo].[udtYesNo] NOT NULL,
[IsHoliday] [dbo].[udtYesNo] NOT NULL,
[IsCurrent] [dbo].[udtYesNo] NOT NULL CONSTRAINT [DF__tblCob__IsCurren__1273C1CD] DEFAULT (0),
[LastUpdated] [dbo].[udtDateTime] NOT NULL CONSTRAINT [DF__tblCob__LastUpda__1367E606] DEFAULT (getdate()),
[UpdatedBy] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__tblCob__UpdatedB__145C0A3F] DEFAULT (user_name()),
[IsActualCob] [dbo].[udtYesNo] NOT NULL DEFAULT (0),
PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCob] WITH NOCHECK ADD FOREIGN KEY([LocationID])
REFERENCES [dbo].[tblLocation] ([ID])
---------------------------------------------------------------------------------------------------
Below is the query which will insert the data
----------------------------------------------------------------------------------------------------
use CONCENTRIX_DM
go
SET ARITHABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
go
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @LocationCode udtLocationCode
DECLARE @LocationID Int
SET @StartDate= '01 Jan 2013'
SET @EndDate = '31 Dec 2013'
SET @LocationCode = 'ASD'
Select @LocationID = ID from tblLocation Where LocationCode = @LocationCode
IF @LocationID is NULL
BEGIN
Raiserror('Invalid LocationCode is being passed', 16, 1)
RETURN
END
IF EXISTS (select count(*) from tblcob where YEAR(COB) = YEAR(@StartDate) AND locationid = @LocationID)
Delete from tblcob where YEAR(COB) = YEAR(@StartDate) AND locationid = @LocationID
-- Generate temp table
select LocationID,Cob,IsMonthEnd,IsWeekend,IsYearEnd,IsHoliday into #cob from tblcob where 1=0
WHILE 1=1
BEGIN
INSERT INTO #COB(LocationID, Cob, IsMonthEnd,IsWeekEnd, IsYearEnd, IsHoliday)
SELECT @LocationID,
@StartDate,
0,--To calculate
CASE WHEN DATENAME(weekday,@StartDate) IN ('Saturday','Sunday') THEN 1 ELSE 0 END,
0,--To calculate
0--To calculate
SELECT @StartDate = @StartDate + 1
IF @StartDate > @EndDate
BREAK
END
/* Update script needs to be written based on the List of Holidays provided by the user for that location */
UPDATE #COB
SET IsHoliday = 1
WHERECOB in ('01 Jan 2013','28 Jan 2013','29 Mar 2013','01 Apr 2013','25 Apr 2013','10 Jun 2013','05 Aug 2013','07 Oct 2013','25 Dec 2013',
'26 Dec 2013')
--Month Ends
UPDATE c
SET IsMonthEnd = 1
FROM (SELECT c1.Cob AS Cob1, MIN(c2.Cob) AS Cob2, c1.LocationID
FROM #COB AS c1
CROSS JOIN #COB AS c2
WHERE c1.LocationID = c2.LocationID
AND c1.COB < c2.COB
AND c1.IsWeekend = 0
AND c2.IsWeekend = 0
AND c1.IsHoliday = 0
AND c2.IsHoliday = 0
GROUP BY c1.COB, c1.LocationID) AS Cob
INNER JOIN #COB c ON c.Cob = COB.Cob1 AND c.LocationID = Cob.LocationID
WHERE DATEPART(month,Cob.Cob1) <> DATEPART(month,Cob.Cob2)
UPDATE #COB
SET ISYEAREND = 1
,ISMONTHEND = 1
WHERE COB=
(SELECT MAX(COB) FROM #COB WHERE ISHOLIDAY = 0 AND ISWEEKEND = 0)
INSERT INTO TBLCOB
SELECT
LocationID
,Cob
,IsMonthEnd
,IsWeekend
,IsYearEnd
,IsHoliday
,0
,getDate()
,''
,0
FROM #COB
Drop table #COB
SET NOCOUNT OFF
GO
----------------------------------------------------------------------------------------------
November 30, 2012 at 8:35 am
November 30, 2012 at 9:08 am
1. take create temp table approach instead of select into approach coz it also lock the main base table and i dnt think here you require to refer this base physical table as your columns are static.
2. add suitable indexes on #cob table it will help you in below queries like join etc.
3. try to incorporate #cob update in first #cob insert.
4. another thing you first Delete statement could be making huge impact which is taking time when no of records are larger.
last but not least ...... post your exec plan so that other persons put more light here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 30, 2012 at 10:59 am
Bhuvnesh (11/30/2012)
1. take create temp table approach instead of select into approach coz it also lock the main base table and i dnt think here you require to refer this base physical table as your columns are static.2. add suitable indexes on #cob table it will help you in below queries like join etc.
3. try to incorporate #cob update in first #cob insert.
4. another thing you first Delete statement could be making huge impact which is taking time when no of records are larger.
last but not least ...... post your exec plan so that other persons put more light here
Thanks for the reply. Attached is the exec plan for the query which is taking 100% of the batch cost.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply