Link to home
Start Free TrialLog in
Avatar of Kamal Khaleefa
Kamal KhaleefaFlag for Kuwait

asked on

Crystal reports load very very slow in asp.net

Dear all

Im using crystal reports with vs2010
I have a web application

I have more than 100k record in the data base
Hence when report is generated it takes more than 3-5 minutes

Is there any solution ?
or to do it Asynchronously
Avatar of Mike McCracken
Mike McCracken

Speed of rendering is controlled by many things.

I assume you have a filter on the records.
What is the filter?

One thing to check is the SQL the report uses.
Click DATABASE
SHOW SQL

You may find that some or all of the filter is not passed to the database.
This happens if you are using Crystal functions in the filtering.

Does the report have a subreport?

mlmcc
The report will be slow if you print 100k records on it , but I guess you are not doing that. 100k records is nothing for a database and cannot be a problem if you are just querying. Do you have another datasource in the report , like an excel file ? Do you have subreports ? Missing printers ? How this report is working on your development machine ? What is the report database ?
Avatar of Kamal Khaleefa

ASKER

Thanks for the Reply

i don't have a subReport. and yes im passing parameters to it.

kindly find here the full source code:

ASPX Page:
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>





<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"  TagPrefix="cc1" %>




<asp:Content ID="CPH1" ContentPlaceHolderID="ContentPlaceHolder1"   runat="server">


     <link rel="stylesheet" href="../../css/style.css">

   

<asp:UpdatePanel ID="UpdatePanel1" runat="server"   >
      <Triggers>
        <asp:PostBackTrigger ControlID="CrystalReportViewer1" />
        <asp:PostBackTrigger ControlID="btnSearchCards" />
        
        
        </Triggers>
  <ContentTemplate>


  <div class="wrap clearfix container"  role="main">
  <h1 align="right" >My Report</h1>
  
  
       





     <table width="100%" class="TblForm"  style="  background-color:White; padding:0px 5px 5px 5px;" >
                                
                                                  
                                                      <tr>
                                                         
                                                    
                                              <td colspan="2"  align="right"  >
                                                 
                                                    
  	<div class="CntCream">
  


 <div class="TwoShade clearfix">
    
     
    <table class="TblForm" width="100%" border="0" cellspacing="0" cellpadding="0">
     <tr>
  
  <td>  <asp:Label ID="Label60" runat="server"  CssClass="FieldName" Text="Key Search"></asp:Label></td>
    <td colspan="2" width="35%">  

    
        <asp:TextBox ID="txtKeySearch"   CssClass="Fmedium"   runat="server"></asp:TextBox>
         
        </td>


  <td align="center">  <asp:Label ID="Label62" runat="server"  CssClass="FieldName" Text="Operation Type "></asp:Label></td>
    <td>  
        <asp:DropDownList ID="ddlOperation" runat="server"></asp:DropDownList>
                  
         </td>

      <td align="center">
      <asp:Label ID="Label3" runat="server"  CssClass="FieldName" Text="User Name"></asp:Label>
  </td>
    <td align="center">
       <asp:DropDownList ID="ddlUser" runat="server"></asp:DropDownList>
  </td>
  </tr>
   

  <tr>
  
  <td><asp:Label ID="Label1" runat="server"  CssClass="FieldName" Text=" Period of work"></asp:Label></td>
    <td colspan="2">
    
     

     <table>
    <tr>
    
    <td>  <asp:Label ID="label87" CssClass="FieldName" runat="server" Text="From:" 
                                                                             ToolTip="From"></asp:Label></td>
      <td> <asp:TextBox Width="80px" ID="txtDateFrom" runat="server"     CssClass="Fmedium"  ></asp:TextBox>
                                                                            <img ID="calImg1" runat="server" alt="" height="20" 
                                                                                src="~/Images/Index-copy_14.png" width="20" align="bottom" />
         <cc1:CalendarExtender ID="CalendarExtender1" CssClass="MyCalendar" PopupPosition="TopLeft" runat="server" PopupButtonID="calImg1" TargetControlID="txtDateFrom" Format="dd/MM/yyyy">
        </cc1:CalendarExtender>        
                                                                                
                                                                                </td>
        <td>  <asp:Label ID="Label4"  CssClass="FieldName"  runat="server" 
                                                                            Text="To:" ToolTip="To"></asp:Label></td>
          <td>  <asp:TextBox Width="80px" ID="txtDateTo" runat="server" CssClass="Fmedium"  
                                                                                   ></asp:TextBox>
                                                                              <img ID="Img1" runat="server" alt="" height="20" src="~/Images/Index-copy_14.png" 
                                                                                  width="20" align="bottom" />
                     <cc1:CalendarExtender ID="CalendarExtender2" CssClass="MyCalendar" PopupPosition="TopRight" runat="server" PopupButtonID="Img1" TargetControlID="txtDateTo" Format="dd/MM/yyyy">
        </cc1:CalendarExtender>        
                                   
                                                                                  </td>
    </tr>
    </table>

    </td>
      <td><asp:Label ID="Label2" runat="server"  CssClass="FieldName" Text="Work Location "></asp:Label></td>
        <td> <asp:DropDownList ID="ddlSection" runat="server"></asp:DropDownList>
        </td>
          
            <td> <asp:Button ID="btnSearchCards" runat="server" Text="View Report" 
              CssClass="button white small" /></td><td></td>
  </tr>
 
  
   
  </table>
  
  
  </div>
  
  	</div>      
  
  
  
  
  
  
  
  
  
   
  
  

                                                      </td>
                                                    </tr>


 


                                               
                                                    
                                       
                                         
                                </table> 
       </div>
         <div  align="center">
    
     
    <table  width="100%" border="0" cellspacing="0" cellpadding="0">
  
  
    <tr>
    <td align="center" colspan="6">
    
        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"        PrintMode="ActiveX"
            AutoDataBind="true" BestFitPage="false" Height="1000px" Width="1100" EnableParameterPrompt="False" 
            ToolPanelView="None"   /> 
    </td>
    
    </tr>
    
 
    </table></div>
        </ContentTemplate>
  
  </asp:UpdatePanel>
