1

2 days ago I created a function that needs INSERT and UPDATE, and I enabled XP_CMDShell and do it by running a script.

Afterwards, SQL Server works very slow for SELECT commands. Even very simple SELECT statement that runs after INSERT via separated command.

I tested this behaviour on other databases and it giae same result that SELECT runs after some minutes.

Also, I tested it on 2 other hosts with SQL Server 2014 and the result was the same.

Function that I created to get value of it within another select statement:

ALTER FUNCTION [Prg].[intCheckDelayedProcessProgram] 
(
     @SalesOrderProductID INT,
     @MainProductTreeID INT,
     @ProductTreeID INT,
     @ProcessId INT,
     @additionalDays INT = 2,
     @currentDateReverceString VARCHAR(10) = NULL
)
RETURNS INT
AS
BEGIN
    --declare @SalesOrderProductID INT = 40957,
    --  @MainProductTreeID INT = 93758,
    --  @ProductTreeID INT = 93758,
    --  @ProcessId INT = 4472,
    --  @additionalDays INT = 2,
    --  @currentDateReverceString VARCHAR(10) = null -- '30/09/1398'

DECLARE @ProduceDailyProgramProductTree_Id INT, @ProgramQuantity INT, @startDate JalaliDate,
        @CurrentDate JalaliDate, @lastDate JalaliDate, @additionalDate JalaliDate, @holiDaysCount INT;
IF(@currentDateReverceString IS NULL OR @currentDateReverceString = '')
    SET @CurrentDate = dbo.GetCurrentJalaliDate();
ELSE
    SET @CurrentDate = Gnr.RevercePersianDate(@currentDateReverceString);

IF (@additionalDays IS NULL)
    SET @additionalDays = 2;

DECLARE @allDelayedItemsCount INT = 0;
BEGIN
    DECLARE @sql NVARCHAR(4000), @cmd VARCHAR(4000);
    DECLARE cursor_pdppt CURSOR
        FOR SELECT pdppt.ID, pdppt.ProgramQuantity, pdppt.[Date] FROM Prg.ProduceDailyProgramProductTree pdppt
        WHERE pdppt.SalesOrderProductID = @SalesOrderProductID
                AND pdppt.MainProductTreeID = @MainProductTreeID
                AND pdppt.ProductTreeID = @ProductTreeID
                AND pdppt.Process = @ProcessId
                AND ISNULL(pdppt.IsDelayed, 0) = 0
    OPEN cursor_pdppt;
    FETCH NEXT FROM cursor_pdppt INTO @ProduceDailyProgramProductTree_Id, @ProgramQuantity, @startDate;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @lastDate = Gnr.RevercePersianDate([Prg].[intGetLastDateForDelayedProcessProgram](@startDate, @additionalDays, @CurrentDate, 1));
            IF (@CurrentDate.GetDate() > @lastDate.GetDate())
            BEGIN
                IF ((SELECT COUNT(*) FROM Prg.ProduceDailyOperation pdo WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id AND pdo.Process = @ProcessId
                        AND pdo.[Date].GetDate() > @lastDate.GetDate()) = 0)
                BEGIN
                    SET @allDelayedItemsCount = @allDelayedItemsCount + @ProgramQuantity;
                END
                ELSE
                BEGIN
                    SET @allDelayedItemsCount = @allDelayedItemsCount +
                        (@ProgramQuantity - 
                            (SELECT SUM(pdo.ProducedQuantity) FROM Prg.ProduceDailyOperation pdo 
                                WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id 
                                    AND pdo.Process = @ProcessId AND pdo.[Date].GetDate() <= @lastDate.GetDate()));
                END;

                SELECT @sql = 'UPDATE [Prg].[ProduceDailyProgramProductTree] SET [IsDelayed] = 1 WHERE ID = ' + CONVERT(VARCHAR(10), @ProduceDailyProgramProductTree_Id);
                SELECT @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d ' + DB_NAME() + ' -Q "' + @sql + '"'
                EXEC MASTER..XP_CMDSHELL @cmd , 'no_output'
            END;
            FETCH NEXT FROM cursor_pdppt INTO @ProduceDailyProgramProductTree_Id, @ProgramQuantity, @startDate;
        END;
    CLOSE cursor_pdppt;

    IF (@allDelayedItemsCount > 0)
    BEGIN
        IF (EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdppt
                        WHERE pdppt.SalesOrderProductID = @SalesOrderProductID
                            AND pdppt.MainProductTreeID = @MainProductTreeID
                            AND pdppt.ProductTreeID = @ProductTreeID
                            AND pdppt.Process = @ProcessId))
        BEGIN
            SELECT @allDelayedItemsCount = @allDelayedItemsCount 
                + (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
                    WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
                        AND pdpptd.MainProductTreeID = @MainProductTreeID
                        AND pdpptd.ProductTreeID = @ProductTreeID
                        AND pdpptd.Process = @ProcessId
                        AND ISNULL(pdpptd.Active, 0) = 1
                        AND ISNULL(pdpptd.IsDeleted, 0) = 0);

            SELECT @sql = 'UPDATE [Prg].[ProduceDailyProgramProductTreeDelayed] SET DelayedQuantity = ' 
                                + CONVERT(VARCHAR(10), @allDelayedItemsCount) + 'WHERE SalesOrderProductID = ' 
                                + CONVERT(VARCHAR(10), @SalesOrderProductID) + 'AND MainProductTreeID = ' 
                                + CONVERT(VARCHAR(10), @MainProductTreeID) + 'AND ProductTreeID = ' 
                                + CONVERT(VARCHAR(10), @ProductTreeID) + 'AND Process = ' 
                                + CONVERT(VARCHAR(10), @ProcessId) +';';
        END
        ELSE
        BEGIN
            SELECT @sql = 'INSERT INTO [Prg].[ProduceDailyProgramProductTreeDelayed] (SalesOrderProductID, MainProductTreeID, Process, ProductTreeID, DelayedQuantity, Active, IsDeleted) VALUES ('
                     + CONVERT(VARCHAR(10), @SalesOrderProductID) + ', ' 
                     + CONVERT(VARCHAR(10), @MainProductTreeID) + ', '
                     + CONVERT(VARCHAR(10), @ProcessId) + ', '
                     + CONVERT(VARCHAR(10), @ProductTreeID) + ', '
                     + CONVERT(VARCHAR(10), @allDelayedItemsCount) + ', ''1'', ''0'')';
        END;

        SELECT @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d ' + DB_NAME() + ' -Q "' + @sql + '"'
        EXEC MASTER..XP_CMDSHELL @cmd , 'no_output'
    END
    ELSE
    IF(EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
            WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
            AND pdpptd.MainProductTreeID = @MainProductTreeID
            AND pdpptd.ProductTreeID = @ProductTreeID
            AND pdpptd.Process = @ProcessId
            AND ISNULL(pdpptd.Active, 0) = 1
            AND ISNULL(pdpptd.IsDeleted, 0) = 0))
    BEGIN
        SELECT @allDelayedItemsCount = 
                (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
                    WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
                        AND pdpptd.MainProductTreeID = @MainProductTreeID
                        AND pdpptd.ProductTreeID = @ProductTreeID
                        AND pdpptd.Process = @ProcessId
                        AND ISNULL(pdpptd.Active, 0) = 1
                        AND ISNULL(pdpptd.IsDeleted, 0) = 0);
    END;
