How to increase timeout of executing time consuming SQL Statements

Topics: Administration, Troubleshooting
Mar 7, 2014 at 8:04 AM
Edited Mar 7, 2014 at 8:05 AM
I encountered an error of "System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out".

Looking into my log file, this SQL statement is causing it:
NHibernate.AdoNet.AbstractBatcher - Could not execute query: SELECT count(*) as y0_ FROM Orchard_Framework_ContentItemVersionRecord this_ inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id inner join Common_CommonPartRecord commonpart3_ on contentite1_.Id=commonpart3_.Id inner join Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id WHERE contenttyp2_.Name in (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8) and contenttyp2_.Name = @p9 and this_.Latest = @p10

Here's a little background of what I was doing. I was trying to view the Content Manager (in Dashboard, click the menu "Content" on the left) which I should be shown a list of Content Items. I basically know why the error happened. I have over ONE MILLION content items in my site, and the Content Manager is executing the above SQL statement in order to get the total number of Content Items to construct the Pager. My mediocre server might not be quick enough to process the query and Orchard timed out.

Is there a way to increase the timeout to allow sufficient time for sql server to finish executing the query?
Developer
Mar 8, 2014 at 2:09 AM
AFAIK you can simply specify the timeout directly in the connection string.
Mar 10, 2014 at 9:29 AM
Piedone wrote:
AFAIK you can simply specify the timeout directly in the connection string.
That would be the Connection Timeout which specifies the timeout when establishing a connection, not the timeout when running an SQL statement. What I'm looking is Command Timeout. It seems like there isn't a way to increase this globally (global.asax, web.config or any other means) without first laying down the intended codes before hand.

Even if I want to modify the Orchard Core Codes (which I know I'm not suppose to, to prevent breaking updates), I don't know where to start. All database querying capabilities are encapsulated by nHibernate.
Mar 17, 2014 at 5:49 PM
I am looking for same as well. Does anybody know how to increase Command Timeout?