在ASP.net应用TSQL IVF造成超时 [英] TSQL IVF causing timeout in ASP.net application

问题描述

感谢您的关注。

首先，一些关于这个问题的背景：

我是用无处不在整个ASP.net应用程序，我已经开发了，在IIS7运行的内联表函数。它是30个或更多的存储过程的基础上，所有的同时通常会在最长200ms的过程（比快更足够了）。该应用程序始终使用所有的数据库连接相同的连接字符串。该函数本身最大10ms的〜运行。

有时候需要将呼叫存储过程，其中，功能是用来将导致在15秒后超时的网页的呼叫。此超时适用于在网页上的所有用户，但它也存储有使用该功能的程序的其他网页仍然执行正常而这是发生，这表明它是有问题的特定存储过程。然而，这已经发生在多个页面，提示它可以是多个存储过程或函数本身。

运行中从不同的用户凭据的管理工作室会话页面上的存储过程的任何（或全部）为＆lt; 200毫秒，即使超时发生在Web应用程序

。

在功能上运行sp_recompile会一直清除超时，任何登录凭据。

由于这是在应用程序的关键部分，sp_recompile被尽快运行，并且小时间可用于调试。另外，我还从来没有能够重现随意超时。

我已经尝试做了很多的研究内嵌表函数并在任何提示，这是与他们共同的问题，因此，他们应该避免没有来。

问题：

难道这些超时正在使用功能造成的，或者是他们保证是其依赖于它的存储过程的问题​​？换一种方式，有可能是在重构存储过程使用一个视图或通过封装适当的逻辑嵌入任何好处？

我的猜测是，它是唯一的存储过程，我很可能会通过增加优化不明，重新编译选项在适当情况下，还是本地化的参数，但在所有诚实我倒是preFER找到一个解决这个溶液施加到底层的功能，例如，我可以在一个位置应用修订

功能：

  CREATE FUNCTION [DBO]。[fn_ObjectIDs]（
    @DateFrom AS DATETIME = NULL
    @ DateTo AS DATETIME = NULL
    @地区INT = NULL
    @ FAMILYID AS INT = NULL
    @ PARENTID AS INT = NULL
    @ childID的AS INT = NULL
）RETURNS TABLE AS
返回
    SELECT DISTINCT
        obj.ID AS标识
    从tblObjects OBJ WITH（NOLOCK）
    INNER JOIN WITH（NOLOCK）tblFamily FAM
            ON obj.famID = fam.famID
        LEFT JOIN tblCountry CNTRY WITH（NOLOCK）
            ON（@region IS NOT NULL）AND（fam.countryId = cntry.countryId）
        LEFT JOIN WITH tblParent父（NOLOCK）
            ON（@ParentID IS NOT NULL）AND（obj.ID = parent.objectID）
        LEFT JOIN tblChild孩子（NOLOCK）
            ON（@ChildID IS NOT NULL）AND（obj.ID = child.objectID）
    哪里
        obj.Visible = 1
        和obj.statusID IN（3,4,6,8）
        AND（（@DateFrom IS NULL）OR（obj.CreatedDate＆GT; = @DateFrom））
        AND（（@DateTo IS NULL）OR（obj.CreatedDate＆LT; = @DateTo））
        AND（（@region IS NULL）OR（cntry.regionID = @region））
        AND（（@FamilyID IS NULL）OR（obj.famID = @FamilyID））
        AND（（@ParentID IS NULL）OR（parent.parentID = @ParentID））
        AND（（@ChildID IS NULL）OR（child.childID = @ChildID））
 

解决方案

暂时增加一些日志，以验证什么是真正导致超时问题。你可以在你的应用程序做到这一点，它存储过程本身。有程序写入当前时间戳和使用时，它的执行调用程序，一个记录表中的参数。同时添加记录到您的应用程序。然后，你就可以当如果有导致此问题，或者如果问题出在程序都没有。

某些参数的具体超时发生，以确定

在广大用户定义的函数是不是一个好主意，但我的理解是，一个表内联函数比一些其他的更好。它增加了大量的开销和优化不能与UDF的正常工作。

