Solved

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

Posted on 2008-10-26
6
383 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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