I definitely prefer using temp tables here too rather than table variables, which generally do not perform well.
Also, use a clustering index that matches the join, not just always based by default on an identity column (horrible practice!). That should allow a merge join between @SLog and @DA, which should be much more efficient.
declare @SLog table
(
[SLogId] [int] NOT NULL IDENTITY(1,1),
[FK_DAid] [int] NOT NULL, -- this is references to DA UNIQUE CLUSTERED ( FK_DAid, SLogId ) WITH ( FILLFACTOR = 100 ),
[Device] nvarchar NULL,
[Brand] nvarchar NULL,
[SFlag] [int] NULL
)
Generally you'd want to cluster this table first by [ProId], although there could be fragmentation issues with that, so you'd want to review that further. Still, that's the best thing to try for quick performance gains on that delete.
CREATE TABLE [dbo].[Csets](
[CsetID] [int] IDENTITY(1,1) NOT NULL,
[ProId] [int] NOT NULL,
[Cset] nvarchar NULL,
[CsetData] nvarchar NULL,
[CsetChksum] nvarchar NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[StatusFlag] [int] NULL,
CONSTRAINT [PK_Csets] PRIMARY KEY CLUSTERED
(
ProId, CsetID
)WITH ( FILLFACTOR = 98, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Csets] WITH CHECK ADD CONSTRAINT [FK_Csets_ProInfo] FOREIGN KEY([ProId])
REFERENCES [dbo].[ProInfo] ([ProInfoId])
GO
ALTER TABLE [dbo].[Csets] CHECK CONSTRAINT [FK_Csets_ProInfo]
GO
i have an doubt in the below peace of code since "ProId" is primary key in projInfo table and Foreign key in Csets.ProId how come we can make it primary key in the Csets table again ?
Could you please let me know any reason behind this ?
and despite of making fill factor = 98 it is still taking 1 min
CONSTRAINT [PK_Csets] PRIMARY KEY CLUSTERED ( ProId, CsetID
)WITH ( FILLFACTOR = 98, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The FK is pointing to the ProInfo table. There's nothing wrong with the PK column (any of them) being a FK to another table. Happens all the time in relational tables.
As for the 1 minute run time, my guess is that the log file needs to be expanded to log all the deletes. Doing that live is very flow. Try adding enough extra space to the log file ahead of time, before running the delete.
Then the delete should finish very, very quickly. SQL speeds up performance of large deletes by returning to you before all the pages are even freed, it does that in the background after you delete has completed.