Deleting multiple rows from an SQL Table

[Robert Brown]

Hi All.

I have a routine that checks a SQL Table for all records 3 months
prior to a predetermined date, then I insert them into an Archive DB
then delete those records from the original table.

When I do a "select" for the records, I load them into a dataset, use
an "insert" statement to insert the info into the second table (by a
for next loop and using executenonquery , then a "delete" statement to
remove them.

The way I delete them is "loop" through the rows of the dataset,
create an SQL statement then use ExecuteNonQuery (see code below).

Surely, this is NOT the way to do it as most times, the record count
is around 70000-10000, and it seems to take quiet a while to do (maybe
about 3 records a second).

Could someone advise the most appropriate and speedy way to delete the
items. Remember the records are in a dataset, and I have read
somewhere that you can update/Delete/Insert into SQL using the
Dataset, but not sure of the command.

Any help would be much appreciated.

Thanks,
Robert

<---------------------- Code ----------------------->
Dim iloop as integer
Dim dsorderItems as new dataset

For iloop = 0 To dsOrderItems.Tables("t1").Rows.Count - 1
sSql = "delete tbl_OrderItems where OrderNum = '" &
dsOrderItems.Tables("t1").Rows(iloop).Item("OrderNum") & "'"

updCommand = New SqlCommand(sSql, SQLConn)
updCommand.Connection.Open()
updCommand.ExecuteNonQuery()
updCommand.Connection.Close()
Next

 

[2]

The bit where you say you read you could do it with a dataset.... I think
you are referring to the dataadapter... I use sqldataadapter for some
stuff... mainly to drive datagrids... anyway, the dsqldataadapterspurpose is
to handle getting records from the database into a dataset and handling any
updates, inserts and deletes. From what I can see to delete a bunch of
records it just executes multiple delete statements similar to your code...
it does however provide other functions and validations that I am not too
clear on also so it does offer advantages apperntly...

Using a dataset allows you to do the operation on a local copy of the data
and then update the server... which is different to performing the updates
directly on the database server itself.

If you are using SQL Server then fire up the SQL Profiler tool... it'll let
you see every SQL command/stored proc being executed on the server... very
handy for seeing what the sqldataadapter does behing the scenes....

Cheers

 

[Cor]

Hi Robert,

I think that I when I was using the dataadapter I think I would do it like
this.

(I think I would not do that for this, because I think that I would just use
the datareader and a commandnonquery with a SQL delete, but this is your
choose)

You can use a dataview, first set the sortorder and than a rowfilter for the
dates.
(Use the sort because that we have seen in this newsgroup goes faster)

Than you can itterate through the dataview just as
\\\\
Dim dv As New DataView(ds.Tables(0))
For i As Integer = dv.Count - 1 To 0 Step -1
dv(i).Delete()
Next
////
After this you can do a SQLdataadapter.update
You have to make for this an SQL deletecommand, or just use a cmdbuilder.

(You can of course also do that SQL non query delete in that dataviewloop.
Than I think it is better not to do the delete in the dataset but just
reread (fill) it again after that all deletes are done.)

I hope this helps?

Cor

 

[Juan Romero]

Robert,

My dear friend, the answer is right in front of your eyes....

Why execute a query to delete every single record when you can execute a
query to delete ALL Records older than 3 months?, for example:
DELETE FROM Table1 WHERE YourFieldDate <='00/00/00'

To begin with, why go through all the records generating insert statements
when you could move them all in one shot with a single query?, for example:

select * into Table2 from Table1 WHERE YourFieldDate <= '00/00/00'

Good luck,

Juan.

 

[Cor]

Hi Juan,

That Robert wrote he first want to archive them.

But I agree with you that I would read them with a same where clause as
delete them, insert them in the archive and than delete them as you said.

But he said he wanted to use a dataset for that.

(I have given with this also the next answer)



Cor

 

[Robert Brown]

Thanks to all that replied..

Juan and Cor have given me some real insight into how to perform the
operations. I was only putting it into a dataset because I thought
that might be the fastest, but I knew there was a better way.

I have tried out Juan's code and it certainly kills the timing of my
code.

Thank you all for the fast replies once again..

Robert