1 Reply Latest reply on Feb 28, 2008 10:54 AM by bobmoles

    Some Techniques we use to deal with .NET anomolies

    bobmoles
      This code will not work if you have Hot Fix 2 installed.

      Data table to query object – 3792 rows with 5 columns ~~about 12-13 Seconds

      BACKGROUND: We are using .NET to call all appropriate data into ADO DataSets to ensure our ability to persist data even if the database is taken offline. We are using a CFGRID to display the data. We are using flash remoting to call the component that returns the query and then puts the query into the data provider for the grid. The ColdFusion code and the C # Code are attached below for a little better clarification of our problem and initial solution.


      Initially we tried to use autoconversion and were getting nothing due to timeout errors. We then turned off autoconversion and started explicitly calling dotNetToCFType(dataTable) with the same result. We then moved the entire application to a much more robust server and started getting results. On the new server, this process was taking approximately 49000 ms to return. We also encountered errors when we allowed .net to build the datatable and return it to us, so we started building the .net datatable in Coldfusion and allowing the dll to fill the table for us. In our actual model, it is several dll’s that manage a lot more than just the datagrid, so where you see discrepancies in variable passing we missed something in the code rewrite for you.


      Total Time Avg Time Template
      12563 ms 12563 ms C:\Inetpub\wwwroot\Portal\F1.cfm
      12500 ms 12500 ms CFC[ C:\Inetpub\wwwroot\Portal\personnel\personnel.cfc | getList2() ] from C:\Inetpub\wwwroot\Portal\personnel\personnel.cfc
      0 ms 0 ms CFC[ C:\Inetpub\wwwroot\Portal\application.cfc | onRequestEnd(/portal/F1.cfm) ] from C:\Inetpub\wwwroot\Portal\application.cfc
      0 ms 0 ms CFC[ C:\Inetpub\wwwroot\Portal\application.cfc | onRequestStart(/portal/F1.cfm) ] from C:\Inetpub\wwwroot\Portal\application.cfc
      0 ms STARTUP, PARSING, COMPILING, LOADING, & SHUTDOWN
      12563 ms TOTAL EXECUTION TIME

      COLDFUSION CODE

      APPLICATION.strportallib = “c:\windows\microsoft.net\framework\v2.0.50727\system.data.dll,C:\Inetpub\wwwroot\Portal\ Bin\PortalInterfaces.dll,C:\Inetpub\wwwroot\Portal\Bin\PortalCFInterop.dll”

      <cffunction name="getGridItems" returntype="array" access="remote">
      <!--- format = [name , Header Text, Display(y/n)] --->
      <cfset gridItems = arrayNew(2)>
      <cfset gridItems[1] = ["nIndex", "Record Index", "no"]>
      <cfset gridItems[2] = ["strLastName", "Last Name", "yes"]>
      <cfset gridItems[3] = ["strFirstName", "First Name", "yes"]>
      <cfset gridItems[4] = ["strMiddleName", "Middle Name", "yes"]>
      <cfset gridItems[5] = ["strSocialSecurityNumber", "Social Security Number", "yes"]>
      <cfreturn gridItems>
      </cffunction>

      <cffunction name="getList" returntype="query" access="remote">
      <!--- By declaring the variables with keyword var, the function will free up the memory that was being used for these items and eliminate data persistence through caching. --->
      <cfset var gridItems = "">
      <cfset var dtPersonnel = "">
      <cfset var queryPersonnel = "">
      <cfset var nFormID = "">
      <cfset var dtSelectTemp = "">
      <cfset var newRow = "">
      <cfset var arCellValue = "">
      <!--- We have to make a call to get the list of grid Items for this portlet. --->
      <cfset gridItems = getGridItems()>
      <!--- We have to instantiate the datatable that will be passed to the dll to be filled. --->
      <cfset dtPersonnel = createObject(".net","System.Data.DataTable","#APPLICATION.strPortalLib#").init()>
      <!--- We have to instantiate the Coldfusion query object that will be filled by the datatable methods. --->
      <cfset queryPersonnel = queryNew("gPersonnelGUID, strLastName, strFirstName, strMiddleName, strSocialSecurityNumber")>
      <!--- This call fills the table based on the users portal Token. See C# code below. Once the process is finalized, all uses of the returned datatable will be in try-catch/if-then blocks to handle empty datasets. --->
      <cfset nFormID = APPLICATION.ifcPortal.GetFormID(dtPersonnel)>
      <!--- This extracts all rows from the datatable into an array. --->
      <cfset dtSelectTemp = dtPersonnel.Select()>
      <!--- This command prepares the query to be filled. --->
      <cfset newRow = queryAddRow(queryPersonnel, #arrayLen(dtSelectTemp)# )>
      <!--- This set of commands loops through the Row array. --->
      <cfloop from="1" to="#arrayLen(dtSelectTemp)#" index="i">
      <!--- This gets all the column data for one row. --->
      <cfset arCellValue = dtSelectTemp .Get_ItemArray()>
      <!--- This set of commands loops through the column array. --->
      <cfloop from="1" to="#arrayLen(arCellValue)#" index="x" >
      <!--- This set of commands Writes the values to the correct column and row in the array. --->
      <cfset QuerySetCell(queryPersonnel, "#gridItems[x][1]#", "#arCellValue[x].stringValue()#", i)>
      </cfloop>
      </cfloop>
      <!--- This returns the coldfusion query object. --->
      <cfreturn queryPersonnel>
      </cffunction>



      C # Code

      public bool GetGridData( DataTable tableGridData )
      {
      if( tableGridData.Columns.Count != 0 )
      {
      return false;
      }
      // We can't pass the CF-provided Table but can fill it
      // using its member methods; create a temp table for
      // library call, then fill the destination table before return
      DataTable tableTempGrid;

      try
      {
      tableTempGrid = new DataTable( );
      }
      catch(Exception exceptNew )
      {
      return false;
      }

      if( tableTempGrid == null )
      {
      return false;
      }
      // This will fill the DataTable based on session context
      if( !m_mgrData.FillGrid( ref tableTempGrid ) )
      {
      return false;
      }

      int nRows = tableTempGrid.Rows.Count;

      if( nRows < 1 )
      {
      m_strLastError = "GetGridData received NULL Results DataTable";

      return false;
      }
      // We now have a source table to copy
      // Fill provided object
      tableGridData.TableName = "PersonnelGrid";

      tableGridData.Columns.Add( "nIndex" );
      tableGridData.Columns.Add( "strLastName" );
      tableGridData.Columns.Add( "strFirstName" );
      tableGridData.Columns.Add( "strMiddleName" );
      tableGridData.Columns.Add( "strSocialSecurityNumber" );

      for( int i = 0; i < nRows; i++ )
      {
      DataRow rowNew = tableGridData.NewRow( );

      DataRow rowSource = tableTempGrid.Rows[ i ];

      rowNew[ "nIndex" ] = i;
      rowNew[ "strLastName" ] = rowSource[ "strLastName" ];
      rowNew[ "strFirstName" ] = rowSource[ "strFirstName" ];
      rowNew[ "strMiddleName" ] = rowSource[ "strMiddleName" ];
      rowNew[ "strSocialSecurityNumber" ]
      = rowSource[ "strSocialSecurityNumber" ];

      tableGridData.Rows.Add( rowNew );
      }

      // If we get here, we're golden!
      return true;
      }
        • 1. Re: Some Techniques we use to deal with .NET anomolies
          bobmoles Level 1
          Still doesn't work with Hot Fix 2
          We have actually modified the Call to GetGridData to look like this:

          <cffunction name="getGridData" returntype="query" access="remote">
          <cfargument name="gridNumber" type="numeric" required="yes">

          <!--- By declaring the variables with keyword var, the function will free up the memory that was being used for these items. --->
          <cfset var dtTemp = "">
          <cfset var outQuery = "">
          <cfset var bSuccess = false>
          <cfset var dtSelectTemp = "">
          <cfset var newRow = "">
          <cfset var arCellValue = "">
          <cfset arrColumnNames = arraynew(1)>
          <!--- We have to instantiate the datatable that will be passed to the dll to be filled. --->
          <cftry>
          <cfset dtTemp = createObject(".net","System.Data.DataTable","#APPLICATION.strPortalLib#").init()>
          <cfcatch type="any">
          <cfset APPLICATION.strerror1info = "DataTable not created">
          </cfcatch>
          </cftry>



          <!--- This call fills the table based on the users portal Token. --->
          <cfset bSuccess = APPLICATION.ifcPortal.PopulateGrid(SESSION.portalToken, ARGUMENTS.gridNumber, dtTemp)>
          <cfif bSuccess>
          <cfset nColumnIndex = dtTemp.Get_Columns().Get_Count() -1>
          <cfloop from="0" to="#nColumnIndex#" index="i">
          <cfset t_columnName = dtTemp.Get_Columns().Get_Item(i).Get_ColumnName()>
          <cfset arrColumnnames[i+1] = "#t_columnName#">
          </cfloop>
          <cfset listColumnNames = arrayToList(arrColumnNames, ",")>
          <cfset outQuery = queryNew(listColumnNames)>
          <!--- This extracts all rows from the datatable into an array. --->
          <cfset dtSelectTemp = dtTemp.Select()>
          <cfset nRecCount = arrayLen(dtSelectTemp)>
          <cfif nRecCount LTE 0>
          <cfset outQuery = queryNew("strLastName, strFirstName, strMiddleName, strSocialSecurityNumber, gPersonnelGUID")>
          <cfelse>
          <!--- This command prepares the query to be filled. --->
          <cfset newRow = queryAddRow(outQuery, #arrayLen(dtSelectTemp)# )>
          <!--- This set of commands loops through the Row array. --->
          <cfloop from="1" to="#arrayLen(dtSelectTemp)#" index="i">
          <!--- This gets all the column data for one row. --->
          <cfset arCellValue = dtSelectTemp .Get_ItemArray()>
          <!--- This set of commands loops through the column array. --->
          <cfloop from="1" to="#arrayLen(arCellValue)#" index="x" >
          <!--- This set of commands Writes the values to the correct column and row in the array. --->
          <cfset QuerySetCell(outQuery, "#arrColumnnames[x]#", "#arCellValue[x].stringValue()#", i)>
          </cfloop>
          </cfloop>
          </cfif>
          <cfelse>
          <cfset outQuery = queryNew("strLastName, strFirstName, strMiddleName, strSocialSecurityNumber, gPersonnelGUID")>
          </cfif>

          <!--- This returns the coldfusion query object. --->
          <!--- ---> <cfreturn outQuery>
          </cffunction>