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
LVL 16
Kamal KhaleefaInformation Security SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
vastoCommented:
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 ?
0
Kamal KhaleefaInformation Security SpecialistAuthor Commented:
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

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Kamal KhaleefaInformation Security SpecialistAuthor Commented:
@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

0
mlmccCommented:
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
0
James0628Commented:
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
0
Kamal KhaleefaInformation Security SpecialistAuthor Commented:
i change the view in data base
and make most of the operation in crystal reports

in get better
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kamal KhaleefaInformation Security SpecialistAuthor Commented:
thanks for all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.