DataTable question/issue.

[fiat2]

I have this app that is passing a dataset to an Excel writer. The database call in SourceData_Build() returns only about 10 rows of data but contains almost 200 columns.

The app has no problem with Converting to Excel and saving a file on the webserver. The issue is that the Panel is never updated and it appears to the user that the webpage is still loading...The state of the page does not change.

I was thinking that the Dispose() would release all resources but it doesn't appear to be doing anything.

has anyone had experience with this type of issue?

DataTable myDataTable = SourceData_Build();
myDataset.Tables.Add(myDataTable);

dc.ConvertDataSetToExcel(myDataset, @FileLocation, REPORT_NAME.ToString()); 

myDataTable.Dispose();

 

[jmeckley]

i don't think dispose will gain you anything here. I could be mistaken, but I think DataTables implement IDisposable only because of the inheritance chain. I don't think it actually utilizes Dispose.

the code above has no ties to the GUI so this isn't the problem. The problem lies in where/how you update the panel.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fiat2]

good point on the Dispose method.

You're right, I shortened the code snippet to ask the question...always a bad move.

here is my Button click event:

protected void btnDownload_Click(object sender, System.EventArgs e)
    {
        MessageBox.Text = "";
        string result = "0";

        //Validate dates
        if (!ValidateInput())
        {

        }
        else
        {
            try
            {
                DataSet myDataset = new DataSet();
               // DataTable myDataTable = SourceData_Build();
                myDataset.Tables.Add(myDataTable );
               // myDataTable.Clear();
                //myDataTable.Dispose();                


                 DataSet myDataset2 = new DataSet();
                DataTable myDataTable2 = SourceData_Build2();
                myDataset2.Tables.Add(myDataTable2);      

                          
                // Set file location             
                string FileLocation = "\\\\blfsrocrdc02\\roc_rd\\public\\WebReports\\";
                string FileExtenstion = ".xls";

                dc.ConvertDataSetToExcel(myDataset2, @FileLocation, REPORT_NAME.ToString());

                H1.Visible = true;
                H1.NavigateUrl = "[URL unfurl="true"]http://camsreports/downloads/"[/URL] + REPORT_NAME.ToString() + FileExtenstion;
            }             
            catch (Exception ex)
            {
                string ErrorMsg = ex.Message;
                MessageBox.Text = ErrorMsg;
                return;
            }
            finally
            {
                if (result != "0")
                { MessageBox.Text = result; }
                else
                {
                    lblTimeStamp.Text = "Report Downloaded: " + System.DateTime.Now.ToLongDateString() + " at " + System.DateTime.Now.ToShortTimeString();                                                            
                }
            }            
            btnDownload.Enabled = false;
        }
        //Enable Export Functionality and DataGrid and display Time Stamp
        tblData.Visible = true;

    }

Right now, myDataset2 is returning a different DataTable, with only 3 rows, 5 columns. This works fine. Excel file is created, Panel updates and the user is notified.

If I uncomment the following code, the panel is never updated, the user can't tell if the Excel file has been created, eventhough it was:

 DataTable myDataTable = SourceData_Build();
 myDataset.Tables.Add(myDataTable );

Again, sorry for the first snippet, I've spent waaaayy too much time on this..

Thanks again for your time.

 

[jmeckley]

the problem is the catch block. drop the return statement. this is not executing the remaining code. sample below

protected void btnDownload_Click(object sender, System.EventArgs e)
{
   MessageBox.Text = "";
   string result = "0";

   if (!ValidateInput())
   {
   }
   else
   {
      try
      {
         DataSet myDataset2 = new DataSet();
         myDataset2.Tables.Add(SourceData_Build2());      
            
         string FileLocation = @"\\blfsrocrdc02\roc_rd\public\WebReports\";
         string FileExtenstion = ".xls";

         dc.ConvertDataSetToExcel(myDataset2, @FileLocation, REPORT_NAME.ToString());

         H1.Visible = true;
         H1.NavigateUrl = "[URL unfurl="true"]http://camsreports/downloads/"[/URL] + REPORT_NAME.ToString() + FileExtenstion;

         lblTimeStamp.Text = "Report Downloaded: " + System.DateTime.Now.ToLongDateString() + " at " + System.DateTime.Now.ToShortTimeString();
     }             
     catch (Exception ex)
     {
        //more detail than Message
        MessageBox.Text = ex.ToString(); 
     }           
     btnDownload.Enabled = false;
  }
  tblData.Visible = true;
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fiat2]

