Deleting 100,000 rows

1 Apr 2005 3:27 PM

Bob Day

I need to delete ALL rows in a dataset and currently use the method in the
code snippet below, which works fine. However, there can be 100,000 rows,
which makes the FILL, DR.Delete and UPDATE take a very long time.

Is there a way to simply delete the entire dataset and then update that to
the datasource more effectinely?

Thanks!
Bob Day

' FILL from datasource to dataset (not shown)
' Used to iterate through Details Row Collection

    Dim DR As DS_Methods_Base.Methods_DetailsRow

' Iterate through the rows colleciton, Delete each rows in DataSet

    For Each DR In gmCD.DS_Methods.Methods_Details.Rows

        DR.Delete()

    Next

' UPDATE from dataset to datasource (not shown)

4 Apr 2005 7:26 AM

Cor Ligthert

Bob,

Deleteing from a datatable is extremely slow in this version of Net (It
thought that it has the attention).

Therefore clearing the datatable. "Datatable.Clear" and filling again will
probably giving you the best results.

I never did that, however you can in my opinion of course as well in this
case dispose your dataset and just create a new one (mydataset = new
dataset) and set that again to your datasource (be aware of that because in
this case the datasource will have a null reference when you don't do that).

I hope this helps,

Cor

4 Apr 2005 11:17 AM

C-Services Holland b.v.

Bob Day wrote:
Show quoteHide quote

> I need to delete ALL rows in a dataset and currently use the method in the
> code snippet below, which works fine. However, there can be 100,000 rows,
> which makes the FILL, DR.Delete and UPDATE take a very long time.
>
> Is there a way to simply delete the entire dataset and then update that to
> the datasource more effectinely?
>
> Thanks!
> Bob Day
>
> ' FILL from datasource to dataset (not shown)
> ' Used to iterate through Details Row Collection
>
>     Dim DR As DS_Methods_Base.Methods_DetailsRow
>
> ' Iterate through the rows colleciton, Delete each rows in DataSet
>
>     For Each DR In gmCD.DS_Methods.Methods_Details.Rows
>
>         DR.Delete()
>
>     Next
>
> ' UPDATE from dataset to datasource (not shown)
>
>

Are you getting the dataset via a SQL command? If so, just use SQL to
delete the entries you want. i.e.:

sqlCommand="delete from mytable"
This will erase all rows from mytable. Ofcourse you can add a WHERE
statement to further narrow the set you wish to delete.

--
Rinze van Huizen
C-Services Holland b.v.

5 Apr 2005 3:43 AM

Bob Day

Yes, the SQL approach is best.

Thanks!

Bob Day
-
Show quoteHide quote

"Bob Day" <Bob***@TouchTalk.net> wrote in message
news:efDzU6MOFHA.3560@TK2MSFTNGP14.phx.gbl...
>I need to delete ALL rows in a dataset and currently use the method in the
>code snippet below, which works fine. However, there can be 100,000 rows,
>which makes the FILL, DR.Delete and UPDATE take a very long time.
>
> Is there a way to simply delete the entire dataset and then update that to
> the datasource more effectinely?
>
> Thanks!
> Bob Day
>
> ' FILL from datasource to dataset (not shown)
> ' Used to iterate through Details Row Collection
>
>    Dim DR As DS_Methods_Base.Methods_DetailsRow
>
> ' Iterate through the rows colleciton, Delete each rows in DataSet
>
>    For Each DR In gmCD.DS_Methods.Methods_Details.Rows
>
>        DR.Delete()
>
>    Next
>
> ' UPDATE from dataset to datasource (not shown)
>