Click here to Skip to main content
14,735,271 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have a table in Sql Database And in table there in more then 40,000 record...,

Now on one page of my site,,

i want to show that data according to Dynamic query(add Multiple Query According to condition),and then return all result of this query to DataTable and Then Put It into GRIDVIew,

Now My problem is, when i want to show all result at a time then it gives error Like this..
Timeout expired. The timeout period elapsed prior to complete


or Query Return Nothing,,,

And when i take data according to some condition then It Return perfect data(Means When Result of data table is low then it work fine...),

This is My Code:
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("SELECT  t.id,  t.year,t.courtname,t.partyname,t.itano,t.favour,t.sectionno, STUFF((SELECT ' ' + s.sitentionno ,'  ' + s.subjudgements,'  ' + s.page , '  ' FROM tblJudgements s WHERE   s.partyname = t.partyname FOR XML PATH('')),1,0,'') AS Citation FROM tblJudgements AS t where t.categoryname='Direct Taxes' ");
        if (txtWordAd.Text != "")
        {
            string keywords = txtWordAd.Text;
            if (!string.IsNullOrEmpty(keywords))
            {
                string[] words = keywords.Split(' ');
                for (int i = 0; i < words.Length; i++)
                {
                    if (i == 0)
                    {
                        strSql2.Append(" and ( t.judgement like '%[^a-z]" + words[i].ToString().Trim() + "[^a-z]%' )  ");
                    }
                    else
                    {
                        strSql2.Append(" and judgement like '%[^a-z]" + words[i].ToString().Trim() + "[^a-z]%'");
                    }
                }
            }
        }
       
        if (txtDateFromAd.Text != "" && txtDateToAd.Text != "")
        {
            strSql2.Append(" and (dateofjudgement  between '" + txtDateFromAd.Text + "' and '" + txtDateToAd.Text + "'  ) ");
        }
       
        SqlDataAdapter adp4 = new SqlDataAdapter(strSql2.ToString(), strConn);
        
        adp4.Fill(dt);

        GridJudgementAdvanced1.DataSource = dt;
        GridJudgementAdvanced1.DataBind();

}



i also try :adp4.SelectCommand.CommandTimeout = 120;

but it not work,,

i apply pagging to GridView 20 record at a time, But Problem is Query Return All 40000 record to datatable And this Consume So much Time, to complete one Query(Process...),

So in that Situation What can i Do ???????

Plz help..

I Also Find Same Problem Here,But There is no solution..


[^]

How to resolve Time out Error in Classic ASP[^]

Timeout expired sql server problem[^]
Posted
Updated 15-Dec-13 9:07am
v3
Comments
Jibesh 15-Dec-13 14:05pm
   
improving the timeout value is not always a good option. I would suggest you to fine tune your SQL query which pulls the data for the first page only.

you can adapt something like this:
1. Getting the total rows count is faster than getting all the row data
2. have the grid pagination implemented in your screen and sql query
3. use the Page number and number of records per page to filter your data query from the sql data base

If you are using SQL Server 2012, you could use the OFFSET and FETCH arguments of the ORDER BY clause. See ORDER BY Clause (Transact-SQL)

Retrieves first 20 rows

select * from people order by lastname,firstname offset 0 rows fetch next 20 rows only


Retrieves next 20 rows

select * from people order by lastname,firstname offset 20 rows fetch next 20 rows only


That said, I think that any query in an online business application that returns as many rows as your query should have additional selection criteria applied to greatly reduce the number of rows retrieved. Your user probably does not want to page through 40,000 rows 20 at a time. That is 2,000 clicks on Next. It would be more considerate of your user's time to let them use additional selection criteria to select only those few rows that they really need to see.
   
v5
Comments
Arun kumar Gauttam 17-Dec-13 0:31am
   
thanks....
Increasing the timeout is not a optimal solution for this . Mike in his solution have pointed out a good way to have a Load on Demand approach for fetching the records. My suggestion would be also the same.
Adding a link for your reference as one of the way to this approach.

http://www.aspsnippets.com/Articles/Load-on-demand-data-in-GridView-on-scroll-using-ASPNet-and-jQuery-AJAX.aspx[^]

Hope this helps...
   
v2
Comments
Arun kumar Gauttam 17-Dec-13 0:31am
   
thanks...
JoCodes 17-Dec-13 5:40am
   
You are welcome...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900