StrataFrame Forum

Business Object Performance

http://forum.strataframe.net/Topic25133.aspx

By Paul Chase - 11/6/2009

I have a routine that is importing Foxpro Data. This week  made some changes and added a few larger tables(more records and more fields) to the import routine that has been running for months. These "new" tables are by no means crazy huge as the largest one is right around 75,000 records and 30 or so fields. This routine has been running at night so I never really noticed if this has always been an issue. however while debugging changes noticed that the performance was lacking..  

Once I started looking onto it I was very suprised at the perfomance vs a plain datatable. I did expect a business object to have some additional overhead over a vanilla datatable , however I was expecting a very minimal difference compared to the results I am seeing. Perhaps I am missing something easy like not setting a business object property or I am looking at something completly wrong. Whatever it is there seems to be a huge performance difference that I would like to get to the bottom of it.

To take anything I was doing out of the mix I created a test project that uses the stataframe sample orderitems table as a source and destination bo. I then created a standard plain old vanilla BO mapped to the items table. The only business object property that is changed from default is the "allowsnullvaluesonnewrow" is set to true.

Basically I fill a 'source' business object Then loop through the 'source' business object and create a new row on a destination object and sets the value of a field on the destination object from the source .

Business Object Example

'Set Start Time

starttime = DateTime.Now

'Loop through filled Business Object

For Each bo As itemsbo In SourceBO.GetEnumerable

'Create New Row in Destination Business Object

DestinationBO.NewRow()

'Set Value of 1 field

DestinationBO.orit_Quantity = bo.orit_Quantity

Next

'Set Stop Time

stoptime = DateTime.Now

'Set Elapsed

elapsed_time = stoptime.Subtract(starttime)

Same routine using a vanilla datatable.

'Set Start Time

starttime = DateTime.Now

'Loop through filled Business Object

For Each bo As itemsbo In SourceBO.GetEnumerable

lorow = dt.NewRow

'-- Set Value of 1 fld

lorow("orit_Quantity") = bo.orit_Quantity

'-- Add the new row to the data table

dt.Rows.Add(lorow)

Next

'Set Stop Time

stoptime = DateTime.Now

'Set Elapsed

elapsed_time = stoptime.Subtract(starttime)

Here are my results based on 50,000 records from the strataframe order items table. These times do not include any filling of data or saving of data.

Use a bo to create a new row and set no values                    .54 seconds

use a bo to create a new row and set the value of one field     5.04 seconds

use a bo to create a new row and set the value of two fields   9.51 seconds

use a bo to create a new row and set the value of 3 fields     14.17 seconds

----------------Data Table--------

Use a datatable to create a new row and set no values            .21 seconds

use a dt to create a new row and set the value of one field      .31 seconds

use a dt to create a new row and set the value of two field      .45 seconds

use a dt to create a new row and set the value of 3  fields      .51 seconds

 

By Paul Chase - 11/6/2009

I guess I forgot to attach the sample app
By Paul Chase - 11/12/2009

Hey guys did this get skipped\missed?

Do you need anything additional from me? Just would like to know you guys are looking at this problem and it is on the radar.

Thank you,

Paul 

By Trent L. Taylor - 11/12/2009

I am taking a look at it right now.
By Trent L. Taylor - 11/12/2009

Paul,



I will have to look at this some more tomorrow. I am going to run it through a profiler to see where the hard spots are. Thanks.
By Paul Chase - 11/13/2009

Thanks Trent.
By Trent L. Taylor - 11/13/2009

Paul,



The code issue is actually happening within the DataTable. I think that there is a handler somewhere within the DataTable managing the ListChanged event which is tied into the FieldPropertyDescriptors that is causing the delay. I am going to have to break this down to the angle iron as the root of the issue is actually within the ADO.NET data table, but is manifesting itself through a handler. I have killed all handlers, etc. and not been able to isolate it just yet.



I am going to add this to my list. However, in the interim, if you are dealing with massive records and just want to circumvent all BO handlers, you can extract the datatable, add the rows, and the BO will still be looking at the same BO reference:



Dim dt As DataTable = MyBO.CurrentDataTable

Dim row As DataRow



'-- Cycle through all of the source rows

ForEach bo As MySourceBO in sourceBo.GetEnumerable()

row = dt.NewRow()

'-- Update the row here

'-- Add the row to the data table within the BO

dt.Rows.Add(row)

Next




Basically this will circumvent all DataTable list changed events. Like I said, I am going to revisit this when I can dedicate 4+ hours as I have already spent 2 with little progress as there is a handler or delegate conflict going on. But the above would at least be a temp solution.
By Trent L. Taylor - 11/13/2009

