Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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

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
sjjcat
Asked:
sjjcat
2 Solutions
 
ClausewitzCommented:
maybe setting the size of the target-db and the growing factor can improve your query.
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
Technology Partners: 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!

 
nmcdermaidCommented:
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
 
sjjcatAuthor Commented:
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
 
Anthony PerkinsCommented:
That is still way too long.  But whatever works for you...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now