Solved

Insert 24mil records to SQL Table using insert - improve speed - SQL Server 2000

Posted on 2008-10-26
6
385 Views
Last Modified: 2008-11-06
I need to copy the results of a query to a table.  When I use the select on the required records it runs in about 4 minutes.  When I do the insert it takes 5h45m. The recieving table already has records, so this is an append of records.
How can I improve the speed of insert statement?
This is the statement.  There are indexes on the query tables but NO indexes on the table I am writing to.  I recreate them at the end of the INSERT.

Thank-you!
insert into	_SLR_Cost_EpisodeServiceDetail
SELECT     ca.Version_Id, ca.Episode_Id, ca.UniqueEpisode_Id, ca.Service_Id, ca.ServiceCode_Id, ca.ServiceCode_Code, ca.DateOfService, ca.Cost, ca.CostGroup, ca.DepartmentType, ca.Mapping, ca.CostOutput, ca.row_id
FROM         dbo._SLR_Cost_EpisodeServiceDetail_Temp1 ca LEFT OUTER JOIN
(SELECT     TOP 100 PERCENT ccc.Version_Id, ccc.Episode_Id, cce.UniqueEpisode_Id, ccc.Service_Id, ccc.ServiceCode_Id, ccc.ServiceCode_Code, ccc.DateOfService, MAX(ccc.Cost) AS Cost, ccc.CostGroup, ccc.DepartmentType, ccc.Mapping, ccc.CostOutput, ccc.row_id
FROM          dbo._SLR_Cost_EpisodeServiceDetail_Temp1 ccc INNER JOIN
dbo._SLR_EpisodesCritCare cce ON ccc.Version_Id = cce.Version_Id AND ccc.Episode_Id = cce.Episode_Id
WHERE      (ccc.DateOfService BETWEEN cce.StartDate AND cce.EndDate) AND (ccc.CritCareFlag = 'Y')
GROUP BY ccc.Version_Id, ccc.Episode_Id, cce.UniqueEpisode_Id, ccc.Service_Id, ccc.ServiceCode_Id, ccc.ServiceCode_Code, ccc.DateOfService, 
ccc.CostGroup, ccc.DepartmentType, ccc.Mapping, ccc.CostOutput, ccc.row_id) cco ON ca.Version_Id = cco.Version_Id AND 
ca.Episode_Id = cco.Episode_Id AND ca.Service_Id = cco.Service_Id
WHERE     (cco.Version_Id IS NULL)

Open in new window

0
Comment
Question by:sjjcat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Assisted Solution

by:Clausewitz
Clausewitz earned 100 total points
ID: 22807216
maybe setting the size of the target-db and the growing factor can improve your query.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22808521
The problem is pretty obvious to and that is the GROUP BY clause and to a lesser extent the unnecessary TOP 100 PERCENT.  You are obviously trying to pull out the distinct values from a table that are missing in dbo._SLR_Cost_EpisodeServiceDetail_Temp1, since you are ignoring all the values from the derived table it would seem this would be the same and more efficient:

insert      _SLR_Cost_EpisodeServiceDetail
SELECT      ca.Version_Id,
            ca.Episode_Id,
            ca.UniqueEpisode_Id,
            ca.Service_Id,
            ca.ServiceCode_Id,
            ca.ServiceCode_Code,
            ca.DateOfService,
            ca.Cost,
            ca.CostGroup,
            ca.DepartmentType,
            ca.Mapping,
            ca.CostOutput,
            ca.row_id
FROM      dbo._SLR_Cost_EpisodeServiceDetail_Temp1 ca
            LEFT OUTER JOIN (
                  SELECT            ccc.Version_Id,
                                    ccc.Episode_Id,
                                    ccc.Service_Id
                  FROM            dbo._SLR_Cost_EpisodeServiceDetail_Temp1 ccc
                                    INNER JOIN dbo._SLR_EpisodesCritCare cce ON ccc.Version_Id = cce.Version_Id AND ccc.Episode_Id = cce.Episode_Id
                  WHERE            ccc.DateOfService BETWEEN cce.StartDate AND cce.EndDate
                                    AND ccc.CritCareFlag = 'Y'
                  GROUP BY      ccc.Version_Id,
                                    ccc.Episode_Id,
                                    ccc.Service_Id) cco ON ca.Version_Id = cco.Version_Id
                                                                        AND ca.Episode_Id = cco.Episode_Id
                                                                        AND ca.Service_Id = cco.Service_Id
WHERE      cco.Version_Id IS NULL

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22808524
It that does not work for you, that copy all the results of the derived table to a temporary table, index it appropriately and then do the Left join on that.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22809471
You can also try
insert      _SLR_Cost_EpisodeServiceDetail WITH (TABLOCK)
to exclusive lock the destination table while inserting.
You can see an insert performance increase by exporting your select query to a native data file using BCP, then import into the table using BCP with the -n and -h switches. Given that you have no clustered index on the destination, it probably won't make a difference though.
 
0
 
LVL 1

Accepted Solution

by:
sjjcat earned 0 total points
ID: 22860391
Hi
The solution ended up being more of a maintenance issue than something specific to the query.
The DB drive was majorly fragmented, so a defrag was run.
Also, the update queries have now been installed into DTS and the entire process runs in around 1 hour - for not only the 24mil record update step but around 20 additonal insert steps.
Thanks for the hints!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22863782
That is still way too long.  But whatever works for you...
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question