none
RowFilter Performance Problem in VS.NET 2005 ! RRS feed

  • Question

  • Hi,

    I have a Project in VS.NET 2003 which uses DataView.RowFilter. When I compile it in VS.NET 2005. Performance of the RowFilter Command decreases dramatically.

     

    I am in the first stages of migrating my project to VS.NET 2005. But this is a big draw back. I would really appreciate it if you could help me in this. No need to mention that Microsoft has indicated that RowFilter performance has increased in this new version.

     

    Thanks,

    Mehdi

    • Moved by VMazurModerator Tuesday, March 24, 2009 10:12 AM (Moved from ADO.NET Data Providers to ADO.NET DataSet)
    Saturday, February 4, 2006 1:04 PM

Answers

  • One easy optimization you can do is creating the view using a single stmt.

    DataView dv = new DataView( TableName, "EName like '%Test%'", null, DataViewRowFilter.CurrentRows );

    This should improve by ~50% +.

    When a DataView is created we verify which rows are passing the filter, and then fetch them. If you create a view without a filter, we have to fectch all rows. (Fetching rows in 2.0 is more expensive because we changed the way we internally store them in order to be more scalable). After that, you create a new filter, and we have to get the filtered rows again.

    As far as the perf to select rows from a datatable, the more rows your filter selects, the longer we will take to fetch and add them to the DataView. If you select fewer rows (really depends on the filter) we will be very fast.

    Does this help ?

    Sorry,

    --VV [MS]
    vascov@microsoft.com

     

    Wednesday, February 8, 2006 9:16 PM

