SQL Server 2012 Stored Procedure timing out. How to fix it?

Question

Context: SQL Server 2012, Windows Server 2008 R2 (64bit), 2 cores, lots of RAM and HD space, ADODB via JScript (not .NET)

These really simple stored procedures keep timing out. It's not like I have a lot of records either (from a server point of view): 100,000 or so.

CREATE PROCEDURE [dbo].[Transfer_Part1]
AS
    SET NOCOUNT ON
    INSERT INTO Primary.dbo.Post
    SELECT *
    FROM Secondary.dbo.Pre
    WHERE HrefProcessed = (-1)
        AND ReferrerProcessed = (-1);

CREATE PROCEDURE [dbo].[Transfer_Part3]
AS
    SET NOCOUNT ON
    DELETE
    FROM Secondary.dbo.Pre
    WHERE HrefProcessed = (-1)
        AND ReferrerProcessed = (-1);

Is there anything I can do to stop getting these messages?

[Microsoft][ODBC SQL Server Driver]Query timeout expired
EXEC Transfer_Part1
[Microsoft][ODBC SQL Server Driver]Query timeout expired
EXEC Transfer_Part3

Do you have an 2-column index on HrefProcessed and ReferrerProcessed? Also see http://stackoverflow.com/a/2126482/27535 for the second one. ANd please add the query plans and table structures — gbn, Apr 24 '13 at 08:05
Do now! Please make your comment into an answer and I'll give you the tick. — bugmagnet, May 01 '13 at 03:15

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

Do you have an 2-column index on HrefProcessed and ReferrerProcessed?

Also see Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?) for the second one.

edited May 23 '17 at 12:12

Community

1
1

answered May 01 '13 at 10:23

gbn

422,506
82
585
676

SQL Server 2012 Stored Procedure timing out. How to fix it?

1 Answers1