Take very much time at the time of deletion by query

Ravi Shekhar

13y1.8k

Hi all,

I have a table which contain 60 colums and appropriate clustered and non clustered index and 50 lacs data. Now i want to move some useless data into any other table. when i insert in other table it takes 5 to 7 minuts per 10 lacs data. now when in delete this data from main table it takes around 40 or more than 40 minuts. Please advice me how to reduce this.
urgent for me.

I am deleting data according to date means datewise.

Please help.

38 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
in that case you delete records in batch

Regards,
Iftikar
0
Ravi Shekhar13y ago
Yeah
0
Iftikar Hussain13y ago
any child table is refered to this wms_loose_transaction_Logs table?

Regards,
Iftikar
0
Ravi Shekhar13y ago
with referential we cant do truncate sir..
0
Iftikar Hussain13y ago
what exception you are getting?

Regards,
Iftikar
0
Ravi Shekhar13y ago
But sir truncate gives exception.if there is any refrential integrity exist with the table.
in this situation???
0
Iftikar Hussain13y ago
you can use this process, since you have lakh of records.

Regards,
Iftikar
0
Ravi Shekhar13y ago
Hi,

i got a solution.

1. First of all I created a table tempPUT from wms_loose_transaction

select * into tempPUT from wms_loose_transaction
Where Status='PUT'

2. After that I created a table tempDSP from wms_loose_transaction

select * into tempDSP from wms_loose_transaction
Where Status='DSP'

3.truncate table wms_loose_transaction

4.insert into wms_loose_transaction select * from tempPUT

I have done above task within 5 minut. Please suggest me following process is right or
any error occur in future or any accuracy problem will happen???
0
Iftikar Hussain13y ago
Please refer the below link for possible option

http://stackoverflow.com/questions/478327/multi-rows-deletion-from-table-in-sql-server

Regards,
Iftikar
0
Ravi Shekhar13y ago
WHILE Exists(Select 1 From wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15))
BEGIN
Delete Top (10000)
From wms_loose_transaction
Where Status='DSP' and StatusTemp1 is not null and
(Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15)
END

It takes 6 minuts to delete 10k records
0
Iftikar Hussain13y ago
check like this

select COUNT(*) from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and transaction_on<='06/15/2013'

Regards,
Iftikar
0
Ravi Shekhar13y ago
select COUNT(*) from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and CONVERT(datetime,CONVERT(varchar,transaction_on,101))<=CONVERT(datetime,'06/15/2013')

It returns 2 lacs row

select COUNT(*) from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15 )

But It returns 0 rows

y???

0
Ravi Shekhar13y ago
select COUNT(*) from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and CONVERT(datetime,CONVERT(varchar,transaction_on,101))<=CONVERT(datetime,'06/15/2013')

It returns 2 lacs row

select COUNT(*) from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15 )

But It returns 0

y???

0
Iftikar Hussain13y ago
try deleting the records in batch

WHILE Exists(Select 1 From wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15))
BEGIN
Delete Top (10000)
From wms_loose_transaction
Where Status='DSP' and StatusTemp1 is not null and
(Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15)
END

Regards,
Iftikar
0
Ravi Shekhar13y ago
15 second for 2 lacs record

please find the attachment.
0
Iftikar Hussain13y ago
can see how much time takes for just selecting the records with the same condition

Regards,
Iftikar
0
Ravi Shekhar13y ago
Sir ,

I already shrinked my log file but. still taking time. I am really in trouble.

I have to control 5 databases at 5 different location.and they all having same problem.
Please help me anyone.
0
Iftikar Hussain13y ago
I think you need to clear your log size of your DB.

Regards,
Iftikar
0
Ravi Shekhar13y ago
3.5 lacs rows in this table.
0
Iftikar Hussain13y ago
what is the total number of records in this table wms_loose_transaction

select count(*) from wms_loose_transaction

Regards,
Iftikar
0
Ravi Shekhar13y ago
Same time taken by your last query...
0
Iftikar Hussain13y ago
I was refering that is your log size is filled up.Since it is also make impact the performance

have you tried this

delete from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15 )

Regards,
Iftikar
0
Ravi Shekhar13y ago
Dear Intikar, I am not getting your point.
0
Kunal Vaishya13y ago
You can use also Partitioning for this for large database.

Refer link
http://msdn.microsoft.com/en-us/library/ms188232(v=sql.105).aspx
0
Iftikar Hussain13y ago
is the allocated log size or available?

Regards,
Iftikar
0
Ravi Shekhar13y ago
Hi Iftikar,

Log file size is 4.41 gb and mdf size is 11.3 gb.
0
Iftikar Hussain13y ago
try this

