StrataFrame Forum

Form Load Speed (revisited)

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

By Bill Cunnien - 3/31/2009

I know that I have brought this up before, but an end-user of our SF application brought something to my attention today that was very appalling.



A window loads in under 4 seconds in subnet A. In subnet B, the same form loads in just over 60 seconds. Subnet A is connected to subnet B by a dedicated T1 line. That is a slow line, but it should not degrade the form load speed by over 1200%.



I realize that this is the proverbial "How long is a string?" question, but can someone provide some tips for me to help reduce the load time of the form?



There are 11 business objects on the form. There are 3 MRUTextEdit controls using a GetDataTable function in the DataBasics namespace. These datatables contain less than 300 records each (one column). There are 10 combo boxes filled by the corresponding BOs fill method. No data is being retrieved for the actual form until the browse dialog is engaged.



My intuition tells me that these comboboxes are the root problem. If I am accessing a BO for each item in the combobox, then there is potentially a LOT of work going on for the filling of each combobox (custom properties, custom methods to retrieve data from other tables, etc.).



Any tips would be extremely helpful! Thanks!!

Bill
By Greg McGuffey - 3/31/2009

Bill,



Well, before you get too carried away, you might want to do a profiler of some type to make sure the BOs are the issue. No reason to go barking up the wrong tree. I've used NetLimiter to throttle my bandwidth down to simulate slow connections (http://www.netlimiter.com). Also, turn on debugging for the datasources to see how many connections are actually being opened when the form is opened.



If it is the BOs, I'd start getting friendly with FillMultipleDataTables method (shared/static method of BusinessLayer). Often it isn't the amount of data, but rather then number of connections being opened and closed. With FillMultipleDataTables you could probably get that from something like 13 connections down to 2 or 3 (or one if you real clever BigGrin ).
By Trent L. Taylor - 3/31/2009

Greg is right, but you can also optimize this drastically.  We have a maintenance program that has over 500 objects, around 20+ combos that require population, and 10 child tables.  All of this loads in less that 1 second (worst case 2-4 on a slow VPN connection).  So this is 100% about how you query and the architecture.  Remember, the size is not always the issue here, but rather the number of trips.

The very first thing I would do is create a stored procedure that returns all of the result sets that you need.  This would be one query for all of the combo boxes.  In the load of the form, you will use the BusinessLayer.FillMultipleDataTables method to execute the stored procedure and load all of the combo BOs at the same time:

Dim cmd As New SqlCommand("dbo.MyStoredProc")

cmd.CommandType = SqlCommandType.StoredProcedure

BusinessLayer.FillMultipleDataTables(cmd, _
                                                 MyBo1, _
                                                 MyBo2, _
                                                 etc...)

Next, change all of the combos to use the CopyDataFrom method and the then use the ListPopulating event for each of these to provide the already populated BO.  I am willing to bet that this alone will drastically improve performance.  There are a number of other tips as well, but this would be a good place to start.

By Bill Cunnien - 4/1/2009

Thanks, guys!! I'll start working on this today. As soon as I convert the window's combos to the method outlined, I will run some tests and let you know the results.



Much appreciated!

Bill
By Bill Cunnien - 4/1/2009

I am having a little trouble with this...





PartTypesBO _parttypesBO = new PartTypesBO();



private void PartsMaintenance_Load(object sender, EventArgs e)

{

InitializeData();

}



private void InitializeData()

{

SqlCommand cmd = new SqlCommand("spx_PartsMaintenance_Load");

cmd.Parameters.AddWithValue("@division", AspireGlobals.CurrentUser.LocationIndex).SqlDbType = SqlDbType.Int;

BusinessLayer.FillMultipleDataTables("Aspire", cmd, _parttypesBO);

}



private void cboPartType_ListPopulating(MicroFour.StrataFrame.UI.ListPopulatingEventArgs e)

{

e.Parameters[0].Value = _parttypesBO;

e.Parameters[1].Value = BusinessCloneDataType.ClearAndFillFromCompleteTable;

}





I thought I would try this with one BO to see how it works. The error that I am getting is in the GetDataSet overridable function in the dbdatasourceitem class. It states {"Line 1: Incorrect syntax near 'spx_PartsMaintenance_Load'."}. Here is the stack trace:



" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataSet(DbCommand command) at MicroFour.StrataFrame.Business.BusinessLayer.FillMultipleDataTables(String dataSourceKey, DbCommand command, BusinessLayer[] businessObjects) at Aspire.Engineering.PartsMaintenance.InitializeData() in C:\Aspire Projects\AspireSF\Engineering\PartsMaintenance.cs:line 57 at Aspire.Engineering.PartsMaintenance.PartsMaintenance_Load(Object sender, EventArgs e) in C:\Aspire Projects\AspireSF\Engineering\PartsMaintenance.cs:line 42 at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Windows.Forms.Form.OnLoad(EventArgs e) at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.OnLoad(EventArgs e) at System.Windows.Forms.Form.OnCreateControl() at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ContainerControl.WndProc(Message& m) at System.Windows.Forms.Form.WmShowWindow(Message& m) at System.Windows.Forms.Form.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m) at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)"




Hopefully, this is an easy fix. I am stumped at the moment.



Thanks,

Bill
By Bill Cunnien - 4/1/2009

Crazy



(Now, where is that smiley that has the hammer hitting its head?)



cmd.CommandType = CommandType.StoredProcedure;



Oops!

Blush
By Greg McGuffey - 4/1/2009

You probably just need to set the CommandType of the cmd object to sproc.
By Greg McGuffey - 4/1/2009

LOL, yep, thought that was it. And yeah, that is definitely a forum enhancement request...smiley with hammer hitting head...



p:-<
By Bill Cunnien - 4/1/2009

Is it necessary to call a requery on each combobox so that the list gets filled after the FillMultipleDataTables method is called? I am finding that the comboboxes are loading before the window's load method.
By Trent L. Taylor - 4/1/2009

Change the PopulateOnFormLoad property to Manual.  Then call the Requery() after the FillMultipleDataTables on each of the combos.
By Bill Cunnien - 4/2/2009

I have converted the window to utilize the FillMultipleDataTables method.



Speed test results to load the window:

This side of the T1 - 3 seconds

Other side of the T1 - 48 seconds



That was not much of an improvement. Is there anything else to address?



Thanks,

Bill
By Bill Cunnien - 4/2/2009

Greg,



I am downloading the demo version of NetLimiter Pro. I will install it on my machine (development) and limit my bandwidth to the same as a T1 (1.5Mb). Any tips or tricks (or gotcha) that I should be aware of?



Thanks,

Bill
By Bill Cunnien - 4/2/2009

After installing the software, I set my bandwidth to 1.5 Mbits. When I ran the application, I got the same load speed for the form as if I had the full 100 Mbits bandwidth. It loaded in under 2 seconds. I am now suspecting a systemic problem in the remote subnet. That will be a bit more complicated to solve, I suppose. At least, I know for sure that my development and StrataFrame are not involved and that the application is running at peak efficiency.



Thanks for you help!!

Bill
By Greg McGuffey - 4/2/2009

Bill,



I noticed that often I didn't get any noticeable difference unless I really cranked down the bandwidth. I was testing due to complaints out in the field, and after I did my testing, I began to suspect they were using a wireless access card (cellular), in a concrete reinforced bomb shelter. In any case, you might just try limiting severely and then getting it to work faster in any case. I cranked my limits down to 500...bytes. It might not be worth the effort, but then, you'll likely get that 2 seconds to milliseconds BigGrin
By Bill Cunnien - 4/6/2009

Not a bad idea...thanks, Greg!