November 23, 2011 at 5:18 am
All,
I have a different scenario.
create table sec_returns
(
sec_no int,
sec_dt datetime,
ret decimal(18,6) null
)
insert into sec_returns
select 1,'01/Jan/2000',2.23232
union
select 1,'01/Feb/2000',1.34543
union
select 1,'01/Mar/2000',2.45645
union
select 2,'01/Jan/2000',2.23232
union
select 2,'01/Feb/2000',1.34543
union
select 2,'01/Mar/2000',2.45645
The table will have the return from 01/jan/2000 to oct/2011.
If any security's ret column contains only NULL for ALL the period i.e 01/jan/2000 to till date only null in ret column, I have to delete it from the table.
Note the table has 140000000 records. It has a unique clustered index on (sec_no, sec_dt)
karthik
November 23, 2011 at 5:56 am
What is the question here?
November 23, 2011 at 6:27 am
The question of hard vs soft deletes is a business decision, not a technical one.
How to implement either one is a technical question.
Is that what you're asking about?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2011 at 6:37 am
yes.basically i just wanted to delete it from the table.
karthik
November 23, 2011 at 6:44 am
viiki.seth (11/23/2011)
What is the question here?
I still don't know the answer to this.
To Karthik: please post your exact question clearly, without expecting us to guess it based on your scenario.
November 23, 2011 at 6:59 am
I would like to delete the securities from the table where as the returns are only NULL from 01/JAN/2000 to tilldate.
Note all the securties will have the returns from 01/JAN/2000.
karthik
November 23, 2011 at 7:02 am
karthikeyan-444867 (11/23/2011)
I would like to delete the securities from the table where as the returns are only NULL from 01/JAN/2000 to tilldate.Note all the securties will have the returns from 01/JAN/2000.
This is just a scenario without a question.
"So what?" is the phrase that comes to my mind when I read this.
November 23, 2011 at 7:07 am
Use "Where Not Exists (select * from table where column is null)" as part of your where clause in your delete statement. That should give you what you need. You'll have to add in the date range to the Where clause as well, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 28, 2011 at 12:41 am
i have used the below code
delete from sec_returns
where sec_no in ( select sec_no
from (select sec_no, sum(isnull(ret,0)) from sec_returns group by sec_no having sum(isnull(ret,0)) = 0)z
)
But...i think this may lead to performance issue. is there any other way to delete the sec_no where as the security have only null for all the months?
karthik
November 29, 2011 at 2:49 am
Hello,
first of all you should detect the sec_no to be deleted; you should do it with this query,
SELECT sec_no
FROM sec_returns
GROUP BY sec_no
HAVING max(ret) IS NULL
Then you can code your DELETE statement,
WITH X AS (
SELECT sec_no
FROM sec_returns
GROUP BY sec_no
HAVING max(ret) IS NULL
)
DELETE sec_returns
FROM sec_returns S
INNER JOIN X ON S.sec_no = X.sec_no
If you are worried with performance issues... clone your environment and test it. Before doing it you can:
check the amount of rows to be deleted, to guess if you can try to delete them in a single transaction.
check the time needed to retrieve all those rows, to guess the time you need to delete them (and the time you are locking resources).
If you think you can run it in a single transaction, all right. If not you can code a DELETE TOP (10000) (or TOP (nnn), use the number you like) enclose inside a transaction and run it inside a loop.
Francesc
November 29, 2011 at 3:59 am
Using a temp table containing the PK's of the target table looks like the best option to me:
SELECT sec_no
INTO #sec_no_to_delete
FROM sec_returns m
WHERE NOT EXISTS (
SELECT 1
FROM sec_returns
WHERE sec_no = m.sec_no
AND sec_dt BETWEEN '01/jan/2000' AND '01/oct/2011' -- validate & correct if necessary
AND ret IS NOT NULL)
GROUP BY sec_no
CREATE UNIQUE CLUSTERED INDEX [CXsec_no] ON #sec_no_to_delete (sec_no)
-- check the 'filter' table by eye
SELECT TOP 10000 [target].*
FROM #sec_returns [target]
INNER JOIN #sec_no_to_delete [filter] ON [filter].sec_no = [target].sec_no
ORDER BY [target].sec_no
-- run delete
DELETE [target]
FROM #sec_returns [target]
INNER JOIN #sec_no_to_delete [filter] ON [filter].sec_no = [target].sec_no
It's easy to check that the query is working properly, it splits the processing load in two, and offers an easy structured means to batch-delete.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 29, 2011 at 4:56 am
If you're deleting that much data (assuming Chris' Dates are ok), you might be better off just rebuilding the table and only import what you want to keep in there.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply