timeout happening due to store proc

[coder91]

We use object data sources to access our database and having a problem at the minute, the user enters text and clicks search and the stored proc is pulling so much data back from the database that its taking over a minute in SQL, in Visual Studio the timeout is 30 seconds. I know I've to us the commandtimeout but not sure how, this is the code I previously had.

    public static ICollection<Summary> GetSummary(String Ref)
        {
            Database db = XDatabaseFactory.GetXDatabase();
            IDataReader reader = db.ExecuteReader("storedprocName", new object[] { Ref });
            LinkedList<Summary> obj = new LinkedList<Summary>();

            using (reader)
            {
                while (reader.Read())
                {

                    Summary newToAdd = CreateSummary(reader);
                    obj.AddLast(ToAdd);
                }
            }

            return obj;
        }

Ive began creating another method above this one with the code

 public static void GetNew(String Ref)
            {
                Database db = xDatabaseFactory.GetxDatabase();
                DbCommand cmd = db.GetStoredProcCommand("StoredprocName");
                cmd.CommandTimeout = 600;


            //build list of parameters
            db.AddInParameter(cmd, "@p_Ref", DbType.String, Ref);

            db.ExecuteReader(cmd);
        }

Can anyone tell me where I'm going wrong or what else I have to do?

[LastMitch]

We use object data sources to access our database and having a problem at the minute, the user enters text and clicks search and the stored proc is pulling so much data back from the database that its taking over a minute in SQL, in Visual Studio the timeout is 30 seconds.

I assume the issue you are having doesn't have any error since you never mention one. The issue is that it's taking more than minute to pulled the data. The code you provided looks right and I feel is more related to connection. Try these tutorials:

http://www.codeguru.com/csharp/csharp/cs_internet/displaying-real-time-data-using-html5-and-asp.net.htm

http://devproconnections.com/aspnet/cache-using-objectdatasource-control

http://highoncoding.com/Articles/356_Data_Caching_in_ASP_NET.aspx

[coder91]

Yea I assummed it was because the stored procedure is pulling so much data it is taking too long, thanks i'll have a look at them and see if they help :)