CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvc
Mesut
Mesut

Reputation: 1875

Sql timeout Exception although max pool size is set to 1000000

I'm working with an asp.net MVC4 project and using SQL Server 2008. My database table contains 100000 rows. Also max pool size of connection string property is set to 1000000 and pooling is set to true.

My table structure is as follows:

CREATE TABLE tblNews
(
ID int IDENTITY(1,1) NOT NULL,
Url nvarchar(300) UNIQUE NOT NULL,
PubDate datetime NOT NULL,
Active bit NOT NULL,
Hit int NOT NULL,
...
)

and there's an index as follows:

CREATE NONCLUSTERED INDEX indexTblNews_Url
ON tblNews(Url)

My select query is:

CREATE PROC spNewsGet
   @Url nvarchar(300)
AS
   UPDATE tblNews
   SET Hit = Hit + 1
   WHERE Url = @Url
     AND PubDate > GETDATE()
     AND Active = 1;

   SELECT
      *
   FROM tblNews
   WHERE Url = @Url
     AND PubDate > GETDATE()
     AND Active = 1
   ORDER BY PubDate DESC

In low rated sites there is no problem and this works perfectly. But in database such big as 100000 rows and website having 2000000 single users / day, it crashes. It throws SqlTimeout exception at one of three pages. When I click a page consequently nearly all of them gives above exception.

I've checked hardware performance and processor consumption is at %70 of I7 3.6 GHZ, ram consumption is 1.5 gb. But there is more empty ram memory. How can I overcome this problem?

Any helps would be very appreciated.

Upvotes: 2

Views: 1590

Answers (2)

StuartLC
StuartLC

Reputation: 107267

A likely reason why it works quickly for some sites, but not for others, is that for high volume sites, the URL is no longer selective enough for the existing index to work. As a result, the UPDATE will likely escalate beyond row locks, causing contention for the clustered / non-clustered indexes.

I doubt that connection starvation is an issue - setting the connection pool max size beyond 32k is pointless, and concurrent SQL connections will also depend on factors relating to .Net threads.

I don't really understand why PubDate is future dated, but if PubDate > GETDATE(), and / or Active = 1 significantly reduce the amount of records in the update query, then I would also add one or both of these fields to the index (as applicable), i.e.:

CREATE NONCLUSTERED INDEX indexTblNews_Url
ON tblNews(Url, PubDate);

You are also duplicating the query - once to update, once to select. You can reduce the redundancy through a temp table and join back into it.

CREATE PROC spNewsGet
@Url nvarchar(300)
AS
  SELECT ID
  INTO #tmp
  FROM tblNews
  WHERE Url = @Url
  AND PubDate > GETDATE()
  AND Active = 1;

  UPDATE tblNews
  SET Hit = Hit + 1
  FROM tblNews INNER JOIN #tmp on #tmp.ID = tblNews.ID;

  -- Consider also using SET TRANSACTION ISOLATION SNAPSHOT or READ UNCOMMITTED here.
  SELECT tblNews.*
  FROM tblNews INNER JOIN #tmp on #tmp.ID = tblNews.ID
  ORDER BY PubDate DESC;
GO

And finally, as per the comment, you can consider dropping the isolation level on the final select, provided that the result set isn't used for 'transaction critical' usage.

Upvotes: 1

Michael J. Gray
Michael J. Gray

Reputation: 9906

Most likely you are using SqlConnection. Try setting the ConnectionTimeout property of the connection to 0 to indicate an infinite timeout. If you're not using SqlConnection, you may have a similar property you can set.

Upvotes: 1

Related Questions