END;
RETURN @allDelayedItemsCount;
END

JalaliDate is user defined type to hold persian date time by Assembly.

Gnr.RevercePersianDate function to convert string persian date to JalaliDate. [Prg].[intGetLastDateForDelayedProcessProgram] other function to calculate specified date by Holidays that I think dose not issue.

UPDATE 1

I test function codes by pass parameter values by inline declare and set test values. Then, user Sql Server Profiler to check script of function, that see scripts stops on this line SET @CurrentDate = dbo.GetCurrentJalaliDate() that use function of Assembly. Execution running without any error and not responding!! Note: I test SELECT dbo.GetCurrentJalaliDate() that responding very fast without error!

UPDATE 2

I call function [Prg].[intCheckDelayedProcessProgram] within SP and inside a complex SELECT statement, that can't call SP in select, so just solution for me was define function. Code run in local server that is together with all data and other SP and functions. If there is alternative for CURSOR help me to rewrite function with it.

  • 1
    That's certainly an approach. I'm curious about your decision to use xp_cmdshell to invoke sqlcmd. Is there a reason you did that instead of directly inserting/updating the table? – billinkc Dec 20 '19 at 17:56
  • 1
    Great - same person, same question, different user. – SMor Dec 20 '19 at 18:32
  • 1
    Trying to use tricks likes this (and there are others, even worse) to get around the Function isolation restrictions is a very bad idea. Those restrictions exist for several very good reasons, and one of those reasons is potential locking problems. Those problems are made much worse by spawning an external process and trying to have it do the dirty work for you. It can cause, not just blocking and deadlock issues, but irresolvable ones. I would recommend starting with sp_lock while the problem is happening to see what is waiting on what, But there are other problems with this as well. – RBarryYoung Dec 23 '19 at 16:02
  • You should also post the code from the CurrentJalaliDate() function as well. And probably the code that is calling your SQL Function also. – RBarryYoung Dec 23 '19 at 16:02
  • Scalar UDFs can cause problems. See docs.microsoft.com/en-us/sql/relational-databases/…. That topic's Performance concerns apply to earlier versions of SQL Server. Instead of a scalar function, consider a Table Valued Function, or in-line the scalar function's T-SQL. To see the function calls, you need to trace the SP:StmtStarting event. A large number of events can be fired, quickly. That means you may need to quickly stop tracing! – Bill Dec 23 '19 at 17:10
10

Instead of using xp_cmdshell to execute queries against SQL Server, change your code to use stored procedures, user-defined functions, or even dynamic T-SQL called using sp_executesql. If you need to execute code against a different server, use a linked server.

Finally, consider rewriting your code not to use a cursor if possible. Set-based operations in SQL Server are far more efficient.

Since these topics are all extremely in-depth topics on their own, there is no way I can flesh out an answer here, but I hope these links help. Good luck.

  • Hi @Randolph, thank you for your reply. I edit question and add Update 2. Please read it. – Sayed Abolfazl Fatemi Dec 22 '19 at 12:16
  • See the comments on your question, and see the other answer(s), as there is some good advice there. Consider using table-valued functions (TVFs) to get around the issues you're seeing. – Randolph West Dec 23 '19 at 19:32
3

You should definitely find a better way than doing DML through a function. You don't need to attach this process to a SELECT statement. You can just dump the results of the query to a temp table and use a CURSOR to iterate over that (just like you are doing in this function), calling a stored procedure to do what you are currently doing in this function.

However, at the very least, you should add some options to your cursor so that they don't lock the base table(s):

DECLARE cursor_pdppt CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
        FOR ...

And, immediately after the CLOSE cursor_pdppt;, you should add:

DEALLOCATE cursor_pdppt;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.