drop INDEX [dpIX_01] ON [dbo].[A] ; drop INDEX [dpIX_02] ON [dbo].[A];
INSERT INTO ReportStage..A --00:00:25 select DISTINCT ResumeHeaderKey, CandidateKey, positionKey, IsDisabled, GenderKey, CityKey FROM Mailing.dbo.Tmp_AD_Mailing WITH (NOLOCK) WHERE PozisyonVarMı = 1
go -- 00:00:31 CREATE NONCLUSTERED INDEX [dpIX_02] ON [dbo].[A] ([positionKey],[IsDisabled],[CityKey]) INCLUDE ([ResumeHeaderKey],[CandidateKey],[GenderKey]) WITH (DATA_COMPRESSION = PAGE) go
CREATE NONCLUSTERED INDEX [dpIX_01]ON [dbo].[A] ([positionKey],[IsDisabled],[GenderKey],[CityKey]) INCLUDE ([ResumeHeaderKey],[CandidateKey]) WITH (DATA_COMPRESSION = PAGE) go
INSERT INTO ReportStage..EF -- 00:00:01 select DISTINCT CandidateKey, FirmKey FROM KNET_BI_DW.dbo.EmbargoFirmCandidateDim WITH(NOLOCK) WHERE FirmKey IS NULL GO
set IDENTITY_INSERT ReportStage..J ON -- 00:00:01 INSERT INTO ReportStage..J (JobHeaderKey, IsDisabled, GenderKey, PositionKey, FirmKey) select DISTINCT JobHeaderKey, IsDisabled, GenderKey, PositionKey, FirmKey FROM KNET_BI_DW.dbo.JobHeaderDim (nolock) WHERE IsActive = 1 AND IsAddKariyernet = 1 AND FirmNameHiddenKey<>2 AND ClosedDateKey > CONVERT(INT, (CONVERT(VARCHAR(24), DATEADD(D, 4, GETDATE()), 112))) set IDENTITY_INSERT ReportStage..J OFF
GO INSERT INTO ReportStage..pb select DISTINCT PozisyonKey1, PozisyonKey2 from KNET_BI_Reports.dbo.[PB_PozisyonBenzerligi] (nolock) WHERE Seviye =1 GO --select DISTINCT ResumeHeaderKey --INTO ReportStage..B --from Mailing.dbo.Tmp_AD_Mailing (nolock)
drop INDEX [dpIX_01] ON [dbo].[JC]; DROP INDEX [dpIX_03]ON [dbo].[JC] ;
INSERT INTO ReportStage..JC -- 00:01:00 select DISTINCT JobHeaderKey, CityKey, (CASE WHEN CityKey IN (34,82) THEN 0 ELSE CityKey END) as CityKeyJoin from KNET_BI_DW.dbo.JobCityDim (nolock)
GO CREATE NONCLUSTERED INDEX [dpIX_01] ON [dbo].[JC] ([JobHeaderKey]) INCLUDE ([CityKeyJoin]) WITH (DATA_COMPRESSION = PAGE) GO
CREATE NONCLUSTERED INDEX [dpIX_03]ON [dbo].[JC] ([CityKeyJoin]) INCLUDE ([JobHeaderKey]) WITH (DATA_COMPRESSION = PAGE) GO
DROP TABLE sonuc GO
SELECT * INTO sonuc FROM ( SELECT X.CandidateKey, X.ResumeHeaderKey, X.JobHeaderKey, RowNum = ROW_NUMBER() OVER (PARTITION BY X.ResumeHeaderKey ORDER BY NEWID() ) FROM ( SELECT DISTINCT A.CandidateKey, A.ResumeHeaderKey, J.JobHeaderKey FROM A WITH (NOLOCK) JOIN pb on pb.PozisyonKey1 = A.positionKey JOIN J WITH (NOLOCK) ON pb.PozisyonKey2 = J.PositionKey AND (J.GenderKey IS NULL OR J.GenderKey = A.GenderKey) and A.IsDisabled=j.IsDisabled JOIN JC WITH (NOLOCK) ON J.JobHeaderKey = JC.JobHeaderKey AND JC.CityKeyJoin = A.CityKey LEFT JOIN EF WITH (NOLOCK) ON A.CandidateKey = EF.CandidateKey AND J.FirmKey = EF.FirmKey ) X LEFT JOIN KNET_BI_DWFact.dbo.ApplicationTransactionFact atf WITH(NOLOCK) ON X.ResumeHeaderKey=atf.ResumeHeaderKey AND X.JobHeaderKey=atf.JobHeaderKey AND X.CandidateKey=atf.CandidateKey WHERE atf.ApplicationTransactionKey IS NULL AND X.JobHeaderKey NOT IN ( SELECT DISTINCT h.JobHeaderKey FROM Mailing.dbo.AD_Mailing_History h WITH (NOLOCK) WHERE X.ResumeHeaderKey = H.ResumeHeaderKey AND h.Date >= CAST(GETDATE() - 7 AS DATE)) )ss WHERE ss.RowNum <=3 option (recompile, maxdop 0)