</asp:Content>

Open in new window



And Here is the VB Code

Imports CrystalDecisions.CrystalReports.Engine
'Imports Microsoft.Reporting.WebForms
Imports CrystalDecisions.Web
Imports CrystalDecisions.Shared
Imports System.Globalization
Public Class AuditingDetailsReport
    Inherits System.Web.UI.Page

    Private Sub AuditingDetailsReport_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        LoadReportsDefaultValues()


    End Sub
    Private Sub CrystalReportViewer1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Init
        CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None
        CrystalReportViewer1.HasToggleGroupTreeButton = False
        CrystalReportViewer1.HasToggleParameterPanelButton = False



        CrystalReportViewer1.HasCrystalLogo = False
        CrystalReportViewer1.HasDrilldownTabs = False
        CrystalReportViewer1.HasDrillUpButton = False
    End Sub

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
  Session("txtFromTGPAudit") = Nothing
            Session("txtToTGPAudit") = Nothing
            Session("SectionIDAudit") = Nothing
            Session("OperationIDAudit") = Nothing
            Session("UserIDAudit") = Nothing
            Session("KeySearchAudit") = Nothing

            txtDateFrom.Text = CDate(Date.Today.AddMonths(-1)).ToString("dd/MM/yyyy")
            txtDateTo.Text = CDate(Date.Today).ToString("dd/MM/yyyy")
            FillOperation()
            FillSections()
            FillUsers()


        End If
    End Sub

