Timeout in Stored Procedure with (COUNT)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Timeout in Stored Procedure with (COUNT)

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2010-01-10 : 03:49:52

Hello,

i have a strange phenomenon

.

In my Stored Procedure I make four counts to get counters from one table. The Stored Procedure creates a HTML report. The Table has +300.000 Records. And I setup some needed Indexes.

The Stored Procedure works fine for five Days. Now I get a Timeout. But If I remove the first count It works fine The removed first count statement takes 90ms by itself.

What can be the reason for the Time out? A Lock on the Table? Or should I run this in a transaction. Or is there better way to get the counters?

Many thanks for help and ideas

.

Best regard
Jaroslaw Kucharski


SET NOCOUNT ON;

	--SELECT @CreateCount = Count(OrderNumber) FROM dbo.T_Order WHERE (NotificationStatus = 1 AND NotificationReason = 0)
	SELECT @UpdateCount = Count(OrderNumber) FROM dbo.T_Order WHERE NotificationStatus = 1 AND NotificationReason = 1
	SELECT @ErrorCount = Count(OrderNumber) FROM dbo.View_NORDIS_CRM_Salesorder_Import_Error
	SELECT @OrderCount = Count(OrderNumber) FROM dbo.T_Order
	SET @EmailSubject = 'Order Status (' + CONVERT(varchar(10), GETDATE(), 104)  +' ' + CONVERT(varchar(5), GETDATE(), 108) +')'

br
Jaroslaw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-10 : 10:53:55

Show us your table structure and indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

MacJK
Starting Member

24 Posts

Posted - 2010-01-10 : 12:10:17

Hello, i post a create script.

br
Jaroslaw

MacJK
Starting Member

24 Posts

Posted - 2010-01-10 : 12:13:08

Hello, the Index.



USE [N]
GO
/****** Object:  Index [IX_T_Order]    Script Date: 01/10/2010 18:12:44 ******/
CREATE NONCLUSTERED INDEX [IX_T_Order] ON [dbo].[T_Order] 
(
	[CRMTransferStatus] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

USE [N]
GO
/****** Object:  Index [IX_T_Order]    Script Date: 01/10/2010 18:12:44 ******/
CREATE NONCLUSTERED INDEX [IX_T_Order] ON [dbo].[T_Order] 
(
	[CRMTransferStatus] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

br Jaroslaw Kucharski

MacJK
Starting Member

24 Posts

Posted - 2010-01-10 : 12:29:07

Hello, it dosent metter how lone i exclud from the Procedure i just need to remove one.

SELECT @CreateCount = Count(OrderNumber) FROM dbo.T_Order WHERE (NotificationStatus = 1 AND NotificationReason = 0)
SELECT @UpdateCount = Count(OrderNumber) FROM dbo.T_Order WHERE NotificationStatus = 1 AND NotificationReason = 1
--SELECT @ErrorCount = Count(OrderNumber) FROM dbo.View_NORDIS_CRM_Salesorder_Import_Error
SELECT @OrderCount = Count(OrderNumber) FROM dbo.T_Order

this work also but if i add all line the Procedure get a time out.
br Jaroslaw Kucharski

br
Jaroslaw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-10 : 12:54:35

You need more indexes to support your code:

T_Order: NotificationReason, NotificationStatus perhaps INCLUDE OrderNumber
T_Order: OrderNumber

I'd have to see the view definition in order to make an index recommendation.

The reason why it works if you take one query out is because a timeout value is a maximum value to allow a query to run. The default in most or all applications is 30 seconds (SQL Server doesn't timeout). So your code is taking longer than 30 seconds to complete, hence the error. By removing a query, you've gotten it under 30 seconds. So we need to work on indexes to to get it as efficient as possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

MacJK
Starting Member

24 Posts

Posted - 2010-01-10 : 12:56:22

Hello, i got it. It seam to be a problem in the code. It work now, i change the code. Many thanks so far.

br

Jaroslaw Kucharski

br
Jaroslaw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-10 : 13:48:33

Hopefully you didn't increase your timeout value.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources