CODEWITHSUNDEEP
sql-serverazure-sql-database
Sumit Gupta
Sumit Gupta

Reputation: 2192

Delete Query: 14 minute to no response

I have a Sql Azure database, everything was good from last 6 months until today, when a simple

Delete from ListData where ListID=2323

fail to delete 7500 records out of 1.9 Million records after 14 minutes of running query. However Select query take less than 2-3 second to list them all.

Previously the delete works much like select and it usually take less than 20 second to finish delete operation. something is wrong today only.

Complete database size is 1.1GB where as we have Web edition set at 5GB so, we have plenty of space available.

Any idea what is going wrong? that delete has cause some serious problem in system which cause my client lose quite a money.

Thanks for any guide.

Edit: I do have couple of Index on table, but no trigger, FK or any other such thing in table. LISTID is foreign key [logically], and RecordID [another column in table] is auto increment id in Listdata table.

*Edit 2 *:

    /****** Object:  Table [dbo].[tblSalesListData]    Script Date: 02-07-2013 11:45:14 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[ListData](
        [RecordID] [bigint] IDENTITY(1,1) NOT NULL,
        [ListID] [bigint] NULL,
        [SalesID] [bigint] NULL,
        [UserID] [varchar](100) NULL,
        [FirstName] [varchar](100) NULL,
        [MiddleName] [varchar](50) NULL,
        [LastName] [varchar](50) NULL,
        [Address1] [varchar](100) NULL,
        [Address2] [varchar](100) NULL,
        [City] [varchar](100) NULL,
        [State] [varchar](100) NULL,
        [ZipCode] [varchar](10) NULL,
        [WorkPhone] [varchar](15) NULL,
        [HomePhone] [varchar](15) NULL,
        [CellPhone] [varchar](15) NULL,
        [Email] [varchar](100) NULL,
        [DealerCode] [varchar](20) NULL,
        [IsPrinted] [varchar](10) NULL,
        [tag] [varchar](100) NULL,
        [RecordDate] [datetime] NULL,
        [CustomInfo] [text] NULL,
        [SourceData] [text] NULL,
     CONSTRAINT [PK_ListData] PRIMARY KEY CLUSTERED 
    (
        [RecordID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[ListData] ADD  DEFAULT ('N') FOR [IsPrinted]
    GO

Upvotes: 3

Views: 288

Answers (3)

Sumit Gupta
Sumit Gupta

Reputation: 2192

Thanks for @Dinup and @Devart that give me idea, though I didn't fully follow their word, but both of them guide me in direction where I found solution as :

  1. Delete all Indexes based on my ListID.
  2. Run my Query, it take less than 1 second now.
  3. Recreate my indexes.

Happy Living.

Upvotes: 0

Devart
Devart

Reputation: 121902

Try to add index and change table structure -

CREATE TABLE dbo.tblSalesListData
(
    RecordID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ListID BIGINT NOT NULL, -- NULL --> NOT NULL
    SalesID BIGINT NULL,
    UserID VARCHAR(100) NULL,
    FirstName VARCHAR(100) NULL,
    MiddleName VARCHAR(50) NULL,
    LastName VARCHAR(50) NULL,
    Address1 VARCHAR(100) NULL,
    Address2 VARCHAR(100) NULL,
    City VARCHAR(100) NULL,
    [State] VARCHAR(100) NULL,
    ZipCode VARCHAR(10) NULL,
    WorkPhone VARCHAR(15) NULL,
    HomePhone VARCHAR(15) NULL,
    CellPhone VARCHAR(15) NULL,
    Email VARCHAR(100) NULL,
    DealerCode VARCHAR(20) NULL,
    IsPrinted VARCHAR(10) NULL,
    tag VARCHAR(100) NULL,
    RecordDate DATETIME NULL,
    CustomInfo VARCHAR(MAX) NULL, -- TEXT --> VARCHAR(MAX)
    SourceData VARCHAR(MAX) NULL  -- TEXT --> VARCHAR(MAX)
)
GO

CREATE /*UNIQUE*/ NONCLUSTERED INDEX IX_ListID ON dbo.tblSalesListData
(
    ListID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Upvotes: 2

Dinup Kandel
Dinup Kandel

Reputation: 2505

I have a little bit of same problem. I did the following step. but be sure to back up the database before doing these steps.

  • Create another table with identical structure
  • Insert the data in new table of the old table
  • Drop the old table

and try again to see how much time will it take.

Upvotes: 1

Related Questions