Request timeout error in large list

Question

I have a list containing 500000 data. When i am query on that list like list.getitembyuniqueid(1), its working fine and give result quickly. But when i restart sql server and try to fetch data from that list, it was giving me a timeout error only when i tried to query on that list. Other pages working fine.

Please help me resolve that issue.

Answer 1

You can use

Caching a DataTable Object

The code in these examples suspends all other threads in a critical section running in IIS, and prevents other threads from accessing the cached object until it is completely built. This addresses the thread synchronization issue; however, the code is still not correct because it is caching an object that is not thread safe.

To address thread safety, you can cache a DataTable object that is created from the SPListItemCollection object. You would modify the previous example as follows so that your code gets the data from the DataTable object.

private static object _lock =  new object();

public void CacheData()
{
   DataTable oDataTable;
   SPListItemCollection oListItems;
   lock(_lock)
   {
           oDataTable = (DataTable)Cache["ListItemCacheName"];
           if(oDataTable == null)
           {
              oListItems = DoQueryToReturnItems();
              oDataTable = oListItems.GetDataTable();
              Cache.Add("ListItemCacheName", oDataTable, ..);
           }
   }
}

Now you can get single item without requesting to SQL Server to get item from SharePoint list. You can use this Cache in every where like a session.

Answer 2

I don’t know which version of SQL server your run and it’s configuration. So my answer is little generic.

When you restart, SQL Server will clear out all memory content like data cache and execution plans. SharePoint has own stored procedures, and SQL Server needs execution plan to execute it. It checks the plan cache first, if found its reused and if nothing found, SQL Server will create a new plan this is resource consuming and has impact in performance and it is reused next time.
To solve the problem you can for example run some simple query against the list, to build some statistic and fill memory of the sql server after server restart. Other option it to keep your SQL server in good state, I mean for example you should check your databases indexes fragmentation, actual statistics, content database size, etc. There SSIS component which can help you with this.

Answer 3

You can achieve by increasing connection time-out.

To increase the connection time-out setting, follow these steps.

Note By default, the IIS connection time-out setting is 120 seconds. 1. Click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager. 2. Right-click the virtual server that you want to configure, and then click Properties. 3. Click the Web Site tab. 4. Under Connections, type the number of seconds that you want in the Connection time-out box, and then click OK.

Answer 4

Have you tried indexing all the columns that are returned on that query? Maybe your query is being throttled, and indexing the other columns could solve the issue.

https://msdn.microsoft.com/en-us/library/ff798465.aspx