Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Slow data grid

Status
Not open for further replies.
dawtes

dawtes

Programmer
Jun 23, 2005
31
US
Hi
I have a created a simple ASP page that return a row of 7K records using a data grid control and created a link for whomever to download the code as a CSV file. Everything works fine but for the result to be displayed on the data grid control is slow and it takes a lot longer to open a link for a CSV file. I am just guessing there is something inside the code that slows the whole process.
My question is:
1-Can I use one connection string and use it with two different objects.
2-Can I use one sql statement for both CSV file link and display the record in a data grid control.
3-Can I combine the code I use to create a CSV file with data grid and still get the CSV file and display the record in a data grid control.I really apprecite if someone could take a look at and give me an advise where to make the correction.
Below is the code :
Thanks

<Html>
<Head>
<Title>connecting to a database using VB</Title>
<style type="text/css">
<!--
.style4 {
font-size: 24px;
font-weight: bold;
}
-->
</style>
</Head>

<%@ Page Trace="False" Debug="True" Language="VB" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>


<Script runat="server" language="VB">


Dim myConnection as OleDbConnection
Dim dr as OleDbDataReader
Dim cmd as OleDbCommand
Dim connectionString as string
Dim i As Integer
Dim filename as string
Dim sb as System.Text.StringBuilder
Dim myConnectionc as OleDbConnection
Dim drc as OleDbDataReader
Dim cmdc as OleDbCommand
Dim connectionStringc as string



Sub page_load()

connectionString = "provider=MSDAORA;Data Source=xx;User ID=yy;Password=yy;"
myConnection = new OleDbConnection(connectionString)
myConnection.Open()


cmd = New OleDbCommand("SELECT (substr( ITEM ,1,4)||'.'||substr(item,5)) as Item , IDESCR ,IDESCRL ,IUNITS FROM ITEMLIST WHERE ISPECYR='01' and IOBSELET = 'N' and ITEM <> '2999509/00001 ' ",myConnection)
dr = cmd.ExecuteReader()
dgMetric.DataSource =dr
dgMetric.DataBind()

connectionString = "provider=MSDAORA;Data Source=xx;User ID=yy;Password=yy;"
myConnectionc = new OleDbConnection(connectionStringc)
myConnectionc.Open()

filename = "MetricItemList.csv"
cmdc = New OleDbCommand("SELECT (substr( ITEM ,1,4)||'.'||substr(item,5))as Item_Number, IDESCR as Short_Description, IDESCRL as Long_Description, IUNITS as Unit FROM ITEMLIST WHERE ISPECYR='01' and IOBSELET = 'N' and ITEM <> '2999509/00001 ' ",myConnectionc)
drc = cmdc.ExecuteReader()
sb = New System.Text.StringBuilder



End sub



Sub Click(ByVal sender as System.Object, ByVal e as System.EventArgs)





'for field name
For i=0 to drc.FieldCount-1
If i < (drc.FieldCount-1) then
sb.Append(Chr(34)&drc.GetName(i)&Chr(34)&",")
Else
sb.Append(Chr(34)&drc.GetName(i)&Chr(34)& VbCrLf)
End If
Next


'for field value
While drc.Read()
For i=0 to drc.FieldCount-1
If i < (drc.FieldCount-1) then
sb.Append(Chr(34)&drc.GetValue(i).ToString & Chr(34)&",")
Else
sb.Append(Chr(34)&drc.GetValue(i).ToString & Chr(34)& VbCrLf)
End if
Next

