SQL Server 2012超时时间

Question

我创建这个函数来计算短缺物料数量

ALTER FUNCTION [dbo].[ReturnShortageByItemCodeLinePackage]
    (@lineId int, @testpackId int, @MaterialDescriptionId int)
RETURNS float
AS BEGIN
    DECLARE @shortageQuantity float
    DECLARE @MIVQuantity float
    DECLARE @totalQuantity float
    DECLARE @spoolQuantity float
    DECLARE @ExistInSiteQuantity float
    DECLARE @BeforeDoneQuantity float
    SELECT  
        @totalQuantity = Quantity,
        @spoolQuantity = QuantitySpool,
        @ExistInSiteQuantity = QuantityExistInSite, 
        @BeforeDoneQuantity = QuantityBeforeDone
    FROM 
        [SPMS2].[dbo].Materials 
    WHERE
        LineId = @lineId 
        AND TestPackageId = @testpackId  
        AND MaterialDescriptionId = @MaterialDescriptionId
    SELECT 
        @MIVQuantity = SUM(QuantityDeliver) 
    FROM
        MaterialIssueVoucherDetails miv
    JOIN 
        MaterialRequestContractorDetails mrc ON miv.MaterialRequestContractorDetailId = mrc.Id
    WHERE
        TestPackageId = @testpackId 
        AND LineId = @lineId 
        AND miv.MaterialDescriptionId = @MaterialDescriptionId
    IF @MIVQuantity IS NULL
    BEGIN
        SET @MIVQuantity = 0    
    END
    SET @shortageQuantity = @totalQuantity - (@BeforeDoneQuantity + @ExistInSiteQuantity + @spoolQuantity + @MIVQuantity)
    RETURN round(@shortageQuantity,3)
END

我在我的存储过程中使用了这个函数，如下所示:

ALTER  PROCEDURE [dbo].[SPViewMTO] 
AS
BEGIN
    SELECT        
        dbo.Lines.Unit, dbo.Lines.LineNumber, dbo.Lines.DocumentNumber, 
        dbo.BaseMaterials.Name AS MaterialName, 
        dbo.MaterialDescriptions.Name AS MaterialDescription, 
        dbo.MaterialDescriptions.Description, dbo.MaterialScopes.ScopeName, 
        dbo.MaterialScopeObjectNames.ObjectName, 
        dbo.MaterialDescriptions.Size1, dbo.MaterialDescriptions.Size2, 
        dbo.MaterialDescriptions.ItemCode, 
        dbo.Materials.Quantity, dbo.Materials.Discipline, dbo.Materials.Id, 
        dbo.Lines.Id AS LineId, dbo.Materials.QuantitySpool,  
        dbo.Materials.QuantityExistInSite, dbo.Materials.QuantityBeforeDone, 
        dbo.TestPackages.PackageNumber, dbo.Materials.TestPackageId,
        ISNULL(dbo.ReturnShortageByItemCodeLinePackage(Lines.Id, TestPackageId, MaterialDescriptionId), 0) AS Shortage
    FROM            
        dbo.Materials 
    INNER JOIN
        dbo.Lines ON dbo.Materials.LineId = dbo.Lines.Id 
    INNER JOIN
        dbo.BaseMaterials ON dbo.Lines.BaseMaterialId = dbo.BaseMaterials.Id 
    INNER JOIN
        dbo.MaterialDescriptions ON dbo.Materials.MaterialDescriptionId = dbo.MaterialDescriptions.Id 
    INNER JOIN
        dbo.MaterialScopes ON dbo.MaterialDescriptions.MaterialScopeId = dbo.MaterialScopes.Id 
    INNER JOIN
        dbo.MaterialScopeObjectNames ON dbo.MaterialDescriptions.MaterialScopeObjectId = dbo.MaterialScopeObjectNames.Id 
    INNER JOIN
        dbo.TestPackages ON dbo.Materials.TestPackageId = dbo.TestPackages.Id
END

在我的SQL Server Management Studio中直接执行存储过程的时间约为47秒，但是当我从c#应用程序调用存储过程时，我得到这个错误:

SQL Server 2012

下面是调用存储过程的代码:

lst = _ctx.Database.SqlQuery<ViewDomainClass.MaterialOffice.DAViewMTO>
                      ("EXEC [dbo].SPViewMTO").ToList();

Answer 1

Timeout是ADO中连接字符串的一个属性。Net和其他框架，如LINQ &EF。尝试根据需要更改连接字符串中的超时时间。也可以在SQLCommand上设置超时时间来覆盖timeout属性。

Answer 2

使用(NOLOCK)可以提高查询的性能。

ALTER  PROCEDURE [dbo].[SPViewMTO] 
AS
BEGIN

 SELECT        dbo.Lines.Unit, dbo.Lines.LineNumber, dbo.Lines.DocumentNumber, dbo.BaseMaterials.Name AS MaterialName, 
                             dbo.MaterialDescriptions.Name AS MaterialDescription, dbo.MaterialDescriptions.Description, dbo.MaterialScopes.ScopeName, 
                             dbo.MaterialScopeObjectNames.ObjectName, dbo.MaterialDescriptions.Size1, dbo.MaterialDescriptions.Size2, dbo.MaterialDescriptions.ItemCode, 
                             dbo.Materials.Quantity, dbo.Materials.Discipline, dbo.Materials.Id, dbo.Lines.Id AS LineId, dbo.Materials.QuantitySpool, dbo.Materials.QuantityExistInSite, 
                             dbo.Materials.QuantityBeforeDone, dbo.TestPackages.PackageNumber, dbo.Materials.TestPackageId,isnull(dbo.ReturnShortageByItemCodeLinePackage(Lines.Id,TestPackageId,MaterialDescriptionId),0) As Shortage
    FROM            dbo.Materials(NOLOCK) INNER JOIN
                             dbo.Lines(NOLOCK) ON dbo.Materials.LineId = dbo.Lines.Id INNER JOIN
                             dbo.BaseMaterials(NOLOCK) ON dbo.Lines.BaseMaterialId = dbo.BaseMaterials.Id INNER JOIN
                             dbo.MaterialDescriptions(NOLOCK) ON dbo.Materials.MaterialDescriptionId = dbo.MaterialDescriptions.Id INNER JOIN
                             dbo.MaterialScopes(NOLOCK) ON dbo.MaterialDescriptions.MaterialScopeId = dbo.MaterialScopes.Id INNER JOIN
                             dbo.MaterialScopeObjectNames(NOLOCK) ON dbo.MaterialDescriptions.MaterialScopeObjectId = dbo.MaterialScopeObjectNames.Id INNER JOIN
                             dbo.TestPackages(NOLOCK) ON dbo.Materials.TestPackageId = dbo.TestPackages.Id
    EnD