Running an SQL query into a gridview for a table with 55 million row.

tanand79 · Dec 13, 2012

Here's my problem: I built a small ASP.Net site to display some data from one of our internal databases. I have it set that the Query runs on a button click and takes into account parameters entered into 4 different text boxes. This all works just fine if I limit the query to the top 1 million or so rows. The issue is that my table has a bit over 55 million rows. If I take off the limiter I'm constantly getting overflow and memory errors. I'm not surprised, but I was wondering if there was a way to fix this. I was wondering if maybe the parameters are only applying themselves AFTER the query has been run. Sorry if I'm being inexact, I only started doing this stuff last week. Here's my code:\[code\]<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">Date (dd/mm/yyyy)    <asp:TextBox ID="TextBox4" runat="server" Text="14/08/2009" style="margin-left: 4px" Width="125px"></asp:TextBox>  TO <asp:TextBox ID="TextBox5" runat="server" Width="125px" Text="30/12/2012" ></asp:TextBox> Trans #   <asp:TextBox ID="TextBox2" runat="server" style="margin-left: 94px" Width="125px"></asp:TextBox> Part   <asp:TextBox ID="TextBox3" runat="server" style="margin-left: 117px" Width="125px"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Go" onclick="Button1_Click" /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Left" onselectedindexchanged="GridView1_SelectedIndexChanged" style="text-align: center" > <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <Columns> <asp:BoundField DataField="C6946_TRANS_TYPE" HeaderText="Type de Transaction/Transaction Type" SortExpression="C6946_TRANS_TYPE" /> <asp:BoundField DataField="C6946_TRANS_DATE" HtmlEncode="false" HeaderText="Date de Transaction/Transaction Date" ConvertEmptyStringToNull="false" SortExpression="C6946_TRANS_DATE" FooterStyle-HorizontalAlign="Right" DataFormatString="{0:d}" >\[/code\] " \[code\]SelectCommand="SELECT C6946_TRANS_TYPE, C6946_TRANS_DATE, C6946_SO, C6946_LATE_FLAG, C6946_TRANS_NUM, ALIGNED_PART_NUMBER, C1001_ENG_PART_NUMBER, C6946_PART_KEY, PART_DESCRIPTION, C6946_RECORD FROM DWH.AIXW2S_T6946_TRANS_IMS" FilterExpression= "C6946_TRANS_NUM LIKE '%{0}%' AND (C6946_PART_KEY LIKE '%{1}%' OR ALIGNED_PART_NUMBER LIKE '%{1}%' OR C1001_ENG_PART_NUMBER LIKE '%{1}%') AND C6946_TRANS_DATE >= #{2}# AND C6946_TRANS_DATE <= #{3}#"><FilterParameters> <asp:ControlParameter ControlID="TextBox2" Name="C6946_TRANS_NUM" PropertyName="text" Type="String" DefaultValue="" ConvertEmptyStringToNull="false"/> <asp:ControlParameter ControlID="TextBox3" Name="C6946_PART_KEY" PropertyName="text" Type="string" DefaultValue="" ConvertEmptyStringToNull="false"/> <asp:ControlParameter ControlID="TextBox4" Name="C6946_TRANS_DATE" PropertyName="text" Type="DateTime" DefaultValue="" ConvertEmptyStringToNull="false"/> <asp:ControlParameter ControlID="TextBox5" Name="C6946_TRANS_DATE" PropertyName="text" Type="DateTime" DefaultValue="" ConvertEmptyStringToNull="false"/> </FilterParameters> </asp:SqlDataSource> </asp:Content>\[/code\]

Running an SQL query into a gridview for a table with 55 million row.

tanand79

New Member