End while
drc.close()
myConnectionc.Close()
Response.ContentType ="Application/x-csv"
Response.AddHeader("content-disposition","attachment;filename="""& filename &"""")
Response.Write(sb.ToString)
Response.End()

End Sub


</Script>


<Body>
<table width="100%" border="0" >
<form runat="server">

<div align="center"><span class="style4">Bid Letting </span><br>
Trns*port Item Lists (Metric System -This list went into effect with the 2000 Spec. Book)
</div>
<P align="right"> Metric system item list data below as:
<asp:LinkButton id="btnSubmit" runat="Server" text=" Comma-Separated Values" OnClick="Click"/>
</P>
<hr>
<asp:DataGrid id="dgMetric" runat="server" autoGenerateColumns="false" cellpadding="4" width="100%">
<ItemStyle Font-Name="Arial" Font-Size="9pt" ForeColor="#000000"/>
<HeaderStyle Font-Name="Arial" Font-Size="9pt" Font-Bold="true" BackColor ="#003366" ForeColor ="#FFFFFF"/>
<AlternatingItemStyle Font-Name ="Arial" Font-Size ="9pt" BackColor ="#CCCCCC"/>
<columns>
<asp:boundColumn DataField="Item" HeaderText="Item Number"/>
<asp:boundColumn DataField="IDescr" HeaderText="Short Description"/>
<asp:boundColumn DataField="IDescrl" HeaderText="Long Description"/>
<asp:boundColumn DataField="Iunits" HeaderText="Unit Name"/>

</columns>
</asp:DataGrid>


</form>
</table>
</Body>
</Html>
 
Sort by date Sort by votes
Before actually delving into how you could reduce the code on the page and combine the different functionality that you need, I would suggest looking at the results that are actually sent to the page.

7000 records is a lot of data for an ASP.NET page to process, and im my opinion is far too much - think of the resulting HTML and how much needs to be outputted to the browser. You could try paging the results so that only x number of rows need to be written out which will reduce the time taken to load the page.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 
Upvote 0 Downvote
or you could also write out a stored proc that returns only the required rows. that will also save some time...

Known is handfull, Unknown is worldfull
 
Upvote 0 Downvote
Someone this morning suggested to me that i should use dataget control and display a certain number of records that way it will increase the the time for the selected resulted to be displayed.I took his suggestion and spent a fair amount of time and move from data grid to data set and the properties of dataset to dispay a 1k record at a time.Just to come to my point even though i used datasets and create a datagrid that pages the time it takes for the row record to be displayed is still longer. Can someone out there throw some idea how i should take care of this problem is appreciated.below is the code i used

<Html>
<Head>
<Title>connecting to a database using VB</Title>
<style type="text/css">
<!--
.style4 {
font-size: 24px;
font-weight: bold;
}
-->
</style>
</Head>

<%@ Page Trace="False" Debug="True" Language="VB" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>


<Script runat="server" language="VB">

Sub page_load()
Dim myConnection as OleDbConnection
Dim i As Integer
Dim filename as string
Dim sb as System.Text.StringBuilder
Dim objDA as OleDbDataAdapter
Dim objDs as New DataSet()
Dim connectionString as string

connectionString = "provider=MSDAORA;Data Source=tplp;User ID=tplcp;Password=tplcp;"
myConnection = new OleDbConnection(connectionString)
myConnection.Open()


objDA = New OleDbDataAdapter("SELECT (substr( ITEM ,1,4)||'.'||substr(item,5)) as Item , IDESCR ,IDESCRL ,IUNITS FROM ITEMLIST WHERE ISPECYR='01' and IOBSELET = 'N' and ITEM <> '2999509/00001 ' ",myConnection)
objDA.Fill(objDs,"ITEMLIST")
dgMetric.DataSource=objDs
dgMetric.DataBind()


End Sub

sub dg_Page(s as object ,e as dataGridPageChangedEventArgs)
dgMetric.CurrentPageIndex = e.NewPageIndex
end sub

</Script>



<Body>
<table width="100%" border="0" >
<form runat="server">


<hr>
<asp:DataGrid id="dgMetric" runat="server" autoGenerateColumns="false" cellpadding="4" width="100%" AllowPaging="true" PageSize="1000" PagerStyle-Mode="NextPrev" PagerStyle-NextPageText="Next &gt;" pagerStyle-PrevPageText="&lt;Prev" onPageIndexChanged="dg_page" >

<ItemStyle Font-Name="Arial" Font-Size="9pt" ForeColor="#000000"/>
<HeaderStyle Font-Name="Arial" Font-Size="9pt" Font-Bold="true" BackColor ="#003366" ForeColor ="#FFFFFF"/>

<AlternatingItemStyle Font-Name ="Arial" Font-Size ="9pt" BackColor ="#CCCCCC"/>
<columns>
<asp:boundColumn DataField="Item" HeaderText="Item Number"/>
<asp:boundColumn DataField="IDescr" HeaderText="Short Description"/>
<asp:boundColumn DataField="IDescrl" HeaderText="Long Description"/>
<asp:boundColumn DataField="Iunits" HeaderText="Unit Name"/>

</columns>
</asp:DataGrid>


</form>
</table>
</Body>
</Html>
 
Upvote 0 Downvote
I am having a hard time folling what you want to do. As stated before ca8msm, 7,000 records to display in a browser is alot.

You have 2 choices.
1. As ca8msm has stated, limit the number of rows returned from your query.

2. As vbkris stated, use a paging sp that only pulls rows needed to be displayed. There are lots of articles out there that show how to write the SP code.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
274
Makumbi256
Makumbi256
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
391
AhmedAzizBarbary
AhmedAzizBarbary
G12Consult
  • Locked
  • Question
Retrieve data from JSON
Replies
1
Views
328
gmmastros
gmmastros
thep1
  • Locked
  • Question
dynamic pivot data to display in gridview
Replies
3
Views
305
jbenson001
jbenson001
baran121
  • Locked
  • Question
Sending data with bluetooth on webproject
Replies
1
Views
127
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top