Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.

I am running the following script to do it:

USE [Cvti101687]
go
truncate table [IDAT_MR_NOTEHISTORY]
go
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_MR_NOTEHISTORY]') AND name = N'PK_iMR_NOTES')
    ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] DROP CONSTRAINT [PK_iMR_NOTES]
GO
USE [101687_test2]
GO

declare @ChunkCounter int
declare @ChunkSize int
set @ChunkCounter = 0
set @ChunkSize = 50000

--hack to do a do-while loop in sql
while 1 = 1
begin
    insert into cvti101639..IDAT_MR_NOTEHISTORY with (tablock)
    SELECT newid(), '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000'
    ,'00000000-0000-0000-0000-000000000000',    ISNULL(TargetClientAccountNum, [REC_CLINUM]), ISNULL(TargetPetAccountNum, '     ')
    ,ISNULL(pck_desc, 'Converted Medical Record'), isnull([REC_DOCTOR], '99999'), isnull([REC_ENTRY],''), isnull(REC_ENTRY,''),[rec_dattim], 0
    FROM [RECORD]
    left join [WVSSPCK] on pck_code = 99 and pck_link = [REC_TAG]
    left join cvti101639..idat_patients p on p.oldAccountNum = REC_CASE
    where rec_deleted = 0 and (rec_rtype = 0 or rec_rtype is null)
        and (TargetClientAccountNum is not null or [REC_CLINUM] is not null)
        and rec_id >= @ChunkCounter and rec_id < @ChunkCounter + @ChunkSize

    --If no rows inserted, break out of the loop
    if(@@rowcount = 0)
        break;

    set @ChunkCounter = @ChunkCounter + @ChunkSize
end

go
USE [Cvti101687]
go
ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] ADD  CONSTRAINT [PK_iMR_NOTES] PRIMARY KEY CLUSTERED 
(
    [cvtGUID] ASC
)

To speed things up, I attempted to drop the clustered index on the destination table and rebuild it at the end (Yes, I know it's a clustered index on a GUID, I am just the conversions guy, I don't get a say in how the database is designed).

However, when looking at the actual execution plan from one of the iterations of the while loop of the insert, I see that it is performing a Clustered Index Insert on my index that I already dropped. Furthermore, the sort it is performing before the clustered index insert costs 69% of each chunk.

  |--Clustered Index Insert(OBJECT:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[PK_iMR_NOTES]), SET:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtGUID] = RaiseIfNull([Expr1012]),[Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtClientGUID] = [Expr1013],[Cvti
        |--Parallelism(Gather Streams, ORDER BY:([Expr1012] ASC))
             |--Sort(ORDER BY:([Expr1012] ASC))
                  |--Compute Scalar(DEFINE:([Expr1012]=newid(), [Expr1013]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1014]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1015]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1016]={guid'
(snip)

Here is a copy of the actual execution plan after it has completed the first few iterations of the chunking loop. Just take the xml and save it as a .sqlplan file and it should be able to be opened in Management Studio.

What am I doing wrong that is causing this not doing a heap insert?

share|improve this question

closed as off-topic by Max Vernon, Paul White, RolandoMySQLDBA, bluefeet, Jon Seigel Oct 2 '13 at 23:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, RolandoMySQLDBA, bluefeet, Jon Seigel
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Could it be that you remove the index in database Cvti101687 and you insert into a table in database cvti101639. –  Mikael Eriksson May 4 '12 at 6:02
    
@MikaelEriksson Wow, thanks for catching the typo. Let me fix it and see what happens –  Scott Chamberlain May 4 '12 at 6:05
    
@MikaelEriksson that was it, thanks! Can you post it as a answer so I can give you the credit. –  Scott Chamberlain May 4 '12 at 6:08
    
You can query sys.objects too for a primary key –  gbn May 4 '12 at 6:48

1 Answer 1

up vote 5 down vote accepted

You are removing the primary key in database Cvti101687 and inserting to a table in database cvti101639.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.