Solved

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

Posted on 2008-10-26
6
382 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 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

16 Experts available now in Live!

Get 1:1 Help Now