Hi and thanks for your attention.

First some background on the question:

I have an Inline Table Function which is used ubiquitously throughout an ASP.net application I have developed, running on IIS7. It's the basis for 30 or more stored procedures, all of while will normally process in a maximum of 200ms (more than fast enough). The application always uses the same connection string for all database connections. The function itself runs in a maximum of ~10ms.

Occasionally a call to a page which calls a stored procedure in which the function is used will result in a timeout after 15 seconds. This timeout applies to all users of the page, but other pages which also have stored procedures which use this function still perform normally while this is occurring, suggesting that it is a specific stored procedure having problems. However this has occurred on multiple pages, suggesting it is either multiple stored procedures or the function itself.

Running any (or all) of the stored procedures on the page from a management studio session with different user credentials is <200ms, even when a timeout is occurring for the web application.

Running an sp_recompile on the function will always "clear" the timeout, from any login credentials.

Because this is a critical portion of the application, sp_recompile is run as soon as possible, and little time is available for debugging. Also, I have never been able to recreate the timeout at will.

I've tried to do a lot of research on Inline Table Functions and haven't come across anything which suggests that this is a common problem with them and they should therefore be avoided.

The Question:

Is it possible that these timeouts are being caused by using the function, or are they guaranteed to be an issue with the stored procedures which rely upon it? To put it another way, is there likely to be any benefit in refactoring the stored procedures to use either a view or by encapsulating the appropriate logic inline?

My guess is that it is the stored procedures only and I'll likely solve this by adding optimize for unknown, option recompile where appropriate, or localising the parameters, but in all honesty I'd prefer to find a solution which applies to the underlying function such that I can apply a fix in a single location.

The function:

CREATE FUNCTION [dbo].[fn_ObjectIDs] (
    @DateFrom       AS DATETIME = NULL
    ,@DateTo        AS DATETIME = NULL
    ,@Region        AS INT = NULL
    ,@FamilyID      AS INT = NULL
    ,@ParentID      AS INT = NULL
    ,@ChildID       AS INT = NULL
) RETURNS TABLE AS
RETURN
    SELECT DISTINCT
        obj.ID       AS IDs
    FROM tblObjects obj WITH (NOLOCK)
    INNER JOIN tblFamily fam WITH (NOLOCK)
            ON obj.famID = fam.famID
        LEFT JOIN tblCountry cntry WITH (NOLOCK)
            ON (@Region IS NOT NULL) AND (fam.countryId = cntry.countryId)
        LEFT JOIN tblParent parent WITH (NOLOCK)
            ON (@ParentID IS NOT NULL) AND (obj.ID = parent.objectID)
        LEFT JOIN tblChild child WITH (NOLOCK)
            ON (@ChildID IS NOT NULL) AND (obj.ID = child.objectID)
    WHERE
        obj.Visible = 1
        AND obj.statusID IN (3,4,6,8)
        AND ((@DateFrom IS NULL) OR (obj.CreatedDate >= @DateFrom))
        AND ((@DateTo IS NULL) OR (obj.CreatedDate <= @DateTo))
        AND ((@Region IS NULL) OR (cntry.regionID = @Region))
        AND ((@FamilyID IS NULL) OR (obj.famID = @FamilyID))
        AND ((@ParentID IS NULL) OR (parent.parentID = @ParentID))
        AND ((@ChildID IS NULL) OR (child.childID = @ChildID))

解决方案

Temporarily add some logging to verify what is actually causing the timeout problem. You can do this in your application and it the stored procedure itself. Have the procedure write the current timestamp and the parameters used to invoke the procedure to a logging tables when it's executed. Also add logging to your application. Then you'll be able to identify when specific timeouts occur if there are certain parameters causing the problem or if the problem is in the procedure at all.

In general user defined functions aren't a good idea, though my understanding is that a table inline function is better than some of the others. It adds a lot of overhead and the optimizer can't work properly with UDFs.

这篇关于在ASP.net应用TSQL IVF造成超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！