Sub LoadReportsDefaultValues()




        Dim oSQLConnection As System.Data.SqlClient.SqlConnection = (New DBConnection).StartConnection
        Try
            oSQLConnection.Open()

            Dim cnnInfo As New CrystalDecisions.Shared.ConnectionInfo()

            cnnInfo.DatabaseName = DataBaseInformation.DBName
            cnnInfo.ServerName = DataBaseInformation.Server
            cnnInfo.UserID = DataBaseInformation.UserName
            cnnInfo.Password = DataBaseInformation.Password


            Dim cryRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument


            Dim FileName As String
            FileName = Server.MapPath("AuditingDetails.rpt")
            cryRpt.Load(FileName)


            Dim crtable As Table

            Dim crtablelogoninfo As TableLogOnInfo

            For Each crtable In cryRpt.Database.Tables
                crtablelogoninfo = crtable.LogOnInfo
                crtablelogoninfo.ConnectionInfo = cnnInfo
                crtable.ApplyLogOnInfo(crtablelogoninfo)

            Next
            ' cryRpt.SetDatabaseLogon("PassUser", "P@ssw0rd", "MCSAPPTB", "PassSystem")

            'Dim tableLogOnInfos As CrystalDecisions.Shared.TableLogOnInfos = CrystalReportViewer1.LogOnInfo
            ' tableLogOnInfos = 


            'For Each tableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo In tableLogOnInfos
            '    tableLogOnInfo.ConnectionInfo = cnnInfo

            'Next
            Dim crParameterDiscreteValue As ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As ParameterValues

            crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields


            crParameterFieldLocation = crParameterFieldDefinitions.Item("SectionID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Session("SectionIDAudit") Is Nothing Then
                crParameterDiscreteValue.Value = -1
            Else
                crParameterDiscreteValue.Value = Session("SectionIDAudit")
            End If

            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)



            crParameterFieldLocation = crParameterFieldDefinitions.Item("OperationID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Session("OperationIDAudit") Is Nothing Then
                crParameterDiscreteValue.Value = -1
            Else
                crParameterDiscreteValue.Value = Session("OperationIDAudit")
            End If

            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)



            crParameterFieldLocation = crParameterFieldDefinitions.Item("KeySearch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

            If Session("KeySearchAudit") Is Nothing Then

                crParameterDiscreteValue.Value = "-1"
            Else
                crParameterDiscreteValue.Value = Session("KeySearchAudit")
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            crParameterFieldLocation = crParameterFieldDefinitions.Item("UserID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

            If Session("UserIDAudit") Is Nothing Then

                crParameterDiscreteValue.Value = -1
            Else
                crParameterDiscreteValue.Value = Session("UserIDAudit")
            End If

            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)










            crParameterFieldLocation = crParameterFieldDefinitions.Item("DateFrom")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

            If Session("txtFromTGPAudit") Is Nothing Then
                '  txtDateFrom.Text = CDate(Date.Today.AddMonths(-1)).ToString("dd/MM/yyyy")
                ' txtDateTo.Text = CDate(Date.Today).ToString("dd/MM/yyyy")
                crParameterDiscreteValue.Value = CDate(Date.Today.AddMonths(-1)) ' CDate("01/01/1900")
            Else


                crParameterDiscreteValue.Value = CDate(Session("txtFromTGPAudit"))

            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)





            crParameterFieldLocation = crParameterFieldDefinitions.Item("DateTo")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Session("txtToTGPAudit") Is Nothing Then

                crParameterDiscreteValue.Value = CDate(Date.Today) ' CDate("01/01/1900")
            Else
                crParameterDiscreteValue.Value = CDate(Session("txtToTGPAudit"))
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)






            CrystalReportViewer1.ReportSource = cryRpt

            CrystalReportViewer1.DataBind()

            ' cryRpt.Dispose()
            

        Catch ex As Exception

        Finally
            Try
                oSQLConnection.Close()
            Catch ex As Exception

            End Try
        End Try
    End Sub
    Sub LoadReports()




        Dim oSQLConnection As System.Data.SqlClient.SqlConnection = (New DBConnection).StartConnection
        Try
            oSQLConnection.Open()

            Dim cnnInfo As New CrystalDecisions.Shared.ConnectionInfo()

            cnnInfo.DatabaseName = DataBaseInformation.DBName
            cnnInfo.ServerName = DataBaseInformation.Server
            cnnInfo.UserID = DataBaseInformation.UserName
            cnnInfo.Password = DataBaseInformation.Password



            Dim cryRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument


            Dim FileName As String
            FileName = Server.MapPath("AuditingDetails.rpt")
            cryRpt.Load(FileName)


            Dim crtable As Table

            Dim crtablelogoninfo As TableLogOnInfo

            For Each crtable In cryRpt.Database.Tables
                crtablelogoninfo = crtable.LogOnInfo
                crtablelogoninfo.ConnectionInfo = cnnInfo
                crtable.ApplyLogOnInfo(crtablelogoninfo)

            Next
            ' cryRpt.SetDatabaseLogon("PassUser", "P@ssw0rd", "MCSAPPTB", "PassSystem")

            'Dim tableLogOnInfos As CrystalDecisions.Shared.TableLogOnInfos = CrystalReportViewer1.LogOnInfo
            ' tableLogOnInfos = 


            'For Each tableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo In tableLogOnInfos
            '    tableLogOnInfo.ConnectionInfo = cnnInfo

            'Next



            'Dim crParameterDiscreteValue As ParameterDiscreteValue
            'Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            'Dim crParameterFieldLocation As ParameterFieldDefinition
            'Dim crParameterValues As ParameterValues

            'crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields





            Dim crParameterDiscreteValue As ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As ParameterValues

            crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields










            crParameterFieldLocation = crParameterFieldDefinitions.Item("SectionID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ddlSection.SelectedValue
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)




            crParameterFieldLocation = crParameterFieldDefinitions.Item("OperationID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ddlOperation.SelectedValue
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            crParameterFieldLocation = crParameterFieldDefinitions.Item("UserID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ddlUser.SelectedValue
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)







            crParameterFieldLocation = crParameterFieldDefinitions.Item("DateFrom")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

            If txtDateFrom.Text.Trim = "" Then

                crParameterDiscreteValue.Value = CDate("01/01/1900")
            Else
                crParameterDiscreteValue.Value = Date.ParseExact(txtDateFrom.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture) '' CDate(txtDateFrom.Text)
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)



            crParameterFieldLocation = crParameterFieldDefinitions.Item("KeySearch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

            If txtKeySearch.Text.Trim = "" Then

                crParameterDiscreteValue.Value = "-1"
            Else
                crParameterDiscreteValue.Value = txtKeySearch.Text
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)







            crParameterFieldLocation = crParameterFieldDefinitions.Item("DateTo")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If txtDateTo.Text.Trim = "" Then

                crParameterDiscreteValue.Value = CDate("01/01/1900")
            Else
                crParameterDiscreteValue.Value = Date.ParseExact(txtDateTo.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture) ' CDate(txtDateTo.Text)
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)





            CrystalReportViewer1.ReportSource = cryRpt

            CrystalReportViewer1.DataBind()

            '   cryRpt.Dispose()



        Catch ex As Exception

        Finally
            Try
                oSQLConnection.Close()
            Catch ex As Exception

            End Try
        End Try
    End Sub



    Protected Sub btnSearchCards_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearchCards.Click

        Session("txtFromTGPAudit") = Date.ParseExact(txtDateFrom.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture)
        Session("txtToTGPAudit") = Date.ParseExact(txtDateTo.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture)
        Session("SectionIDAudit") = ddlSection.SelectedValue
        Session("OperationIDAudit") = ddlOperation.SelectedValue
        Session("UserIDAudit") = ddlUser.SelectedValue
        If txtKeySearch.Text.Trim = "" Then
            Session("KeySearchAudit") = Nothing
        Else
            Session("KeySearchAudit") = txtKeySearch.Text
        End If


        LoadReports()
    End Sub

