We've got a .Net web app with a table called CoreFile. The table has a PK called FileID and a varbinary(max) column called FileData, amount other columns. The table holds small
files, most of which are below 100kb, but with the odd 1.5-2mb file. I don’t believe there is anything above 10mb.
We have a stored procedure that deletes an individual entry:
DELETE FROM CoreFile WHERE FileID = @FileID
The problem is that when the SP is executed from the web app, the connection times out and the delete is unsuccessful, particularly when the file is larger. The specific error is:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When we execute the SP from SSMS however, the statement runs almost instantly, regardless of the file size.
I checked the connection settings using the SQL Server Profiler, and both SSMS and the web app appear to use the same configuration:
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language British
set dateformat dmy
set datefirst 1
set transaction isolation level read committed
I tried replacing the SP with a LINQ statement using the Entity Fram
View Complete Post