Kamal Khaleefa
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
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
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 ?
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:
And Here is the VB Code
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>
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
ASKER
@mlmcc
here is the sql
and here is the functions i write in Record Selection formula editor
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"
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
)
If you changed the filter to just use UserID and just did the filter as
{V_LogDetails_Auditing.Use r_ID}={?Us erID}
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
{V_LogDetails_Auditing.Use
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for all
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