All replies

  • I wouldnt expect to get much of a response when all you say is "performance decreases dramatically". How much data is in the table? What are you setting the row filter to? What does "dramatically" mean?
    Sunday, February 5, 2006 8:20 PM
  • I would also be interested on having more details about the regression.

    How are you measuring time ? (from where to where and how)

    Thanks,

    --VV [MS]

    Monday, February 6, 2006 5:36 PM
  • Hi,

    Sorry for the lack of information, but I have tested a few scenarios and I had the same problem with it. so I thought it should be a general problem (as opposed to related to a specific situation).

    Anyways here's one of the cases :  

    Database : Microsoft Access File (.mdb)

    Table Rows : 70,000

    Search field Size and Name :  EName - string[60]

    Statement used to Filter :

           DataView dv = new DataView( TableName );

           dv.RowFilter = "EName like '%Test%'";

    Time consumed by dv.RowFilter When Compiled by .NET1.1 : almost 1.5 secs 

    Time consumed by dv.RowFilter When Compiled by .NET2.0 : almost 10 secs 

    Wednesday, February 8, 2006 7:01 AM
  • One easy optimization you can do is creating the view using a single stmt.

    DataView dv = new DataView( TableName, "EName like '%Test%'", null, DataViewRowFilter.CurrentRows );

    This should improve by ~50% +.

    When a DataView is created we verify which rows are passing the filter, and then fetch them. If you create a view without a filter, we have to fectch all rows. (Fetching rows in 2.0 is more expensive because we changed the way we internally store them in order to be more scalable). After that, you create a new filter, and we have to get the filtered rows again.

    As far as the perf to select rows from a datatable, the more rows your filter selects, the longer we will take to fetch and add them to the DataView. If you select fewer rows (really depends on the filter) we will be very fast.

    Does this help ?

    Sorry,

    --VV [MS]
    vascov@microsoft.com

     

    Wednesday, February 8, 2006 9:16 PM
  • Hi,

    Thank you for your accurate practical reply. I'd tested this situation before and the result is almost like what you say (30-50 % faster).
    But you see my problem here are two things :

     1. I have given the user the ability to select the field to search for and critica, so as you see I can't make any judgements on what field is used (in my example it's not neccessarily EName could be any other field).

       2. As I mentioned, RowFilter time consumption in .NET 2.0 is about 7-8 times slower and although the thing you mentioned makes it almost 2 times faster (by limiting us to only one field), It's still gives the user 4 times slower filter time experience.

      Finaly, the main time consuption for RowFilter in my experience is not related to number of rows passing the query, cause I tested a Filter ( EName like '%jack%' ) and it returned 4 rows out of 70,000 and it took about 8 secs.

    Now, could you please give me an advice on how to cope with these problems.

     

    Thank you again,

    Mehdi Moshtaghi.

    Saturday, February 11, 2006 6:15 PM
  • Hi Mehdi,

    You can have more than one condition on the filter refering to the same or different fields.

    Can you send me your DB and a small repro to vascov@microsoft.com ?

    In the meantime, i encourage you to get in touch with CSS [1] in order to get a fix / solution to the perf problem you are experiencing. We have one optimization which may improve your scenario.

    Again, sorry for the perf degradation,

    --VV [MS]
    vascov@microsoft.com

    [1] http://www.microsoft.com/services/microsoftservices/srv_support.mspx

     

    Sunday, February 12, 2006 8:33 PM
  • Hi, Mehdi

    I have the same problem now with a project that I upgraded to VS.NET 2005.

    I use a Dataview and the RowFilter property to filter data like you did. I have about 140.000 rows. When filtering is done with the VS2003 version it only takes a second. The VS2005 verision need almost 30 seconds!!!!

    I read all replies to this post. Did you contact CSS???

    Did they give you more info concerning this problem??

     

     

    Wednesday, May 10, 2006 12:20 PM
  • I don't know If I should start a new post for this, but my RowFilter command is not working at all, I set it, and rows that are supposed to be filtered out are coming through.

    dvPetra = New DataView(dtPetra)

    dvPetra.Sort = "ServerWellLogPath"

    dvPetra.RowFilter = String.Format("ErrorMessage IS NULL AND SessionGUID = '{0}'", TestGUID)

    I am getting records where both ErrorMessage is not null, and the GUID is wrong.

    Wednesday, May 24, 2006 2:19 PM
  • Can you send me a repro to vascov@microsoft.com ?

    I'm interested in understanding why you are getting results back that you shouldn't.

    Thanks,

    --VV [MS]
    vascov@microsoft.com

    Saturday, May 27, 2006 12:37 AM
  • I'm sorry for the perf degradation you're experiencing.

    I ask you to get in touch with CSS and open an incident to solve the performance issue you're experiencing.

    --VV [MS]
    vascov@microsoft.com

    Saturday, May 27, 2006 12:39 AM
  • I already contacted Microsoft Austria and sent them a test projects (VS2003 and VS2005 versions) doing exactly the same thing where they could easily reproduce the situation.

    They told me that there will be a bug fix with the next version of the .NET Framework.

    They also told me to contact PSS to get a HotFix.

    Since I still found no time to contact them I decided to stay with VS2003 and .NET Framework 1.1 :-(((!

    Thanks for your reply.

    Wednesday, June 14, 2006 6:39 AM
  • At TechEd 2007, I talked to someone on the DataSet team at Microsoft about this very same issue that I was having. He said it was because they had re-architected the way they were organizing data behind the scenes in 2.0 as opposed to 1.1. He said that 1.1 used ArrayLists for data organizations in DataSets and that 2.0 used an auto-balancing Red-Black tree and that was most likely the balancing operations of the tree that were causing the performance degradation.

     

    He said that this change was made, because it enabled the DataSet to scale better "overall" but that they were aware of performance degredations in operating over small amounts of data across the two versions. If I find out anything else, I will let you know.

    Wednesday, July 11, 2007 1:12 PM
  • Thanks man,

       The Performance degrade which I experienced, was on a table with more than 100K rows, and that's big enough for a table in memory (in my opinion). But anyways, thanks for this piece of information. I didn't know they 'd used B-Tree's for this.

     

      

    Friday, July 13, 2007 3:31 PM
  • Hi,

    Does any one know if there has there been any progress on this performance problem?

     

    Thursday, March 20, 2008 11:50 AM
  • With .NET Framework 2.0 SP1 the problem was fixed for my project, so I could upgrade to VS2005.
    I also upgraded the project to VS2008 still targeting .NET Framework 2.0 SP1
    Saturday, March 21, 2009 10:57 PM