Post reply

tuning sql statement which ran for an hour

  • June 27, 2019 at 9:15 am

    #3658016

    Hi All,

    Trying to delete rows from a table. it took 1 hour. It is deleting around 60K rows from 1 specific table. I see high waits for memory, pageiolatch_ex.

    other thing is that, I dont see any blocking as such during that 1 hour.

    sql stmt

    ======

    (@P1 bigint) DELETE FROM "dbo"."<<tablename>>" WHERE "<<columnname>>" = @P1

    some stats collected from Database performance analyszer

    Statistics

    Executions 55,126 Logical Writes 27,770

    Physical Reads 38,660 Logical Reads 151,587,517

    Rows Processed 55,126

    Can anyone provide inputs on how to go about making this query run faster.

    please attached zip file for plan and data.

    Thanks,

    Sam

    Attachments:
    You must be logged in to view attached files.

  • June 27, 2019 at 9:31 am

    #3658018

    Table size is almost 1GB. i.e. 842.07 MB

    Row count = 64750

    no triggers on the table .

     

    table script

    ============

     

    CREATE TABLE [dbo].[tname](

    [PARENT_PUB_KEY] [bigint] IDENTITY(1,1) NOT NULL,

    [CREATE_DATE] [datetime] NULL,

    [CREATED_BY] [nvarchar](100) NULL,

    [SOURCE_SYSTEM] [nvarchar](100) NULL,

    [INTERFACE] [nvarchar](50) NULL,

    [MDM_ID] [nchar](14) NULL,

    [PAYLOAD] [nvarchar](max) NULL,

    [BATCH_ID] [nvarchar](100) NULL,

    [MDM_TRIGGER] [nvarchar](100) NULL,

    [UPDATE_DATE] [datetime] NULL,

    [UPDATE_BY] [varchar](100) NULL,

    [PARTY_TYP_CD] [nvarchar](40) NULL,

    PRIMARY KEY CLUSTERED

    (

    [PARENT_PUB_KEY] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    -- no additional indexes

    -- no triggers on the table

    -- one observation what if the table has LOB datatypes ?

     

    • This reply was modified 5 years, 4 months ago by  vsamantha35. Reason: add more details
    • This reply was modified 5 years, 4 months ago by  vsamantha35. Reason: add details
    • This reply was modified 5 years, 4 months ago by  vsamantha35. Reason: edit for more details

  • June 27, 2019 at 10:14 am

    #3658025

    How are you running the query?  The query for which you posted the plan will only delete a single row, since your WHERE clause specifies a single value of the primary key.  Therefore, if you're deleting 60,000 rows (I suspect the exact number is 55,126) then that's how many times the query will run.  Given that, it's not surprising that it's taking an hour.  Rewrite the query so that it deletes all the rows in a single operation.

    John

  • June 27, 2019 at 11:56 am

    #3658053

    Hi John,

    It could be 55,126 rows matching with that parameter value and so it has executed that many times. correct me if I am wrong.

    Is there any other ways, to speed up the delete operation?

     

  • June 27, 2019 at 11:59 am

    #3658054

    No, because the parameter value is the primary key and so only one row can match.  You're deleting one row, changing the parameter value, deleting one row again, and so on.  That's why it's slow.

    John

  • June 27, 2019 at 2:06 pm

    #3658127

    okay. I get it.

  • July 1, 2019 at 8:54 pm

    #3659063

    if you want to preserve a small  number of rows in a table then to speed up the delete process you could. for example lets say you have a large audit table with overs a million rows, you only want to keep the last months data then you could.

    1. Write out the rows to keep in another table
    2. truncate the table
    3. write back the rows

     

    ***The first step is always the hardest *******

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply