SQL Server Performance

Brain stuck for following query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, Mar 2, 2007.

  1. waqar Member

    Hi,

    I have 2 tables

    TBL 1

    CREATE TABLE [dbo].[a](
    [col0] [int] NOT NULL,
    [col1] [int] NOT NULL,
    [col2] [int],
    [col3] [int]
    CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
    (
    [col0] ASC,
    [col1] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    TBL 2

    CREATE TABLE [dbo].(
    [col0] [int] NULL,
    [col1] [int] NULL,
    [col2] [int] NULL,
    [col3] [int] NULL
    ) ON [PRIMARY]

    I want to insert all unique record from table b to table a (uniqueness is only based on first 2 columns). Keeping in mind i have 10 million records in table b and cannot insert 1 by 1 records.

    insert into [a] select col0,col1,max(col2),max(col3) from group by col0,col1.
    Will this query work?



    ________________________________________________
    Be great in act, as you have been in thought.
  2. MohammedU New Member

    I don't see any problem with your query but make sure you have right index and tlog configure with enough space....


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. mmarovic Active Member

    That would be long running query and may singificantly increase the size of log file. To avoid that you can try to insert rows in cycle using smaller batches. One possible solution is:


    declare @max int
    declare @step int
    declare @first int

    select @first = min(col0), @max = max(col0)
    from a

    set @step = 100

    while (1=1) begin
    insert into a
    select col0, col1, max(col2), max(col3)
    from b
    where b.col0 between @first and @first + @step - 1

    set @first = @first + @step

    if @first > @max break

    end -while

    If database recovery model is full you may need frequent transaction log backups scheduled during the query execution.

Share This Page