Göran Andersson
5/18/2006 8:18:00 AM
Dispose the DataAdapter and the SqlConnection when you have read the
data into the DataSet.
Specify the fields that you want to fetch from the database, instead of
using * in the query. Remove any fields that you don't need, to reduce
the size of the DataSet.
Lou Civitella wrote:
> Recently a client of ours has moved their web site from one hosting company
> to another. The new host is actually a virtual dedivated server. Today the
> site went down and so did the server due to the fact that a process on the
> server ate up too much memory. I found the process eats alot of memory when
> the client tries to run a Crystal Report from the web site. The process is
> w3wp.exe. When the process is recycled it starts out using @20,000k of
> memory after the client runs the report is maxes out about 90,000k and goes
> down a bit to around 75,000k to 80,000k. When I checked the Application Pool
> recycle it was set at 1740 minutes so I changed it to 60 minutes. I talked
> to tech support of the hosting company and they said that when the sql
> server is being accessed the memory is not being cleared due to the fact
> that there is no code to do this in the web app. I added some code to try
> and fix this but nothing seems to bring the levels back down.
>
> here is my code. What can I add to bring the levels of this process down?
>
> Dim strConnectionString As String = "Data Source=(local);Initial
> Catalog=Data1;Persist Security Info=True;User ID=sa;Password=admin"
>
> Dim strSQL As String = "SELECT * FROM Products WHERE Tag=1 And Active =-1"
>
> Dim conn As New System.Data.SqlClient.SqlConnection(strConnectionString)
>
> Dim report As New ReportDocument()
>
> Dim connection As IConnectionInfo
>
> Dim serverName As String = "(local)"
>
> Dim userID As String = "sa"
>
> Dim password As String = "admin"
>
> Dim DS As New Data.DataSet
>
> Dim DA As New System.Data.SqlClient.SqlDataAdapter(strSQL, conn)
>
>
> DA.SelectCommand.CommandTimeout = 120000
>
> DA.Fill(DS)
>
> report.Load("C:\Inetpub\wwwroot\website\ProductCart\pcadmin\reports\largelabel.rpt")
>
> report.SetDataSource(DS.Tables(0))
>
>
> ' Set Database Logon to main report
>
> For Each connection In report.DataSourceConnections
>
> Select Case connection.ServerName
>
> Case serverName
>
> connection.SetLogon(userID, password)
>
> End Select
>
> Next
>
>
> CrystalReportViewer1.ReportSource = report
>
>
>
> Thanks,
>
> Lou
>
>