Open in new window

@mlmcc

here is the sql

 SELECT "V_LogDetails_Auditing"."DisplayName", "V_LogDetails_Auditing"."comment", "V_LogDetails_Auditing"."Operation_Date", "V_LogDetails_Auditing"."Operation_Name", "V_LogDetails_Auditing"."SectionNameArabic", "V_LogDetails_Auditing"."IPAddress", "V_LogDetails_Auditing"."ComputerName", "V_LogDetails_Auditing"."SectionID", "V_LogDetails_Auditing"."OperationID", "V_LogDetails_Auditing"."User_ID"
 FROM   "IDCMSProduction"."dbo"."V_LogDetails_Auditing" "V_LogDetails_Auditing"

Open in new window


and here is the functions i write in Record Selection formula editor
(
if  {?OperationID}<>-1 then   {V_LogDetails_Auditing.OperationID} = {?OperationID}  else true

)
and   
(
if {?DateFrom}<>#01/01/1900# then {V_LogDetails_Auditing.Operation_Date}>={?DateFrom} else true
)
 
and
(
if  {?DateTo}<>#01/01/1900# then {V_LogDetails_Auditing.Operation_Date}<={?DateTo} else true
)

and 
(

if {?KeySearch}<>"-1" then   {?KeySearch} in {V_LogDetails_Auditing.comment}       else true
)

and 
(
if {?SectionID}<>-1 then {V_LogDetails_Auditing.SectionID}={?SectionID} else true
)
and 
(
if  {?UserID}<>-1 then   {V_LogDetails_Auditing.User_ID}={?UserID} else true
)

Open in new window

If you changed the filter to just use UserID and just did the filter as

{V_LogDetails_Auditing.User_ID}={?UserID}

Does it take 3-5 minutes to load?

Are the UserID, SectionID, and OperationID indexed fields?

Are the date fields dates or datetimes?

You will probably get a speed increase if you change the order of the tests so the KeySearch is last.

The order should be the 3 ID fields first then the date and last the keysearch

Since you have the VS2010 version have you considered using optional parameters?

mlmcc
The SQL that you posted was from "Show SQL Query"?  What parameter values did you enter when you did that?

 The SQL doesn't include any of the tests from your record selection formula, which either means that CR isn't passing the tests to the server for some reason, or you used the "all" options for all of the parameters ({?OperationID} is -1, {?DateFrom} is #01/01/1900#, etc.), so there were no tests to pass.

 If you just used the "all" options for all of the parameters, try using "Show SQL Query" again and enter a value for each parameter, to see if the tests are included in the SQL.

 James
ASKER CERTIFIED SOLUTION
Avatar of Kamal Khaleefa
Kamal Khaleefa
Flag of Kuwait image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for all