Thanks again for the reply...and simplifying my code.

The only problem:
If I change

myDataset2.Tables.Add(SourceData_Build2());

to

myDataset2.Tables.Add(SourceData_Build());

The same results occur, the loading panel never gets turned off. The second code snippet [SourceData_Build()] contains all the columns whereas
[SourceData_Build2()] only contains a few.

I'm still researching this and haven't seen anything yet.

Thanks again for your time.
Mike

 

[jmeckley]

step through the code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fiat2]

That is a slight problem...the last function that is being called is the Dispose() in the Report.Master.

All goes well for this function, even when called with the larger DataSet. There does seem to be a timing issue, because, regardless of dataset, if I step through the Dispose() function and into the insert that I have there to log the job, the panel is not updated properly.

This issue seems to be a timing issue. As if the page is timing out. Can that be set?

Thanks again for your time.
Much appreciated.

 

[jmeckley]

yes, however this should be considered a hack. extending the timeout of a request can lead to performance issues if this is a high traffic site. the problem isn't the page. the problem is either 1. the sql query is slow or 2. the export process is slow.

I'm willing to bet the problem is the query (20 rows w/ 200 columns). can you post the query? something may jump out as a performance issue. also check your indexes. at a minimum I use a clustered index on all PKs and a nonclusted index on FK columns. after that you may want to fine tune the criteria fields. This begins the art indexing.

As for the timeout hack; google "webforms increase pagetimeout" or "asp.net increase page timeout". there should be some info on the web about this.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fiat2]

Below is the view that is providing the data for the report.
When executing the query with the parameters that is being passed in, it executes in about 1 minute.

