Performace problem with DataView.RowFilter

[Joe]

Not sure what group this question is better suited for so I sent it to both.

I have a DataTable which contains 1545 rows. I have a method that returns a
DataTable of distinct rows based on one column. This table ends up with 386
rows.

I need to loop through the smaller table and filter a DataView based on each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386 rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}

 

[Sahil Malik]

Yes there is a much better way to do this.

Let me understand your problem first - You have two tables. One contains
distinct values but it was formed out of the first.

Instead of recreating a dataview everytime the user selects a row from the
distinct table, use the relation and use GetChildRows instead. That's MUCH
MUCH faster.

Check this out too ---
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/12/23/38575.aspx


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Joe]

This worked out great as far as performance but the problem I know have it
removing the tables from the DataSet I had to add them to in order for this
to work.

To remove the tables I need to remove the keys as well as the relations.

Yes there is a much better way to do this.

Let me understand your problem first - You have two tables. One contains
distinct values but it was formed out of the first.

Instead of recreating a dataview everytime the user selects a row from the
distinct table, use the relation and use GetChildRows instead. That's MUCH
MUCH faster.

Check this out too ---
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/12/23/38575.aspx


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Not sure what group this question is better suited for so I sent it to
both.

I have a DataTable which contains 1545 rows. I have a method that returns
a
DataTable of distinct rows based on one column. This table ends up with
386
rows.

I need to loop through the smaller table and filter a DataView based on
each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386 rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}

 

[W.G. Ryan eMVP]

Joe - Sahil is dead right on this (as always) - I had reset the filters a
while ago on 2,000+ rows - then redid it using GetChildRows - it went from
taking around 12 seconds to complete to less than one. Night and day there.

 

[Sahil Malik]

mm hmm .. it took me a while to decipher what you were trying to say. I
might still be wrong, but I think you're trying to say that for the relation
to work the table must exist within the same dataset.

Is that a big deal? (Adding this table to your existing dataset?). You can
always remove it once you are done processing.

To remove the tables I need to remove the keys as well as the relations.

No you don't. You can do DataTable.Copy and then do a DataTable.ImportRow in
a loop to create a completely disconnected table with structure and data.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

This worked out great as far as performance but the problem I know have it
removing the tables from the DataSet I had to add them to in order for
this
to work.

To remove the tables I need to remove the keys as well as the relations.

Yes there is a much better way to do this.

Let me understand your problem first - You have two tables. One contains
distinct values but it was formed out of the first.

Instead of recreating a dataview everytime the user selects a row from
the
distinct table, use the relation and use GetChildRows instead. That's
MUCH
MUCH faster.

Check this out too ---
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/12/23/38575.aspx


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Not sure what group this question is better suited for so I sent it to
both.

I have a DataTable which contains 1545 rows. I have a method that returns
a
DataTable of distinct rows based on one column. This table ends up with
386
rows.

I need to loop through the smaller table and filter a DataView based on
each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386 rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}

 

[Sahil Malik]

I'm not always dead right , but thanks anyway

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Joe - Sahil is dead right on this (as always) - I had reset the filters a
while ago on 2,000+ rows - then redid it using GetChildRows - it went from
taking around 12 seconds to complete to less than one. Night and day
there.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Not sure what group this question is better suited for so I sent it to both.

I have a DataTable which contains 1545 rows. I have a method that returns a
DataTable of distinct rows based on one column. This table ends up with 386
rows.

I need to loop through the smaller table and filter a DataView based on each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386
rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}

 

[W.G. Ryan eMVP]

Well, every time I've seen. On this in particular though - the difference
is pretty nuts in a lot of cases. That's definitely one of those 'tips'
that is pure gold.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I'm not always dead right , but thanks anyway

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Joe - Sahil is dead right on this (as always) - I had reset the filters a
while ago on 2,000+ rows - then redid it using GetChildRows - it went from
taking around 12 seconds to complete to less than one. Night and day
there.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Not sure what group this question is better suited for so I sent it to both.

I have a DataTable which contains 1545 rows. I have a method that

returns
a

DataTable of distinct rows based on one column. This table ends up with 386
rows.

I need to loop through the smaller table and filter a DataView based on each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386
rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}

 

[Sahil Malik]

Yeah I recently started working on this project where they were doing
DataViews in one of their bases classes heavily, with about 10-15 lines of
code, I was able to increase their page throughput by about 33%. Now isn't
that cool?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Well, every time I've seen. On this in particular though - the
difference
is pretty nuts in a lot of cases. That's definitely one of those 'tips'
that is pure gold.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I'm not always dead right , but thanks anyway

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Joe - Sahil is dead right on this (as always) - I had reset the filters a
while ago on 2,000+ rows - then redid it using GetChildRows - it went from
taking around 12 seconds to complete to less than one. Night and day
there.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Not sure what group this question is better suited for so I sent it to
both.

I have a DataTable which contains 1545 rows. I have a method that returns
a
DataTable of distinct rows based on one column. This table ends up
with
386
rows.

I need to loop through the smaller table and filter a DataView based
on
each
row from this table.
This loops takes ~5 secs. Is there anyway to improve this?

DataTable dt = SelectDistinctValues(...); // returns a table with 386
rows
foreach (DataRow dr in dt.Rows)
{

dv.RowFilter = string.Format("value = '{0}'", dr[0].ToString() );

foreach (DataRowView drv in dv)
{
...
}
}