One more thought...the main thing here is that before we make any changes, we are going to have to totally understand the bottleneck and make sure that any change made doesn't break something else as this would be a low level change. Thus the time necessary to diagnose needs to be allocated prior to making any change.
By Paul Chase - 11/13/2009

Thanks Trent.

For the most part the import routine that this affect's runs after hours unless there is some type of exception that would require a manual rerun. So rather than refactor the mapping methods to use a datatable I will leave it as is for now as I do have some other stuff going on that I might break. So it is not a life or death issue for me but I did want to find out what was going on, and obviously resolve it.

I understand 100% that this will not be a "quick" fix as the underlying datatable events have to be one of the worms at the bottom of the business object can.

Appreciate ya,

Paul  

By Les Pinter - 11/16/2009

Hi Paul,

   What's the old joke? A guy says "Doctor, it hurts when I do this" and smacks his head. Doctor says "Then don't do it..."

   Back in the heyday of FoxPro, I wrote an article very similar to your comment about the listboxes in the "two-column mover" control created by Alan Griver, who is now in charge of languages at MS. I was able to load a 50,000-line listbox must faster than his method did, using arrays. His answer was "why do you need 50,000 items in a listbox?". I had to admit that he was right.

   There's probably a way to speed up listview loading internally from a business object, but maybe a BrowseDialog will give your users a more manageable way of filtering and selecting records than would a ListView. That's what I ended up doing. By the time my users got to the two-column-mover, they were looking at only a few hundred records, having already filtered out what they weren't interested in.

Les

By Paul Chase - 11/16/2009

Hi Les,

I think there is a misunderstanding, I am not trying to use a listview with a large recordset  Smile

I am bulk importing records from foxpro to a sql database. The issue I am having is that setting the values on the destination business object via the strong typed property takes a good bit longer than setting the values on a plain jane datatable.

By Keith Chisarik - 11/16/2009

I am very interested in your findings, I found this a long time ago and honestly just figured it was the way it was, anything over 20k records (ballpark) I have been using SQLBulkCopy to get speed on the commit.
By Les Pinter - 11/17/2009

Hi Paul,

   Darn, I was in such a hurry to tell that anecdote that I didn't read your post carefully. That'll learn me...

Les

By Edhy Rijo - 11/17/2009

Hi Paul,



I agree with Keith about SQLBulkCopy, in fact in this thread you can see how Keith's sample code helped me in this regard.
By Paul Chase - 4/12/2010

Trent,

I recently updated to the latest version of SF and was unable to tell if this issue was resolved ?

Thanks

Paul

By Trent L. Taylor - 4/12/2010

Well, I am not sure that it is actually an issue. From reading through the post, you are dealing with large numbers of records in a single BO. If you are trying to do mass updates, then the SqlBulkCopy is a great way to dump tons of records into a table and totally sidetracking the strong-typing and data verification.



That being said, I have not had a chance to put this through a code tracing tool to see where your exact bottleneck may be. Most like there is an event being raised and handled. I will take another look when I get my head above water.
By Paul Chase - 4/13/2010

Hey Trent,
I respectfully disagree that this is not really an issue Smile I do agree that this is not a NOTICEABLE issue for most day to day use's of business object's. I would also like to clarify that The bottleneck is with setting field values in a BO vs. a ADO datatable ,NOT on the adding of rows or the saving of rows.(BTW I do use Bulk Copy in instances where I am doing mass inserts of records but it doesn’t change this issue).


As an example programmatically SETTING the values of 100 fields in 1 data row takes .002 seconds via Strong Typed BO properties and .0000000000 seconds via weak typed ado, probably not noticeable. However setting the same values for the same 100 fields but for 1000  data rows then takes 7.64 seconds vs .03 seconds and that IS a noticeable difference.


I realize I can refactor my code to use weak typing and directly reference the businessobject's underlying datatable to increase performance by bypassing whatever underlying handler is mucking up the waters and I have done this in some places where I need the performance and was unable to wait for it to be resolved.
Really the long and short of it is that I am using a business object to programmatically set multiple values for somewhat large tables with large recordset’s. Although this usage may not be the norm ,I feel it is reasonable to expect a business object to perform somewhat on par with a datatable?

 
I realize that whatever is causing the issue is in a sticky spot and will take you guys more time to resolve and I appreciate the time and effort that it takes to get to it. I just wanted to clarify what exactly the issue is, and to simply re-iterate that this is an issue that affects my business processes negatively and let you know I appreciate whatever you can do to resolve it .

Thanks Man,

Paul