Take very much time at the time of deletion by query

Question

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.

Iftikar Hussain · Answer

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

Iftikar Hussain · Answer

in that case you delete records in batch Regards Iftikar

Ravi Shekhar · Answer

Yeah

Iftikar Hussain · Answer

any child table is refered to this wms_loose_transaction_Logs table? Regards Iftikar

Ravi Shekhar · Answer

with referential we cant do truncate sir..

Iftikar Hussain · Answer

what exception you are getting? Regards Iftikar

Ravi Shekhar · Answer

But sir truncate gives exception.if there is any refrential integrity exist with the table. in this situation???

Iftikar Hussain · Answer

you can use this process since you have lakh of records. Regards Iftikar

Ravi Shekhar · Answer

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???

Iftikar Hussain · Answer

Please refer the below link for possible option http://stackoverflow.com/questions/478327/multi-rows-deletion-from-table-in-sql-server Regards Iftikar

Ravi Shekhar · Answer

WHILE Exists(Select 1 From wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) BEGIN Delete Top (10000) From wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) END It takes 6 minuts to delete 10k records

Iftikar Hussain · Answer

check like this select COUNT(*) from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and transaction_on Regards Iftikar

Ravi Shekhar · Answer

select COUNT(*) from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and CONVERT(datetimeCONVERT(varchartransaction_on101)) It returns 2 lacs row select COUNT(*) from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) But It returns 0 rows y???

Ravi Shekhar · Answer

select COUNT(*) from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and CONVERT(datetimeCONVERT(varchartransaction_on101)) It returns 2 lacs row select COUNT(*) from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) But It returns 0 y???

Iftikar Hussain · Answer

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) BEGIN Delete Top (10000) From wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) END Regards Iftikar

Ravi Shekhar · Answer

15 second for 2 lacs record please find the attachment.

Iftikar Hussain · Answer

can see how much time takes for just selecting the records with the same condition Regards Iftikar

Ravi Shekhar · Answer

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.

Iftikar Hussain · Answer

I think you need to clear your log size of your DB. Regards Iftikar

Ravi Shekhar · Answer

3.5 lacs rows in this table.

Iftikar Hussain · Answer

what is the total number of records in this table wms_loose_transaction select count(*) from wms_loose_transaction Regards Iftikar

Ravi Shekhar · Answer

Same time taken by your last query...

Iftikar Hussain · Answer

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) Regards Iftikar

Ravi Shekhar · Answer

Dear Intikar I am not getting your point.

Kunal Vaishya · Answer

You can use also Partitioning for this for large database. Refer link http://msdn.microsoft.com/en-us/library/ms188232(v=sql.105).aspx

Iftikar Hussain · Answer

is the allocated log size or available? Regards Iftikar

Ravi Shekhar · Answer

Hi Iftikar  Log file size is 4.41 gb and mdf size is 11.3 gb.

Iftikar Hussain · Answer

try this delete from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and (Year(transaction_on) also check your sql log file size Regards Iftikar

Ravi Shekhar · Answer

at the beginning it returned a row which suggested me to crate an index. after that i created index. Now it suggest nothing

Iftikar Hussain · Answer

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

Ravi Shekhar · Answer

No sir It still takes same time period. Please give me another Solution.

Kunal Vaishya · Answer

try this query may be help full Delete from wms_loose_transaction Where [Status]='DSP' and (Isnull(StatusTemp1'') '' and CONVERT(datetimeCONVERT(varchartransaction_on101))

Iftikar Hussain · Answer

try this delete from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and transaction_on Regards Iftikar

Ravi Shekhar · Answer

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.

Ravi Shekhar · Answer

at the time of creating non clustered index what is mean by INCLUDE.

Iftikar Hussain · Answer

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

Ravi Shekhar · Answer

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(datetimeCONVERT(varchartransaction_on101)) ============================================================================================= delete from wms_loose_transaction Where Status='DSP' and StatusTemp1 is not null and CONVERT(datetimeCONVERT(varchartransaction_on101))

Iftikar Hussain · Answer

Hi Please provide your sql query and table schema. Regards Iftikar