delete from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and (Year(transaction_on) <=2013 and MONTH(transaction_on)<=06 and Day(transaction_on)<=15 )

also check your sql log file size

Regards,
Iftikar
0
Ravi Shekhar13y ago
at the beginning it returned a row which suggested me to crate an index. after that i created index. Now it suggest nothing
0
Iftikar Hussain13y ago
Have you created the missing index for this table? can you give me outout for the below query
```
SELECT * FROM sys.dm_db_missing_index_details
```
Regards,
Iftikar
0
Ravi Shekhar13y ago
No sir It still takes same time period. Please give me another Solution.
0
Kunal Vaishya13y ago
try this query may be help full

Delete from wms_loose_transaction
Where [Status]='DSP'
and (Isnull(StatusTemp1,'') <> ''
and CONVERT(datetime,CONVERT(varchar,transaction_on,101))<=CONVERT(datetime,'06/15/2013')
0
Iftikar Hussain13y ago
try this

delete from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and transaction_on<='06/15/2013'

Regards,
Iftikar
0

Ravi Shekhar13y ago

2.50 lacs record i am deleting after creating an index given by
this query....

SELECT * FROM sys.dm_db_missing_index_details

but it still takes time (arount 35 minuts) for delete . 

Please give me any solution.

Iftikar Hussain13y ago
If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.

The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree

Regards,
Iftikar
0
Ravi Shekhar13y ago
at the time of creating non clustered index what is mean by INCLUDE.
0
Iftikar Hussain13y ago
Please run the below query in your sql to find the missing index and provide the output
```
SELECT * FROM sys.dm_db_missing_index_details
```
Regards,
Iftikar
0
Ravi Shekhar13y ago
CREATE TABLE [dbo].[wms_loose_transaction_Logs](
[OuterBoxNo] [varchar](25) NOT NULL,
[MonoboxNo] [varchar](23) NOT NULL,
[GateInNo] [varchar](10) NULL,
[concept] [varchar](50) NULL,
[category] [varchar](50) NULL,
[Status] [varchar](3) NULL,
[StatusTemp] [varchar](50) NULL,
[StatusTemp1] [varchar](50) NULL,
[aielse_code] [varchar](50) NULL,
[slot_code] [varchar](50) NULL,
[InvoiceNo] [varchar](15) NULL,
[lt_attribute2] [varchar](50) NULL,
[lt_attribute3] [varchar](50) NULL,
[lt_attribute4] [varchar](50) NULL,
[transaction_on] [datetime] NULL,
[transaction_by] [varchar](50) NULL,
[OKStatus] [varchar](50) NULL,
[OLDOUTERBOX] [varchar](50) NULL,
[REPACKSERIAL] [varchar](50) NULL,
[PRINTSERIALNO] [varchar](50) NULL,
[REPACKON] [datetime] NULL,
[REPACKBY] [varchar](50) NULL,
[putaway_on] [datetime] NULL,
[putaway_by] [varchar](50) NULL,
[picking_on] [datetime] NULL,
[picking_by] [varchar](50) NULL,
[loaded_on] [datetime] NULL,
[loaded_by] [varchar](50) NULL,
[steging_on] [datetime] NULL,
[steging_by] [varchar](50) NULL,
[grn_on] [datetime] NULL,
[grn_by] [varchar](50) NULL,
[letdown_on] [datetime] NULL,
[letdown_by] [varchar](50) NULL,
[hold_on] [datetime] NULL,
[hold_by] [varchar](50) NULL,
[StatusTemp2] [char](1) NULL,
[TempUserID] [varchar](50) NULL,
[cyclecountdone] [varchar](50) NULL,
[cyclecountdoneon] [datetime] NULL,
[cyclecountdoneby] [varchar](50) NULL,
[GRNNO] [varchar](10) NULL,
[VARIENCEFLAG] [varchar](15) NULL,
[UPLOADSTATUS] [varchar](10) NULL,
[OrderNo] [varchar](15) NULL,
[Capacity] [int] NULL,
[ArtCapacity] [int] NULL,
CONSTRAINT [PK_wms_loose_transaction_logs] PRIMARY KEY CLUSTERED
(
[OuterBoxNo] ASC,
[MonoboxNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

===================================================================================

Insert into wms_loose_transaction_logs
Select * from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null and CONVERT(datetime,CONVERT(varchar,transaction_on,101))<=CONVERT(datetime,'06/15/2013')

=============================================================================================

delete from wms_loose_transaction
Where Status='DSP'
and StatusTemp1 is not null
and CONVERT(datetime,CONVERT(varchar,transaction_on,101))<=CONVERT(datetime,'06/15/2013')
0
Iftikar Hussain13y ago
Hi,
Please provide your sql query and table schema.

Regards,
Iftikar
0