11

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.

  • Can you explain question in detail ? – Dipen Shah Dec 21 '15 at 8:37
  • after restart of SQL server, is it giving timeout error for "list.getitembyid(1)" also? – Gaurravs Dec 21 '15 at 10:20
  • yes. and also if i didnt query to that list for 2 to 3 hours, it also gave me that error. – Rajesh Joshi Dec 21 '15 at 10:22
5
+50

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.

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.

  • If i didn't query for 2 to 3 hours and not restarting SQL server, still it gives me timeout error. – Rajesh Joshi Dec 21 '15 at 10:19
  • And also my query is simple. just list.getitembyid(1) – Rajesh Joshi Dec 21 '15 at 10:20
  • SharePoint relays on SQL server, and I think that in your case you probably have a problem with SQL server. You don't share too many information about your configuration so my answer was generic. it could be problem with memory, processors, disk, etc... You have to remember that if you have large list you will need proper configuration of SQL server. So start with SQL server optimization. Depends on farm size it could be very challenging job and should be carefully planed. There's option in SSIS which allow to optimise database. – Tomasz Szulczewski Dec 21 '15 at 10:28
  • my list contains 500000 data – Rajesh Joshi Dec 21 '15 at 10:30
  • can you suggest me what i need to do? – Rajesh Joshi Dec 21 '15 at 10:32
1

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.

1

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

  • tried that but not resolved my issue – Rajesh Joshi Dec 23 '15 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.