Removing rows from very large table with indices

Hello,
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..

Removing records

Hi, i'm not so good in Sql so can somebody help me.
Let say i have 10 records. Each records have a id
1, bmw
2, peugeot
3, ford
5, citrone
6, mazda
7, volvo
8, renault
9, chrysler
10, alfa
Now i receive a txt with the id who are still valid (in stock) and have to
remove the cars sold.
Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
Of course i can use Recordset and check but i wonder if i can do that i one
sql statement ?
GL.DELETE FROM tablename WHERE ID NOT IN (2,3,4,6,7,9,10)
HTH. Ryan
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
> one sql statement ?
> GL.
>
>
>
>|||Hi Gerard,
BEGIN TRAN -- Just in case!
DELETE yourtable
WHERE id NOT IN ( SELECT id FROM yourtxtfile_as_a_table )
Now check...
SELECT *
FROM yourtable
If it worked...
COMMIT TRAN
otherwise...
ROLLBACK
Note, this relies on the id's relating to the same entity between
databases - can you gaurentee that?
You can use DTS to load the textfile into SQL Server, or if you just want to
delete by id then...
DELETE yourtable WHERE id IN ( ones to remove seperated by commas )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
> one sql statement ?
> GL.
>
>
>
>|||Are you saying that you want to delete records for cars with ids 1, 5, and
8?
A simple in clause will take care of this...
Delete from MyTable
where CarID in (1,5,8)
However, if your application is going to be doing this on a regular basis,
with a different list each time, you are probably better off doing the
database updates one at a time. The performance will not be quite as good,
but the code will be much simpler to maintain. With the example that you
give here, the performance difference would be negligible unless you are
performing thousands and thousands of updates.
Note, if what I posted is what you are looking for, then you probably want
to find a good beginner's SQL site and pick up some tips there (I would
reccomend one, but I'm afraid I don't know any off hand.). It will prove
much more useful than any newsgroup, at least until you get to some more
complicated problems.
Best of luck.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:mEOyf.118557$mB2.6107192@.phobos.telenet-ops.be...
> Hi, i'm not so good in Sql so can somebody help me.
> Let say i have 10 records. Each records have a id
> 1, bmw
> 2, peugeot
> 3, ford
> 5, citrone
> 6, mazda
> 7, volvo
> 8, renault
> 9, chrysler
> 10, alfa
> Now i receive a txt with the id who are still valid (in stock) and have to
> remove the cars sold.
> Lets say: 2,3,4,6,7,9,10 are still in stock, 1,5 and 8 are sold.
> Of course i can use Recordset and check but i wonder if i can do that i
one
> sql statement ?
> GL.
>
>
>
>|||Thank you all, i didn't know it was so easy to do. Time to learn sql
profoundly. Thanks. GL|||You might also want to look up the codes used for automobile companies
and product lines. They are alphabetic and you can quickly learn them
by sight.
ALWAYS research your customer's industry first.

removing duplicated records

Hi,

Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.

I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.

Please advise,

Thanks,
MiraJwhy dont u post the DDL of table,some describtion abt table|||maybe you could use group by and use max() function to exclude duplicates

select col1, col2, max(text_col3), max(text_col4)...
from t
group by col1, col2...|||why dont u post the DDL of table,some describtion abt table

Table :

create table test(col1 int, col2 int, col3 text, col4 image)|||it is quiet difficult to do, there are some options,
insert all records into temp table ,add unique column before transfering data to temp table

1.If data values are less
than 8000 characters, you can cast the values to VARCHAR(8000) and use DISTINCT or
GROUP by to get the unique row.


If data values are greater than
8000
characters you may have to use a cursor & loop through
the rows & delete the
rows. In some cases you may have to use
an identity column or a number table
( with a cross join
operation ) to easily distingush one row from another.
----------
This link may help u http://www.windowsitpro.com/SQLServer/Article/ArticleID/23234/23234.html

Removing duplicate Records

I have a table that holds the following

1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM
1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM

for each 7530568 ordernumber there should only be one OESCHD status.

This is the query I'm using to insert the data sent to me.

INSERT INTO ORDER_EVENTS
SELECT d.division as division,
dt.orderNum as orderNum,
dt.poNum as poNum,
dt.statusCode as statusCode,
dt.statusChangeDate as statusChangeDate
FROM dt_Order_Events dt INNER JOIN
division d ON dt.division = d.divisionShort INNER JOIN
status s ON s.division = d.division AND s.statCode = dt.statusCode
WHERE directive <> 'C' AND
dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)

This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.

I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.

Any help would be apreciated.

look here:

http://www.sql-server-performance.com/dv_delete_duplicates.asp

you will get your answers there.

tomer

Removing certain records of a SQL Server table

Hello all,

A little question regarding SQL Server DB's.

I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).

How can I proceed?

I am fairly new with SQL server... Please help!

Thanks,

Eric
:(Set up a cascading delete relationship between your invoices table and your invoice lines table. Then run this statement:

delete
from Invoices
where InvoiceDate < dateadd(months, -48, getdate())

Look up the dateadd function in Books Online to verify the syntax above.|||Skunked again.