Solved

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

Posted on 2008-10-26
6
381 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now