Hi,
apologies for the delay in posting this reply - we've been firefighting the past few weeks - but now that's over performance has been pushed back to the top of my boss's priority list.
As you can see from the methods below we typically have one parent business object consisting of two entities with several lists of child business objects hanging off it.
Most of these child object map to single entities.
I'm slightly confused as to wither we should be passing a single data adapter as a parameter to all methods called from the Load() method - or is it fine to just declare
a new adapter in each FetchXXXList() method ?
I realise now from reading the forum further that we should be using prefetch paths to improve perfomance - but as it stands - this code is running instantaneously on SQL sever - but helluva slow (10 - 120 seconds delay) on MS Access.
We're unfortunately stuck using MS Access just now - our boss is a self taught VB6 programmer who's been using Access for years. He's very sceptical about moving
to .NET and SQL server and doesn't understand why our .NET app is running slower
than his VB6 ADO app. Unless we can demonstrate like for like performance he is
likely to tell us to bin the entire .net migration and LLBLGEN
Based on our code below, what would be your suggested first step to improving performance?
/// <summary>
/// Load as part of a larger transaction
/// </summary>
/// <param name="objDataAccessAdapter"></param>
/// <param name="intID"></param>
public override void Load(DataAccessAdapter objDataAccessAdapter, int intID)
{
IHasSerialPorts iSerialDevice;
IVideoConnections iVideoDevice;
IBinaryIO iBinaryIODevice;
try
{
// All codecs are stored in the DB as a combination of a row in tblHardware and a row in tblCodec
// Load Hardware entity
base.Load(objDataAccessAdapter, intID);
// Load the Codec Entity
m_CodecEntity = new CodecEntity(intID);
objDataAccessAdapter.FetchEntity(m_CodecEntity);
if (m_CodecEntity.IsNew == false)
{
// Set the IP address from the Codec Entity
this.SetIPAddress(m_CodecEntity.IP);
// Set the Protocol from the Codec Entity
this.m_eProtocol = (enumCodecProtocol)m_CodecEntity.Protocol;
// Set the Resolution from the Codec Entity
this.m_eResolution = (enumCodecResolution)m_CodecEntity.Resolution;
// Load the Serial Port List
iSerialDevice = (IHasSerialPorts)this;
iSerialDevice.FetchSerialPortList(this);
// Load the Video Connections List
iVideoDevice = (IVideoConnections)this;
iVideoDevice.FetchVideoRouteConnectionsList(this);
// Load the Binary IO Pins List
iBinaryIODevice = (IBinaryIO)this;
iBinaryIODevice.FetchBinaryIOPinList(this);
}
else
{
Exception expBaseCodecLoad = new Exception("BaseCodec.Load() - Could not load Codec with ID=" + intID);
throw expBaseCodecLoad;
}
}
catch (Exception expCaught)
{
Exception expBaseCodecLoad = new Exception("Exception caught in BaseCodec.Load", expCaught);
throw expBaseCodecLoad;
}
} // End BaseCodec::Load()
/// <summary>
/// Retrieve a list of all serial ports which this device controls
/// </summary>
public void FetchSerialPortList(BaseDevice objThisDevice)
{
DataAccessAdapter objDataAcessAdapter;
EntityCollection collSerialPorts;
RelationPredicateBucket objWhereHardwareIDClause;
ISortExpression objOrderByClause;
int intSerialPortLoop;
SerialPort objNewSerialPortBusObj;
SerialPortEntity objSerialPortEntity;
// Remove all SerialPorts currently in the list
m_SerialPortList.Clear();
// Create a DataAcessAdapter
objDataAcessAdapter = new DataAccessAdapter(DBConnectionStrings.GetDevConfigDBConnectionString());
// Create the WHERE tblDeviceType.Device_Type_name = this.device_type_name
objWhereHardwareIDClause = new RelationPredicateBucket();
FieldCompareValuePredicate objHardwareIDFilter = PredicateFactory.CompareValue(
SerialPortFieldIndex.Hardware_ID,
ComparisonOperator.Equal,
objThisDevice.GetID());
objWhereHardwareIDClause.PredicateExpression.Add(objHardwareIDFilter);
objOrderByClause = new SortExpression(SortClauseFactory.Create(SerialPortFieldIndex.Port_number,
SortOperator.Ascending));
// Declare a collection of DeviceModel entities
collSerialPorts = new EntityCollection(new SerialPortEntityFactory());
/// SELECT * FROM tblSerialPort WHERE Parentnt_UID = this.ID ORDER BY SerialPortNo
objDataAcessAdapter.FetchEntityCollection(collSerialPorts, objWhereHardwareIDClause, 0, objOrderByClause);
// Now iterate through the collection and construct a SerialPort business object for each SerialPortEntity
for (intSerialPortLoop = 0; intSerialPortLoop < collSerialPorts.Count; intSerialPortLoop++)
{
// Create a SerialPort business object from the SerialPortEntity
objSerialPortEntity = (SerialPortEntity)collSerialPorts[intSerialPortLoop];
objNewSerialPortBusObj = new SerialPort(objSerialPortEntity);
// Add it to the SerialPortList
AddSerialPort(objThisDevice, objNewSerialPortBusObj);
} // Endfor SerialPortLoop
} // End FetchSerialPortList()
Thanks
SolidPig