SQL Server Performance

Performance Issues on Insert Into Partitioned View

Discussion in 'Performance Tuning for DBAs' started by ozamora, Jun 28, 2004.

  1. ozamora New Member

    I have a Partitioned view which is a UNION ALL of the last 14 days of batches created from my database. For each day there is a range of batches.

    A sample DDL of a table:

    CREATE TABLE [batchResults_20040428] (
    [batch_id] [int] NOT NULL ,
    [list_id] [int] NOT NULL ,
    [subscriber_id] [int] NOT NULL ,
    [status_id] [int] NOT NULL ,
    [daterefered] [datetime] NULL ,
    [referal] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [datecreated] [datetime] NOT NULL ,
    PRIMARY KEY CLUSTERED
    (
    [batch_id],
    [list_id],
    [subscriber_id]
    ) WITH FILLFACTOR = 60 ON [OEN_ARCHIVE_ACTION_2004_04_15] ,
    CONSTRAINT [CK_batchResults_20040428_check] CHECK ([batch_id] >= 13220 and [batch_id] <= 13367)
    ) ON [OEN_ARCHIVE_ACTION_2004_04_15]
    GO

    Every day at midnight a new table is created and added to the view, and the oldest dropped from the view.

    The table is created with FILLFACTOR = 60 in order to allow a big amount of rows to be inserted.

    I am having issues when inserting into the view with a statement similar to:

    INSERT INTO batchresults
    (batch_id, list_id, subscriber_id, referal, status_id, datecreated)
    SELECT batch_id, list_id, subscriber_id, referal, status_id, GetDate()
    FROM batchresultsqbuffer
    WHERE action_id = 5 AND deliverystatus_id <> 2

    If I dump the resultset into an empty table it will fly. But inserting into the view is taking between 2 and 7 minutes per 50~200k rows.

    This is a sample of a table SHOWCONTIG

    DBCC SHOWCONTIG scanning 'DeliveryText_20040621' table...
    Table: 'DeliveryText_20040621' (967674495); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 423672
    - Extents Scanned..............................: 53176
    - Extent Switches..............................: 398817
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 13.28% [52959:398818]
    - Logical Scan Fragmentation ..................: 47.66%
    - Extent Scan Fragmentation ...................: 0.48%
    - Avg. Bytes Free per Page.....................: 2749.8
    - Avg. Page Density (full).....................: 66.03%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    Any feedback will be appreciated

Share This Page