1

I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:

   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,
    
    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN 

I am getting warning from blitzcache as Forced serialization, Expensive sort, non-sargable and Merge join "Mant to Many" = True

The only index we had is on id which is a NC index with id as leading key column.

I will try to update my question if execution plan if i can. Table in SELECT is around 100 million as of now. Also it has PK/CI on col1.

I have asked users why we do * in that TOP which pulls almost 50+ columns but in update use just fewer, may b i am thinking wrong but just came to my mind.

Adding index to modified time also does not seem to help.

Please suggest what else can be checked. Thanks

plan- Index seek has happened on NC index id enter image description here

4
  • 1
    1) Could u add the table definition? 2) could u paste the plant to brentozar.com/pastetheplan ? 3) you are ordering the whole table whixh is aperformance hog. Is id the PK? I eould say a preselect of ordering only PK then selecting the columns could be better Jan 22 at 21:52
  • I would try on getting 1, 2 is slightly not possible due to secure env. Id is non clustered index, col1 is the PK/CI on the BatchChangeTable
    – Newbie-DBA
    Jan 22 at 22:51
  • pastetheplan obfuscates the tables etc while providing statistics Jan 22 at 23:32
  • @Newbie-DBA 1) You are correct to question the SELECT ... *, it is bad practice in most cases to do that. List only the columns you need in the SELECT list. This typically reduces the amount of data that needs to be loaded from disk, and also can sometimes result in a better execution plan based on if the fields you're selecting are covered by the index used. 2) Without seeing your execution plan it's like asking us to pour you a glass of water, without a glass or water, there's unfortunately not much that can be recommended. Please consider obfuscating it and posting it.
    – J.D.
    Jan 23 at 15:16
2

Warnings

  • The forced serialization is because of the OUTPUT (with no target or to a @table variable will cause this, but not a #temp table)
  • The expensive sort is because you're ordering by ModifiedTime without a supporting index and query pattern
  • The non-SARGable warning is because (if I remember how I wrote the check correctly), of the local variables.

How to fix it

You'll need a good supporting index:

CREATE INDEX super_awesome
ON dbo.BatchChangeTable
    (id, ModifiedTime)
INCLUDE
    (EnabledDate);

But the way the query is written, IN will be interpreted as a range predicate, and the ORDER BY ModifiedDate will likely still cause a Sort operation. You'll need to change the form to use two equality predicates with a UNION ALL in order to take full advantage of the index.

You're also going to need to rewrite your query to use dynamic SQL so that you don't get hit with the side effects of local variables.

The finished product will look something like this:

BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
    u.id = @StatusIP,
    u.EnabledDate = GETDATE()
OUTPUT
    deleted.col1,
    deleted.col2,
    deleted.col3,
    deleted.col4,
    Inserted.id
INTO #output 
FROM
(
    SELECT TOP (5000)
        b.*
    FROM 
    (
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @status_D
         
         UNION ALL 
         
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @Status_F
         ORDER BY b2.ModifiedTime
    ) AS b
    ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                   N'@status_D int,
                     @StatusIP int,
                     @Status_F int',
                     @status_D,
                     @StatusIP,
                     @Status_F;

SELECT 
    o.*
FROM #output AS o

COMMIT TRAN;         

You'll need to tailor this a little to suit your actual situation, but it should get you going in the right direction.

0
1

ideas based on available info:

  1. add nonclustered index (id, ModifiedTime) or (ModifiedTime, id), not sure based on your info
  2. try to rewrite update .

code:

update BatchChangeTable 
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id
where col1 in (select top 5000 col1 from BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime])
0

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.