Time - out problem

[nice_true]

Hi!
I have coded a project in vb.net.
It contains code that retrieves around 15 lakh records from the database and displays them in a grid.
I use data adapter and fill the dataset with it.
Then I specify the dataset as the datagrid's datasource.

Now the problem is that on running the project, the system takes a lot of time and finally the message "the page cannot be displayed" appears.

There is no problem with the queries, I have tested all of them in the query analyzer.

Can anyone help me out? Why is it so?

Is retieving of such a large number of records in the data adapter inappropriate?
If yes, then plz specify a solution.

In case I try to retieve only the top 10 or top 15 records, the project runs fine.

My pc has 512 MB of RAM.

[kb.net]

Hi,

It must be something in your code please paste the code you are using to retrive data and I hope I will be able to help you.

[nice_true]

Hi!

Actually, I do not have the code right now at my disposal, but I would like to tell u something about it

Firstly, there is a main "for loop", which retrieves around 150 records from the database.

This works fine.

Next, inside this loop, I have around 10-12 for loops that work on every value retrieved from the main "for loop" specified above.

I have used the keywords "new" with all variables like command, data adapter, dataset, etc, and have disposed all of them once the for loop is over, so that the memory gets freed up once a for loop gets over.

Now, while tracing the program, it works fine till the first 2-3 loops, then the system gets a bit slower and then a bit more slower, and finally it gets hanged up and the page with the message "the page cannot be displayed." appears.

I am not able to understand why is it so,inspite of having 512 MB f RAM.

[kb.net]

Hi,

ADO.net depends on connectless data retrival that means that all the data tables are retrived into the datasets and stored there (and eventually in the memory). This must be much faster than regular ADO but in your case and because of the multiple loops you are facing alot of reading at the same time which is reducing functionality. You will have to minimize the number of loops either by using temporary tables or by adjusting your querries. In all cases no one will be able to give u an actual solution for such process unless you are able to get the code u r using.

[nice_true]

Hi!

Below given is the code, due to which my system gets extremely slow,
Please see if you can help me out..........

Imports System.Data.SqlClient
Imports System.DBNull
Imports System.Array

Public Class WebForm1
Inherits System.Web.UI.Page
Public conctrack As SqlConnection = New SqlConnection("workstation id=(local);packet size=4096;user id=upload;password=n#0_pars#;data source=(local);persist security info=False;initial catalog=ctrack;Connect Timeout=0")
Dim dtable As New DataTable
Dim da As New SqlDataAdapter
Dim flag As Integer
Dim stdate As String
Dim eddate As String
Public clr As Integer
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents ddlsdyear As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddledmonth As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddleddate As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddledyear As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlsddate As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlsdmonth As System.Web.UI.WebControls.DropDownList
Protected WithEvents errlabel As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents nitcologo As System.Web.UI.HtmlControls.HtmlGenericControl

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
errlabel.Visible = False

