Performance Issue

HI all,

i have around 2 lakhs of records in @slogs table i want to delete it all but it taking around 8 to 10 seconds i have wrote an query that is below.

please let me is there any other to write which takes "0" seconds ?

declare @SLog table
(
[SLogId] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY ,
[FK_DAid] [int] NOT NULL, -- this is references to DA
[Device] nvarchar NULL,
[Brand] nvarchar NULL,
[SFlag] [int] NULL
)

declare @DA table(
[DeviceID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[FK_Pid] [int] NULL, --(this is references to table [ProInfo])
[FK_RegionCountryCode] [int] NULL,
[EAK] nvarchar NULL,
[SFlag] [int] NULL
)

declare @ProInfo table
(
[Pid] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[FK_ProId] [int] NULL, -- this references to table project
[PVersion] nvarchar NULL,
[EDBVersion] nvarchar NULL,
[DVersion] [int] NULL,
[DBVersionname] nvarchar NULL,
[SFlag] [int] NULL
)

DELETE FROM @SLog
FROM @SLog S INNER JOIN @DA D ON S.FK_DAid = D.DeviceID
INNER JOIN @ProInfo P ON D.FK_Pid = P.Pid
WHERE P.Pid= 204

Instead of table variables maybe use temp table with indices?

Create table #slogs

1 Like

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
)

1 Like

i have one more issue with regards to the above subject

what if it is an physical table(normal db table) and we are deleting 2 lakhs records.

i have only single delete statement with where clause

delete from [dbo].[Csets] where [FK_ProId] = 123

CREATE TABLE [dbo].[Csets](
[CsetID] [int] IDENTITY(1,1) NOT NULL,
[FK_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
(
[CsetID] ASC
)WITH (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([FK_ProId])
REFERENCES [dbo].[ProInfo] ([ProInfoId])
GO

ALTER TABLE [dbo].[Csets] CHECK CONSTRAINT [FK_[Csets]_[ProInfo]
GO

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
1 Like

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.

1 Like

also check on the Foreign keys as well as if there are any Triggers. If FKs have cascade delete or update, that would explain it as well

Thanks