May 27, 2008 at 8:42 am
Hi All
I am using SQL 2005(64Bit), i have a job that runs each day to transfer data from one Table A to Table B, and then delete it from Table A, as days go by the number of rows gets increased, this causes more time to delete from table A, it takes me 5 minutes to delete 400K rows, which is not acceptable, i am just using a simple delete statement.
Since this is a production environemnt, this deletion causes table block and i cant access the table during this time, i can delete it in small chunks with a while loop, is there any other way to do this in 2005, which consumes less resources and time??
Please advise me on this
Thanks in Advance
May 27, 2008 at 10:53 am
Would be easier if we could see the SQL code and table definitions (with indexes).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 11:05 am
Thanks for the response
This is the code, i have inserted the records into a temp table from tableA and then insert this record into TableB, once inserted into Table B, i delete from TableA with the same userid from temp table (which is initally from table A)
Both the tables are indexed on Userid
DELETE a
FROM Table1 a
INNER JOIN #Temp b
ON a.Userid = b.Userid
Cheers
May 27, 2008 at 11:19 am
Well, your not giving us much of the context there, but try doing a DELETE TOP (10000) instead, with a WHILE loop around it that loops until @@ROWCOUNT=0.
Of course, I don't think that that wil actually work, because I suspect that part of the outer context that you withheld from us includes a transaction, which will hold locks on everything until it is committed anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 11:30 am
Thanks Young
This is the exact Query i use to delete the table contents expect the table name,
I create a primary key index on the fly for the temp table as below on userid
alter table #Temp add constraint
PK_Msgid Primary key clustered (userid ) on [Primary]
Iam thinking of using while loop, but as you said that will block the table as well,
Is there any other way to process this with minimal or no blockage.
What shall i do in order to avoid table block, please suggest
cheers
May 27, 2008 at 11:47 am
Show us the whole procedure. Not just the DELETE query. The other parts of the procedure are important to this also because they are all part of the transaction.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 11:53 am
just out of curiousity - what's the clustered index on Table1? Is is unique? A WHILE loop might help, but running the deletes based on the clustered index of the table you're deleting from would likely be a lot faster, especially if you can "walk" that clustered index as part of your WHILE loop
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 27, 2008 at 12:24 pm
Matt makes a great point. We really need the DDL's also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 2:14 pm
Do this table have any foreign key references or constraints refering it?
Manu
May 27, 2008 at 5:06 pm
... and let's not forget what everyone else forgets... Delete Triggers and Indexed Views... they will certainly take a toll on performance, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2008 at 3:30 am
Thanks Guys
There are no Foregin keys for this table, and i am using Primary key which is used on both the tables, the DDL for the whole procedure is as below
SELECT a.Mid, a.Ph, a.msg, a.dd, a.Rt, a.st,
a.MDa, a.BI, isNull(a.BO, 0) as BO, a.NMs, a.r, a.sr, a.kd, a.Re, a.Or, a.Ims, Yy = datepart(Yy,a.dd)-2000, Mm = datepart(Mm,a.dd),
Dd = datepart(Dd,a.dd), Hh = datepart(Hh,a.dd), KeyID into #Temp
FROM OQa (nolock)
WHERE ( (a.dd < GETDATE() - 0.1) AND (a.st IN (4,7)) ) OR
( (a.dd < GETDATE() - 0.1) AND (a.st BETWEEN 250 AND 255) ) OR
( (a.dd < GETDATE() - 0.25) AND (a.st BETWEEN 4 AND 29) ) OR
( (a.dd < GETDATE() - 0.25) AND (a.st BETWEEN 34 AND 61) ) OR
( (a.dd < GETDATE() - 2) AND (a.st in (3)) )
INSERT INTO OQa2
(Mid, Ph, msg, dd, Rt, st,
MDa, BI, BO, NMs, r, sr, kd, Re, Or, Ims, Yy, Mm, Dd, Hh, KeyID)
select * from #Temp
alter table #Temp add constraint
PK_Mid Primary key clustered (Mid ) on [Primary]
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DELETE a
FROM OQa
INNER JOIN #Temp b
ON a.Mid = b.Mid
alter table #Temp drop constraint PK_Mid
Drop table #Temp
Cheers
May 28, 2008 at 3:50 am
May you show us Actual Execution Plan of DELETE statement?
And table definitions of OQa with all its indexes too
May 28, 2008 at 3:55 am
Hi
I dont have a execution plan , but the table index structure is as below
IX_OQa_Binonclustered, stats no recompute located on PRIMARYBi
IX_OQa_Blo_Binonclustered, stats no recompute located on PRIMARYBlo, Bi
IX_OQa_ddnonclustered, stats no recompute located on PRIMARYdd
IX_OQa_iMidnonclustered, stats no recompute located on PRIMARYiMid
IX_OQa_Phnonclustered, stats no recompute located on PRIMARYPh
IX_OQa_Rtnonclustered, stats no recompute located on PRIMARYRt
IX_OQa_Stnonclustered, stats no recompute located on PRIMARYSt
IX_OQa_Stanonclustered, stats no recompute located on PRIMARYSta
IX_OQa_StaRtnonclustered, stats no recompute located on PRIMARYSta, Rt
PK_OQaclustered, unique, primary key, stats no recompute located on PRIMARYMid
Cheers
May 28, 2008 at 4:13 am
CrazyMan (5/28/2008)
I dont have a execution plan , but the table index structure is as below
ok
Let try from another side.
How much time is this query being executed?
SELECT a.Mid, a.Ph, a.msg, a.dd, a.Rt, a.st,
a.MDa, a.BI, isNull(a.BO, 0) as BO, a.NMs, a.r, a.sr, a.kd, a.Re, a.Or, a.Ims, Yy = datepart(Yy,a.dd)-2000, Mm = datepart(Mm,a.dd),
Dd = datepart(Dd,a.dd), Hh = datepart(Hh,a.dd), KeyID into #Temp
FROM OQa (nolock)
WHERE ( (a.dd < GETDATE() - 0.1) AND (a.st IN (4,7)) ) OR
( (a.dd < GETDATE() - 0.1) AND (a.st BETWEEN 250 AND 255) ) OR
( (a.dd < GETDATE() - 0.25) AND (a.st BETWEEN 4 AND 29) ) OR
( (a.dd < GETDATE() - 0.25) AND (a.st BETWEEN 34 AND 61) ) OR
( (a.dd < GETDATE() - 2) AND (a.st in (3))
May 28, 2008 at 4:17 am
Just once a day early morning to move data from one table to other table,
Cheers
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply