Efficient local data-handling

[Mantorok]

Hi all

I have a DataTable containing around 25000 rows. For each row I want to
query the entire DT to find out if potentially duplicate items exist, problem
is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'", "''"),
(row["cl_fname"] as string).Replace("'", "''")));

My question - is there a quicker way of doing this? BTW, the reason why I'm
doing this locally is because the DT is made up of data from 7 different
sources.

Thanks
Kev

[Nicholas Paldino [.NET/C# MVP]]

Mantorok,

Why not cycle through the rows, and then keep a record of which rows are
duplicates?

I would have a Dictionary<<type of unique identifier>, List<int>. The
<type of unique identifier> would be the type of a field in the row which
would be duplicated among rows. Either this, or some sort of key which
indicates the values in the row that is duplicated (a structure might work
well here since it will generate the same hash code for the same values in
it, whereas the hashcode generated for the DataRow will not).

Then, the list would be a list of row indexes which share that value.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mantorok" <sp******@spam.com> wrote in message
news:cb**************************@news.rmplc.co.uk ...

Hi all

I have a DataTable containing around 25000 rows. For each row I want to
query the entire DT to find out if potentially duplicate items exist,
problem is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'",
"''"),
(row["cl_fname"] as string).Replace("'",
"''")));

My question - is there a quicker way of doing this? BTW, the reason why
I'm doing this locally is because the DT is made up of data from 7
different sources.

Thanks
Kev

[Mark Newmister]

Sort the table based on your key (test) values. While iterating via loop,
see if your current row fields match the fields of the next row.

"Mantorok" <sp******@spam.com> wrote in message
news:cb**************************@news.rmplc.co.uk ...

Hi all

I have a DataTable containing around 25000 rows. For each row I want to
query the entire DT to find out if potentially duplicate items exist,
problem is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'",
"''"),
(row["cl_fname"] as string).Replace("'",
"''")));

My question - is there a quicker way of doing this? BTW, the reason why
I'm doing this locally is because the DT is made up of data from 7
different sources.

Thanks
Kev

[Mantorok]

Hi

How do you sort a DataTable?

Thanks
Kev

Sort the table based on your key (test) values. While iterating via
loop, see if your current row fields match the fields of the next row.

"Mantorok" <sp******@spam.com> wrote in message
news:cb**************************@news.rmplc.co.uk ...

Hi all

I have a DataTable containing around 25000 rows. For each row I want
to query the entire DT to find out if potentially duplicate items
exist, problem is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'",
"''"),
(row["cl_fname"] as string).Replace("'",
"''")));
My question - is there a quicker way of doing this? BTW, the reason
why I'm doing this locally is because the DT is made up of data from
7 different sources.

Thanks
Kev

[Mantorok]

Hi

Although I understand that there are work-arounds, my main gripe is that
I shouldn't really have to, the DataTable provides a Select method which
should handle [almost] anything I throw at it, it's quite dissapointing really.
When I said slow, I mean real slow, as in abnormal, it was looking to take
around 1/2 hour if i'd left it to continue.

As it goes I've managed to speed it up by removing the "ClientId <> " criteria
which sped it up immensly (by about 20x), I've had a quick search on the
MSDN forums and it seems the DataTable does seem to have an issue with the
Select method.

Thanks for the help.

Kev

Mantorok,

Why not cycle through the rows, and then keep a record of which
rows are duplicates?

I would have a Dictionary<<type of unique identifier>, List<int>.
The <type of unique identifier> would be the type of a field in the
row which would be duplicated among rows. Either this, or some sort
of key which indicates the values in the row that is duplicated (a
structure might work well here since it will generate the same hash
code for the same values in it, whereas the hashcode generated for the
DataRow will not).

Then, the list would be a list of row indexes which share that
value.

Hope this helps.

"Mantorok" <sp******@spam.com> wrote in message
news:cb**************************@news.rmplc.co.uk ...

Hi all

I have a DataTable containing around 25000 rows. For each row I want
to query the entire DT to find out if potentially duplicate items
exist, problem is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'",
"''"),
(row["cl_fname"] as string).Replace("'",
"''")));
My question - is there a quicker way of doing this? BTW, the reason
why I'm doing this locally is because the DT is made up of data from
7 different sources.

Thanks
Kev

[Göran Andersson]

You don't. Use the DefaultView of the DataTable, or create a new
DataView, to sort the data.

Mantorok wrote:

Hi

How do you sort a DataTable?

Thanks
Kev

Sort the table based on your key (test) values. While iterating via
loop, see if your current row fields match the fields of the next row.

"Mantorok" <sp******@spam.com> wrote in message
news:cb**************************@news.rmplc.co.uk ...

Hi all

I have a DataTable containing around 25000 rows. For each row I want
to query the entire DT to find out if potentially duplicate items
exist, problem is - this is reeeeeeaaaaal slow.

I'm using the Select method:

DataRow[] found = allClients.Select(
string.Format("ClientId <> '{0}' AND cl_sname = '{1}' AND
cl_fname = '{2}'",
row["ClientId"],
(row["cl_sname"] as string).Replace("'",
"''"),
(row["cl_fname"] as string).Replace("'",
"''")));
My question - is there a quicker way of doing this? BTW, the reason
why I'm doing this locally is because the DT is made up of data from
7 different sources.

Thanks
Kev