I am facing a performance issue due to "Insert into" statement in sql. I am using a CTE to select data from multiple tables and insert into other table. It was working just fine until yesterday. Select takes less than a minute to retrieve the data where as insert into taking forever. Can some one please help me in understanding what i am doing wrong. Any help is highly appreciated. Thanks.
Here is my code:
I am using this query in an SP. I am trying to load 220K records to 1.5M records table.
;with CTE_A
AS
(
SELECT A1, A2,...
FROM dbo.A with (nolock)
WHERE A1 = <some condition>
GROUP BY a.A1,a.A2 , a.A3
), CTE_C as
(
SELECT C1, C2,....
FROM dbo.B with (nolock)
WHERE a.C1 = <some condition>
GROUP BY a.c1,a.C2 , a.C3
)
INSERT INTO [dbo].MainTable
SELECT
A1, A2, A3 , C1, C2, C3
FROM
CTE_A ta with (nolock)
LEFT OUTER JOIN
CTE_C tc with (nolock) ON ta.a1 = tc.a1 and ta.b1 = tc.b1 and ta.c1 = tc.c1
LEFT OUTER JOIN
othertable bs with (nolock) ON usd_bs.c = s.c
AND (A1 BETWEEN bs.a1 AND bs.a1)
AND bs.c1 = 1
(nolock)
hints have no effect on DML queries (INSERT
,UPDATE
,DELETE
andMERGE
). So you should probably remove them from any SELECT query you are using for comparison. – RBarryYoung