CREATE OR REPLACE FORCE VIEW CIDDEV.VW_RDE_ZEUS_LDS
(
   FACILITY,
   PROTOCOLID,
   MOLOTID,
   MONOMER_LOTID,
   MIX_DATE,
   RD_NUMBER,
   MONOMER_FAMILY,
   ANTTOOL,
   ANTTOOL_D1_NOM,
   ANTTOOL_D1_ACT,
   ANTTOOL_D2_NOM,
   ANTTOOL_D2_ACT,
   ANTTOOL_D3_NOM,
   ANTTOOL_D3_ACT,
   ANTTOOL_EH_BLEND_RAD_NOM,
   ANTTOOL_EH_BLEND_RAD_ACT,
   ANTTOOL_OZ_SHAPE_FACT_NOM,
   ANTTOOL_OZ_SHAPE_FACT_ACT,
   ANTTOOL_OPT_TOOL_SAG_NOM,
   ANTTOOL_OPT_TOOL_SAG_ACT,
   ANTTOOL_PIN_DIAMETER_NOM,
   ANTTOOL_PIN_DIAMETER_ACT,
   ANTTOOL_R1_NOM,
   ANTTOOL_R1_MAJOR_AXIS_ACT,
   ANTTOOL_R1_MINOR_AXIS_ACT,
   ANTTOOL_R2_NOM,
   ANTTOOL_R2_ACT,
   ANTTOOL_R3_NOM,
   ANTTOOL_R3_ACT,
   ANTTOOL_S1_NOM,
   ANTTOOL_S1_ACT,
   ANTTOOL_S2_NOM,
   ANTTOOL_S2_ACT,
   ANTTOOL_TOTAL_TOOL_SAG_NOM,
   ANTTOOL_TOTAL_TOOL_SAG_ACT,
   ANTTOOL_UE_HEIGHT_NOM,
   ANTTOOL_UE_HEIGHT_ACT,
   MOLD_LOTID,
   POSTTOOL,
   POSTTOOL_D1_NOM,
   POSTTOOL_D1_ACT,
   POSTTOOL_D2_NOM,
   POSTTOOL_D2_ACT,
   POSTTOOL_D3_NOM,
   POSTTOOL_D3_ACT,
   POSTTOOL_EH_BLEND_RAD_NOM,
   POSTTOOL_EH_BLEND_RAD_ACT,
   POSTTOOL_OZ_SHAPE_FACT_NOM,
   POSTTOOL_OZ_SHAPE_FACT_ACT,
   POSTTOOL_OPT_TOOL_SAG_NOM,
   POSTTOOL_OPT_TOOL_SAG_ACT,
   POSTTOOL_PIN_DIAMETER_NOM,
   POSTTOOL_PIN_DIAMETER_ACT,
   POSTTOOL_R1_NOM,
   POSTTOOL_R1_MAJOR_AXIS_ACT,
   POSTTOOL_R1_MINOR_AXIS_ACT,
   POSTTOOL_R2_NOM,
   POSTTOOL_R2_ACT,
   POSTTOOL_R3_NOM,
   POSTTOOL_R3_ACT,
   POSTTOOL_S1_NOM,
   POSTTOOL_S1_ACT,
   POSTTOOL_S2_NOM,
   POSTTOOL_S2_ACT,
   POSTTOOL_TOTAL_SAG_NOM,
   POSTTOOL_TOTAL_SAG_ACT,
   POSTTOOL_UE_HEIGHT_NOM,
   POSTTOOL_UE_HEIGHT_ACT,
   POSTTOOL_WORK_SAG_NOM,
   POSTTOOL_WORK_SAG_ACT,
   POSTTOOL_WORK_SAG_DIA_NOM,
   POSTTOOL_WORK_SAG_DIA_ACT,
   CASTING_UNIT,
   CASTING_QTY_IN,
   CASTING_QTY_OUT,
   CASTING_DATE,
   CASTING_OPERATOR,
   CURE_UNIT,
   AXIS_ALIGNMENT_TIME,
   INJECT_VOLUME,
   CAP_FORCE,
   CLAMP_FORCE,
   LAMP_CHOICE,
   INTENSITY,
   CYCLE_TIME,
   CURE_RECIPE,
   HOLD_TIME,
   CURE_O2_COUNT,
   DECAP_QTY_IN,
   DECAP_QTY_OUT,
   DECAP_DATE,
   DECAP_OPERATOR,
   NUM_LENSES_ON_POST,
   NUM_LENSES_ON_RESERVOIR,
   RESERVOIR_REM_QTY_IN,
   RESERVOIR_REM_QTY_OUT,
   RESERVOIR_REM_DATE,
   RESERVOIR_REM_OPERATOR,
   WET_RELEASE_QTY_IN,
   WET_RELEASE_QTY_OUT,
   WET_RELEASE_DATE,
   WET_RELEASE_OPERATOR,
   SRO_QTY_IN,
   SRO_QTY_OUT,
   SRO_DATE,
   SRO_OPERATOR,
   SRO_TIME_IN,
   SRO_TEMP,
   SRO_TIME_OUT,
   DRY_RELEASE_QTY_IN,
   DRY_RELEASE_QTY_OUT,
   DRY_RELEASE_DATE,
   DRY_RELEASE_OPERATOR,
   DRY_RELEASE_QTY_NOT_RELEASE,
   DRY_RELEASE_QTY_DAMAGE,
   EXTRACTION_METHOD,
   EXTRACTION_LOCATION,
   EXTRACTION_QTY_IN,
   EXTRACTION_QTY_OUT,
   EXTRACTION_DATE,
   EXTRACTION_OPERATOR,
   HYDRATION_1_SOLUTION,
   HYDRATION_1_TIME,
   HYDRATION_2_SOLUTION,
   HYDRATION_2_TIME,
   HYDRATION_3_SOLUTION,
   HYDRATION_3_TIME,
   HYDRATION_4_SOLUTION,
   HYDRATION_4_TIME,
   HYDRATION_5_SOLUTION,
   HYDRATION_5_TIME,
   HYDRATION_6_SOLUTION,
   HYDRATION_6_TIME,
   COSM_INSP_QTY_IN,
   COSM_INSP_QTY_OUT,
   COSM_INSP_DATE,
   COSM_INSP_OPERATOR,
   TOTAL_DEFECTS,
   PACKAGING_MACHINE,
   PACKAGING_SOLUTION_TYPE,
   PACKAGING_SOLUTION_PH,
   PACKAGING_SOLUTION_OSMOLALITY,
   PACKAGING_FILL_VOLUME,
   PACKAGING_DATE,
   BLISTER_INSP_QTY_IN,
   BLISTER_INSP_QTY_OUT,
   BLISTER_INSP_DATE,
   BLISTER_INSP_OPERATOR,
   STERIL_QTY_IN,
   STERIL_QTY_OUT,
   STERIL_DATE,
   STERIL_OPERATOR,
   STERILIZER,
   POST_STERIL_BLISTER_QTY_IN,
   POST_STERIL_BLISTER_QTY_OUT,
   POST_STERIL_BLISTER_DATE,
   POST_STERIL_BLISTER_OPERATOR,
   DIAMETER_SAG_QTY_IN,
   DIAMETER_SAG_QTY_OUT,
   DIAMETER_SAG_DATE,
   DIAMETER_SAG_OPERATOR,
   DIAMETER_SAG_SOLUTION,
   DIAMETER_SAG_TEMPERATURE,
   DIAMETER_AVG,
   DIAMETER_STD,
   SAG_AVG,
   SAG_STD,
   WARPAGE_AVG,
   WARPAGE_STD,
   CT_QTY_IN,
   CT_QTY_OUT,
   CT_DATE,
   CT_OPERATOR,
   CT_AVG,
   CT_STD,
   POWER_QTY_IN,
   POWER_QTY_OUT,
   POWER_DATE,
   POWER_OPERATOR,
   SPHERE_AVG,
   SPHERE_STD,
   IMAGEQUALITY_AVG,
   IMAGEQUALITY_STD
)
AS
     SELECT   'RDE' AS facility,
              ps.protocolid,
              ps.molotid,
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MONOMER',
                                        'Lot Info')
                 AS "Monomer LotID",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MONOMER',
                                        'Mix Date')
                 AS "Mix Date",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MONOMER',
                                        'RD#')
                 AS "RD Number",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MONOMER',
                                        'Type (Family)')
                 AS "Monomer Family",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MOLDING',
                                        'Anterior Tool')
                 AS "Ant Tool",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'D1')
                 AS "Ant Tool D1 Nom",
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'D1')
                 AS "Ant Tool D1 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'D2')
                 AS "Ant Tool D2 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'D2')
                 AS "Ant Tool D2 Act",
              '' AS "Ant Tool D3 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'D3')
                 AS "Ant Tool D3 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'EH Blend Radius')
                 AS "Ant Tool EH Blend Rad Nom",                            --
              '' AS "Ant Tool EH Blend Rad Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'Optic Zone Shape Factor')
                 AS "Ant Tool OZ Shape Fact Nom",                           --
              '' AS "Ant Tool OZ Shape Fact Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'Optical Tool Sag')
                 AS "Ant Tool Optical Tool Sag Nom",                        --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'Optical_Tool_Sag')
                 AS "Ant Tool Optical Tool Sag Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'Pin Diameter')
                 AS "Ant Tool Pin Diameter Nom",                            --
              '' AS "Ant Tool Pin Diameter Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'R1')
                 AS "Ant Tool R1 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'R1_Major_Axis')
                 AS "Ant Tool R1 Major Axis Act",
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'R1_Minor_Axis')
                 AS "Ant Tool R1 Minor Axis Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'R2')
                 AS "Ant Tool R2 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'R2')
                 AS "Ant Tool R2 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'R3')
                 AS "Ant Tool R3 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'R3')
                 AS "Ant Tool R3 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'S1')
                 AS "Ant Tool S1 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'S1')
                 AS "Ant Tool S1 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'S2')
                 AS "Ant Tool S2 Nom",                                      --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'S2')
                 AS "Ant Tool S2 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'Total Tool sag')
                 AS "Ant Tool Total Sag Nom",                               --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'Total_Tool_Sag')
                 AS "Ant Tool Total Sag Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Anterior Tool',
                                       'Unblended Edge Height')
                 AS "Ant Tool UE Height Nom",                               --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Anterior Tool',
                                      'Unblended_Edge_Height')
                 AS "Ant Tool UE Height Act",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MOLDING',
                                        'Anterior Mold Lot ID')
                 AS "Mold Lot ID",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'MOLDING',
                                        'Posterior Tool')
                 AS "Post Tool",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'D1')
                 AS "Post Tool D1 Nom",
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'D1')
                 AS "Post Tool D1 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'D2')
                 AS "Post Tool D2 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'D2')
                 AS "Post Tool D2 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'D3')
                 AS "Post Tool D3 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'D3')
                 AS "Post Tool D3 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'EH Blend Radius')
                 AS "Post Tool EH Blend Rad Nom",                           --
              '' AS "Post Tool EH Blend Rad Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'OZ Shape Factor')
                 AS "Post Tool OZ Shape Fact Nom",                          --
              '' AS "Post Tool OZ Shape Fact Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Optical Tool Sag')
                 AS "Post Tool Optical Tool Sag Nom",                       --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'Optical_Tool_Sag')
                 AS "Post Tool Optical Tool Sag Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Pin Diameter')
                 AS "Post Tool Pin Diameter Nom",                           --
              '' AS "Post Tool Pin Diameter Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'R1')
                 AS "Post Tool R1 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'R1_Major_Axis')
                 AS "Post Tool R1 Major Axis Act",
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'R1_Minor_Axis')
                 AS "Post Tool R1 Minor Axis Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'R2')
                 AS "Post Tool R2 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'R2')
                 AS "Post Tool R2 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'R3')
                 AS "Post Tool R3 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'R3')
                 AS "Post Tool R3 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'S1')
                 AS "Post Tool S1 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'S1')
                 AS "Post Tool S1 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'S2')
                 AS "Post Tool S2 Nom",                                     --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'S2')
                 AS "Post Tool S2 Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Total Sag')
                 AS "Post Tool Total Sag Nom",                              --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'Total_Tool_Sag')
                 AS "Post Tool Total Sag Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Unblended Edge Height')
                 AS "Post Tool UE Height Nom",                              --
              fn_bl_get_tool_actuals (ps.protocolid,
                                      ps.molotid,
                                      'Posterior Tool',
                                      'Unblended_Edge_Height')
                 AS "Post Tool UE Height Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Working Sag')
                 AS "Post Tool Working Sag Nom",                            --
              '' AS "Post Tool Working Sag Act",
              fn_bl_get_tool_nominals (ps.protocolid,
                                       ps.molotid,
                                       'Posterior Tool',
                                       'Working Sag Diameter')
                 AS "Post Tool Work Sag Dia. Nom",                          --
              '' AS "Post Tool Work Sag Dia. Act",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Casting Unit')
                 AS "Casting Unit",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'CASTING / CURING')
                 AS "Casting Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'CASTING / CURING')
                 AS "Casting Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING')
                 AS "Casting Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'CASTING / CURING')
                 AS "Casting Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Cure Unit')
                 AS "Cure Unit",
              '' AS "Axis Alignment Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Inject Volume')
                 AS "Inject Volume",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Capping')
                 AS "Cap Force",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Clamp Force (lbs.)')
                 AS "Clamp Force",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Bulb Type')
                 AS "Lamp Choice",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Intensity Setting')
                 AS "Intensity",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        '')
                 AS "Cycle Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Cure Mode')
                 AS "Cure Recipe",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        'Cure Time')
                 AS "Hold Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'CASTING / CURING',
                                        '')
                 AS "Cure O2 Count",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'Decap')
                 AS "Decap Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'Decap')
                 AS "Decap Qty Out",
              fn_bl_get_operation_date (ps.protocolid, ps.molotid, 'Decap')
                 AS "Decap Date",
              fn_bl_get_operation_operator (ps.protocolid, ps.molotid, 'Decap')
                 AS "Decap Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Lens on Posterior - Qty')
                 AS "# Lenses on Post",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Reservoir on Posterior - Qty')
                 AS "# reservoirs on Post",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'Reservoir Removal')
                 AS "Reservoir Removal Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'Reservoir Removal')
                 AS "Reservoir Removal Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'Reservoir Removal')
                 AS "Reservoir Removal Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'Reservoir Removal')
                 AS "Reservoir Removal Operator",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'Wet Release')
                 AS "Wet Release Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'Wet Release')
                 AS "Wet Release Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'Wet Release')
                 AS "Wet Release Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'Wet Release')
                 AS "Wet Release Operator",
              fn_bl_get_operation_Counts_in (ps.protocolid, ps.molotid, 'SRO')
                 AS "SRO Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid, ps.molotid, 'SRO')
                 AS "SRO Qty Out",
              fn_bl_get_operation_date (ps.protocolid, ps.molotid, 'SRO')
                 AS "SRO Date",
              fn_bl_get_operation_operator (ps.protocolid, ps.molotid, 'SRO')
                 AS "SRO Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Time In')
                 AS "SRO Time In",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Temperature')
                 AS "SRO Temperature",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Time Out')
                 AS "SRO Time Out",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'Dry Release')
                 AS "Dry Release Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'Dry Release')
                 AS "Dry Release Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'Dry Release')
                 AS "Dry Release Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'Dry Release')
                 AS "Dry Release Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Qty not released')
                 AS "Dry Release Qty Not Released",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'DDRV',
                                        'Qty damaged')
                 AS "Dry Release Qty Damaged",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Method')
                 AS "Extraction Method",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Location')
                 AS "Extraction Location",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'EXTRACTION')
                 AS "Extraction Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'EXTRACTION')
                 AS "Extraction Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'EXTRACTION')
                 AS "Extraction Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'EXTRACTION')
                 AS "Extraction Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 1 Solution')
                 AS "Hydration 1 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 1 Cycle Time (sec)')
                 AS "Hydration 1 Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 2 Solution')
                 AS "Hydration 2 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 2 Cycle Time (sec)')
                 AS "Hydration 2 Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 3 Solution')
                 AS "Hydration 3 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 3 Cycle Time (sec)')
                 AS "Hydration 3 Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 4 Solution')
                 AS "Hydration 4 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 4 Cycle Time (sec)')
                 AS "Hydration 4 Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 5 Solution')
                 AS "Hydration 5 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 5 Cycle Time (sec)')
                 AS "Hydration 5 Time",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 6 Solution')
                 AS "Hydration 6 Solution",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Extraction',
                                        'Hydration 6 Cycle Time (sec)')
                 AS "Hydration 6 Time",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'INSPECTION')
                 AS "Cosmetic Insp Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'INSPECTION')
                 AS "Cosmetic Insp Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'INSPECTION')
                 AS "Cosmetic Insp Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'INSPECTION')
                 AS "Cosmetic Insp Operator",
              di.total_defects AS "Total Defects",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Packaging',
                                        'Machine #')
                 AS "Packaging Machine",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Packaging',
                                        'Solution Type')
                 AS "Solution Type",
              '' AS "Solution pH",
              '' AS "Solution Osmolality",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'Packaging',
                                        'Fill Volume')
                 AS "Fill Volume",
              fn_bl_get_operation_date (ps.protocolid, ps.molotid, 'PACKAGING')
                 AS "Packaging Date",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'BLISTER INSPECTION')
                 AS "Blister Inspection Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'BLISTER INSPECTION')
                 AS "Blister Inspection Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'BLISTER INSPECTION')
                 AS "Blister Inspection Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'BLISTER INSPECTION')
                 AS "Blister Inspection Operator",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'STERILIZATION')
                 AS "Sterilization Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'STERILIZATION')
                 AS "Sterilization Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'STERILIZATION')
                 AS "Sterilization Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'STERILIZATION')
                 AS "Sterilization Operator",
              fn_bl_get_operation_info (ps.protocolid,
                                        ps.molotid,
                                        'STERILIZATION',
                                        'Sterilizer')
                 AS "Sterilizer",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'POST STER BLISTER INSPECTION')
                 AS "Post Steril Blister Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'POST STER BLISTER INSPECTION')
                 AS "Post Steril Blister Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'POST STER BLISTER INSPECTION')
                 AS "Post Steril Blister Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'POST STER BLISTER INSPECTION')
                 AS "Post Steril Blister Operator",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'DIAMETER / SAG')
                 AS "Diameter / Sag Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'DIAMETER / SAG')
                 AS "Diameter / Sag Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'DIAMETER / SAG')
                 AS "Diameter / Sag Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'DIAMETER / SAG')
                 AS "Diameter / Sag Operator",
              '' AS "Solution",
              '' AS "Measurement Temp",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'Diameter')
                 AS "Diameter AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'Diameter')
                 AS "Diameter STD",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'Sag') AS "SAG AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'Sag') AS "SAG STD",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'Warpage')
                 AS "Warpage AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'Warpage')
                 AS "Warpage STD",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'CENTER THICKNESS')
                 AS "CT Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'CENTER THICKNESS')
                 AS "CT Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'CENTER THICKNESS')
                 AS "CT Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'CENTER THICKNESS')
                 AS "CT Operator",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'CT') AS "CT AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'CT') AS "CT STD",
              fn_bl_get_operation_Counts_in (ps.protocolid,
                                             ps.molotid,
                                             'POWER INSPECTION')
                 AS "Power Qty In",
              fn_bl_get_operation_Counts_out (ps.protocolid,
                                              ps.molotid,
                                              'POWER INSPECTION')
                 AS "Power Qty Out",
              fn_bl_get_operation_date (ps.protocolid,
                                        ps.molotid,
                                        'POWER INSPECTION')
                 AS "Power Date",
              fn_bl_get_operation_operator (ps.protocolid,
                                            ps.molotid,
                                            'POWER INSPECTION')
                 AS "Power Operator",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'Sphere Power')
                 AS "Sphere Power AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'Sphere Power')
                 AS "Sphere Power STD",
              GET_LDS_AVG (ps.protocolid, ps.molotid, 'Image Quality')
                 AS "Image Quality AVG",
              GET_LDS_STD (ps.protocolid, ps.molotid, 'Image Quality')
                 AS "Image Quality STD"
       FROM   mv_pro_summary ps, mv_def_info di
      WHERE   ps.protocolid = di.protocolid AND ps.molotid = di.molotid
   ORDER BY   PS.PROTOCOLID ASC, ps.molotid ASC;

 

