Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to find the cause for this error while calling a stored proc with a table valued parameter:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

My objective is to load sanitized data from a 3rd party app into a data-warehousey location for in-house apps to consume. I don't have an "updated" flag in the source, so I'm using a data flow to push all of the data into a recordset, then firing a script task to call a MERGE proc with TVP.

I've used this pattern successfully several times, but this data set is by far the largest--over 1m rows (though not terribly wide). I can always truncate and reload the table every time within an acceptable time frame, but that just seems awfully wasteful on I/O.

I looked at the settings in the ADO.NET connection, but the timeouts there don't seem like they should apply. I'm considering slicing the recordset into smaller chunks and looping over the proc, but without a better understanding of the problem, I suspect that would just cause problems when the data set grows enough to make the chunks fail.

Proc:

alter proc [sis].[Attendance_Merge]
    @data sisAttendance readonly
    as begin
if object_id('tempdb..#data') is not null drop table #data;
create table #data (
    [SchoolYear] smallint not null,
    [Student] [varchar] (10) NOT NULL,
    [Date] [date] NOT NULL,
    [Period] [varchar] (10) NOT NULL,
    [Reason] [varchar] (32) NOT NULL,
    [Funding] [varchar] (1) NOT NULL,
    [StateReported] [varchar] (1) NOT null,
    [rn] int not null
    primary key clustered (SchoolYear,Student,[Date],Period,rn)
    );
insert into #data
select
    *
    ,rn = row_number() over(
        partition by
            SchoolYear
            ,Student
            ,[Date]
            ,Period
        order by
            Student
        )
    from @data
    ;
with att as (
    select
        *
        ,rn = row_number() over(
            partition by
                SchoolYear
                ,Student
                ,[Date]
                ,Period
            order by
                Student
            )
        from sis.Attendance
    )
merge into att t
    using #data s
        on t.SchoolYear = s.SchoolYear
        and t.Student = s.Student
        and t.[Date] = s.[Date]
        and t.Period = s.Period
        and t.rn = s.rn
    when matched
            and (t.Reason <> s.Reason
                or t.Funding <> s.Funding
                or t.StateReported <> s.StateReported
                )
        then update set 
            t.Reason = s.Reason
            ,t.Funding = s.Funding
            ,t.StateReported = s.StateReported
    when not matched by target
        then insert (
            SchoolYear
            ,Student
            ,[Date]
            ,Period
            ,Reason
            ,Funding
            ,StateReported
            )
        values (
            s.SchoolYear
            ,s.Student
            ,s.[Date]
            ,s.Period
            ,s.Reason
            ,s.Funding
            ,s.StateReported
            )
    when not matched by source
        then delete
    ;
end
share|improve this question
    
You're probably getting a plan optimised for a single row. Try adding OPTION (RECOMPILE) –  Martin Smith Feb 12 at 15:51
2  
The timeout is going to be enforced by System.Data.SqlClient.SqlCommand.CommandTimeout. It defaults to 30 seconds. This is a client-side enforcement. Whereas there is a good chance your T-SQL code may not be optimal, the root of the actual timeout isn't there. –  Thomas Stringer Feb 12 at 16:28
    
@Martin -- Didn't change anything, but got me looking more at the proc itself. I was dumping the TVP into a temp table immediately, then doing the merge. Thinking an index might help, I added a clustered PK constraint, but also no change. Now I'm starting to think that dumping 1m rows into a temp table isn't saving any I/O over rewriting the table...maybe I'm coming at this all wrong –  SQLFox Feb 12 at 16:49
    
@Thomas -- That's what I was looking for! I'd found references to that attached to a connection string, but not to the SqlCommand, and I didn't make the connection. cmd.CommandTimeout = 0; let it complete successfully! Now to figure out if this was a good idea to begin with... –  SQLFox Feb 12 at 16:52
1  
I haven't studied the code in depth but why the #temp table? Why not just use the TVP as the source for the MERGE? Does the definition of the TVP include a PK constraint? Also, my typical disclaimer, please MERGE with caution. –  Aaron Bertrand Feb 12 at 17:21
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.