End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim eddate, sddate As String
Dim cmdstation As New SqlCommand("select scode from ctrack.dbo.branch where scode is not null and scode <> ' ' union select scode from data.dbo.branch where scode is not null and scode <> ' '", conctrack)
Dim da As New SqlDataAdapter
Dim dset As New DataSet
da.SelectCommand = cmdstation
da.Fill(dset, "branch")
sddate = ddlsdmonth.SelectedItem.Text + "/" + ddlsddate.SelectedItem.Text + "/" + ddlsdyear.SelectedItem.Text
eddate = ddledmonth.SelectedItem.Text + "/" + ddleddate.SelectedItem.Text + "/" + ddledyear.SelectedItem.Text
If IsDate(sddate) And IsDate(eddate) Then
If Not sddate > eddate Then
errlabel.Visible = False
conctrack.Open()
' Counting Total Gp - Start
Dim station As Integer
For station = 0 To dset.Tables(0).Rows.Count - 1
Dim cmdtotalgp As New SqlCommand("select count(distinct ctrack.dbo.gate.gpno) 'counttotalgp' from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' union select count(distinct data.dbo.gate.gpno) 'counttotalgp' from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "'", conctrack)
cmdtotalgp.CommandTimeout = 0
Dim datotalgp As New SqlDataAdapter
Dim dsettotalgp As New DataSet
Dim counttotalgp As Integer
datotalgp.SelectCommand = cmdtotalgp
datotalgp.Fill(dsettotalgp, "gate")
Dim l As Integer
If dsettotalgp.Tables.Count > 0 Then
For l = 0 To dsettotalgp.Tables(0).Rows.Count - 1
counttotalgp = counttotalgp + dsettotalgp.Tables(0).Rows(l)("counttotalgp")
Next
Else
counttotalgp = 0
End If

' - End

'Counting Gp Cancel - Start
Dim cmdgpcancel As New SqlCommand("select count(distinct ctrack.dbo.gate.gpno) 'countgpcancel' from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' and ctrack.dbo.gate.grno='Cancel' union select count(distinct data.dbo.gate.gpno) 'countgpcancel' from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "' and data.dbo.gate.grno='Cancel'", conctrack)
cmdgpcancel.CommandTimeout = 0
Dim dagpcancel As New SqlDataAdapter
Dim dsetgpcancel As New DataSet
Dim counttotalgpcancel As Integer
dagpcancel.SelectCommand = cmdgpcancel
dagpcancel.Fill(dsetgpcancel, "gate")
Dim m As Integer
If dsetgpcancel.Tables.Count > 0 Then
For m = 0 To dsetgpcancel.Tables(0).Rows.Count - 1
counttotalgpcancel = counttotalgpcancel + dsetgpcancel.Tables(0).Rows(m)("countgpcancel")
Next
Else
counttotalgpcancel = 0
End If

' - End

' Counting Gp FOC - Start
Dim cmdgpfoc As New SqlCommand("select distinct ctrack.dbo.gate.gpno,ctrack.dbo.gate.grno from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' and ctrack.dbo.gate.grno='Cancel' and (ctrack.dbo.gate.cash+ctrack.dbo.gate.cheque+ctrack.dbo.gate.credit)>0 union select distinct data.dbo.gate.gpno,data.dbo.gate.grno from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "' and data.dbo.gate.grno='Cancel' and (data.dbo.gate.cash + data.dbo.gate.cheque + data.dbo.gate.credit)>0", conctrack)
cmdgpfoc.CommandTimeout = 0
Dim dagpfoc As New SqlDataAdapter
Dim dsetgpfoc As New DataSet
dagpfoc.SelectCommand = cmdgpfoc
dagpfoc.Fill(dsetgpfoc, "gate")
Dim count As Integer
Dim counttotalgpfoc As Integer = 0
For count = 0 To dsetgpfoc.Tables(0).Rows.Count - 1
Dim cmdgpfoc1 As New SqlCommand("select distinct grno,paystat from grn where grno='" & dsetgpfoc.Tables(0).Rows(count)("grno") & "' and (paystat='TP' or paystat='FOC' or paystat='FOCC')", conctrack)
cmdgpfoc1.CommandTimeout = 0
Dim dagpfoc1 As New SqlDataAdapter
Dim dsetgpfoc1 As New DataSet
dagpfoc1.SelectCommand = cmdgpfoc1
dagpfoc1.Fill(dsetgpfoc1, "gate")
If dsetgpfoc1.Tables.Count > 0 Then
counttotalgpfoc = counttotalgpfoc + 1
End If
Next
' - End

' Counting DK - Start
Dim cmddk As New SqlCommand("select distinct ctrack.dbo.gate.gpno,ctrack.dbo.gate.dk from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.grno <> 'Cancel'" & " and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' union select distinct data.dbo.gate.gpno,data.dbo.gate.dk from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.grno <> 'Cancel'" & " and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "'", conctrack)
cmddk.CommandTimeout = 0
Dim dadk As New SqlDataAdapter
Dim dsetdk As New DataSet
Dim i, counttotaldk As Integer
dadk.SelectCommand = cmddk
dadk.Fill(dsetdk, "gate")
For i = 0 To dsetdk.Tables(0).Rows.Count - 1
counttotaldk = counttotaldk + CDbl(dsetdk.Tables(0).Rows(i)("dk"))
Next
' - End

' Counting RC - Start
Dim cmdrc As New SqlCommand("select distinct ctrack.dbo.gate.gpno,ctrack.dbo.gate.toll_tax from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.grno <> 'Cancel'" & " and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' union select distinct data.dbo.gate.gpno,data.dbo.gate.toll_tax from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.grno <> 'Cancel'" & " and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "'", conctrack)
cmdrc.CommandTimeout = 0
Dim darc As New SqlDataAdapter
Dim dsetrc As New DataSet
darc.SelectCommand = cmdrc
darc.Fill(dsetrc, "gate")
Dim j, counttotalrc As Integer
For j = 0 To dsetrc.Tables(0).Rows.Count - 1
counttotalrc = counttotalrc + CDbl(dsetrc.Tables(0).Rows(j)("toll_tax"))
Next
' - End

' Counting Own Effort - Start
Dim cmdowneffort As New SqlCommand("select distinct ctrack.dbo.gate.gpno,ctrack.dbo.gate.others from ctrack.dbo.gate where left(ctrack.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and ctrack.dbo.gate.others is not null and ctrack.dbo.gate.gp_date>='" & sddate & "' and ctrack.dbo.gate.gp_date <='" & eddate & "' union select distinct data.dbo.gate.gpno,data.dbo.gate.others from data.dbo.gate where left(data.dbo.gate.datatr,3)='" & dset.Tables(0).Rows(station)("scode") & "' and data.dbo.gate.others is not null and data.dbo.gate.gp_date>='" & sddate & "' and data.dbo.gate.gp_date <='" & eddate & "'", conctrack)
cmdowneffort.CommandTimeout = 0
Dim daowneffort As New SqlDataAdapter
Dim dsetowneffort As New DataSet
Dim k, counttotalothers As Integer
daowneffort.SelectCommand = cmdowneffort
daowneffort.Fill(dsetowneffort, "gate")
For k = 0 To dsetowneffort.Tables(0).Rows.Count - 1
If (dsetowneffort.Tables(0).Rows(k)("others") > 0) Then
counttotalothers = counttotalothers + 1
End If
Next
' - End
If Not dtable.Columns.Count > 0 Then
dtable.Columns.Add("station")
dtable.Columns.Add("totalgp")
dtable.Columns.Add("gpcancel")
dtable.Columns.Add("gpfoc")
dtable.Columns.Add("netgp")
dtable.Columns.Add("owneffort")
dtable.Columns.Add("dk")
dtable.Columns.Add("rc")
dtable.Columns.Add("updateupto")
End If
Dim drow As DataRow
drow = dtable.NewRow
drow("station") = dset.Tables(0).Rows(station)("scode")
drow("totalgp") = counttotalgp
drow("gpcancel") = counttotalgpcancel
drow("gpfoc") = counttotalgpfoc
drow("netgp") = counttotalgp - (counttotalgpcancel + counttotalgpfoc)
drow("owneffort") = counttotalothers
drow("dk") = counttotaldk
drow("rc") = counttotalrc
drow("updateupto") = "jhn"
dtable.Rows.Add(drow)
DataGrid1.DataSource = dtable
DataGrid1.DataBind()
DataGrid1.Visible = True
If dtable.Rows.Count > 0 Then
showcolumns()
Else
unshowcolumns()
End If
conctrack.Close()
Next

Else
errlabel.Text = "Sorry! The starting date is greater than the ending date."
errlabel.Visible = True
DataGrid1.DataSource = Nothing
DataGrid1.Visible = False
End If
Else
errlabel.Text = "Please select a valid date."
errlabel.Visible = True
DataGrid1.DataSource = Nothing
DataGrid1.Visible = False
End If
End Sub

Private Sub ddlstation_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

'Filtering the consignor names according to the station selected - Start
'If ddlstation.SelectedIndex <> 0 Then
' DropDownList1.Items.Clear()
' DropDownList1.Items.Add("--Select Consignor Name--")
' conctrack.Open()
' Dim i As Integer
' Dim cmdconsignor = New SqlCommand("select distinct consignor,custid from partymst where left(custid,3)='" & ddlstation.SelectedItem.Text & "' order by consignor", conctrack)
' cmdconsignor.CommandTimeout = 0
' Dim drconsignor As SqlDataReader
' drconsignor = cmdconsignor.ExecuteReader()
' i = 1
' Do While drconsignor.Read
' DropDownList1.Items.Add(Convert.ToString(drconsignor(0)))
' DropDownList1.Items(i).Value = drconsignor("custid")
' i = i + 1
' Loop
' ' -End
' drconsignor.Close()
' conctrack.Close()
'End If
End Sub
Sub showcolumns()
DataGrid1.Columns(0).Visible = True
DataGrid1.Columns(1).Visible = True
DataGrid1.Columns(2).Visible = True
DataGrid1.Columns(3).Visible = True
DataGrid1.Columns(4).Visible = True
DataGrid1.Columns(5).Visible = True
DataGrid1.Columns(6).Visible = True
DataGrid1.Columns(7).Visible = True
DataGrid1.Columns(8).Visible = True
DataGrid1.Columns(9).Visible = False
DataGrid1.ShowFooter = False
End Sub
Sub unshowcolumns()
DataGrid1.Columns(0).Visible = False
DataGrid1.Columns(1).Visible = False
DataGrid1.Columns(2).Visible = False
DataGrid1.Columns(3).Visible = False
DataGrid1.Columns(4).Visible = False
DataGrid1.Columns(5).Visible = False
DataGrid1.Columns(6).Visible = False
DataGrid1.Columns(7).Visible = False
DataGrid1.Columns(8).Visible = False
DataGrid1.Columns(9).Visible = True
DataGrid1.ShowFooter = True
End Sub
End Class

In the above code, hen I set the "packet-size" in the connection string to 32767 and timeout to 1000, the system runs a bit faster.