[jbenson001]

One minute is way to long to expect a user to wait for data to return. Limit the number of rows and definately limit the number of columns.

 

[jmeckley]

that's horrendous!, but then again 200 columns.

the first issue I would raise is why all the functions? There
's a good chance this is logic, and as such belongs in code, not the database.

the second issue. I only see
create view ...
as
select ...

i don't see the from, where, etc. clauses.

It also appears the database is not normalized, however without more information on the sql I can't say for sure.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[fiat2]

jbenson001--
Well, that depends. If the report that the user is running will only be ran once a week. One minute is really not that big of a deal.

The number of rows is not the issue. I'm only returning, on average, 10.

The number of columns cannot be changed. This report will report on data that is to assist in developing a product...the users need the data.

I may start looking into splitting the data into different data tables to provide on different worksheets.

jmeckley---
Not really logic, I'm transposing data. It should be done via procedures but I quickly created the functions. The reason you don't see the from/where statement is due to the size of the post. I didn't notice when I posted.
Our DBAs tell me my database is between 2NF and 3NF.

 

[jmeckley]

If the report that the user is running will only be ran once a week. One minute is really not that big of a deal.

true, but when the user wants the report, they want it. they don't want to wait for it. And during this time you are tying up resources. what if 10 people access this report at the same time? this has the potential to create problems.

if this is acceptable, then you may want to consider this a long running task. in which case it should be executed independent of the UI. UI could kick it off, but wouldn't wait for it.

The number of columns cannot be changed.

that's the final destination, not the source.

for example you have a cross tab. I have 2 choices

select
    column,
    sum(case when x then y else z end) as ...,
    sum(case when x then y else z end) as ...
from
    ...

or I can get the data and transpose in memory

select
    column,
    x,
    y,
    z
from ....

then pivot in code

the query will take seconds instead of minutes and now you have the power of OOP to transpose the data.

in your case you could query say 1000 rows of 50 columns instead of 10 rows of 200 columns.

I'm also no longer a fan of procs/functions in the database. the database is persistence, nothing more. keep the logic where it belongs, in the domain.

here are some crude, manual perf tests.
1. execute the query directly against the database. and time it.
2. execute the query from .net and time it.
3. create some fake data (200 columns, 10 rows) and export using your exporter. time it.
this will tell you where your bottleneck is. all that's left is solving it.

you could also use Sql Profiler (assuming sql server) and dotTrace from JetBrain to quantity the problem.

as a comparison, mod your sql to pull back just the raw data (no function calls or anything) and time how long that takes. if the execution time is drastically reduced, then move the db functions into code. transform in memory and export.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

the database is persistence, nothing more. keep the logic where it belongs, in the domain.

Are we going to go down that road again? LOL