Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looking for faster Insertion of records Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 4:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:28 AM
Points: 37, Visits: 251
HI,

I need some suggestion to reduce the execution time of the below insert statement.

INSERT INTO New_Table(CD,PD,PED,Itm)

Select m.CD,a.PD,t1.PED,i.Itm--Record Count of this query is 95000000
From Table1 t1
JOIN View_V m ON m.RN=t1.CD
JOIN Table2 i on i.Mne=t1.Itm
UNION
SELECT m.CD,t2.PD,t2.PED,i.Itm--Records Count of this query is 190000000
FROM Table1 t2
JOIN View_V m ON m.RN=t2.CD
JOIN Table2 i on i.Mne=t2.Itm

On "New_Table" the 4 columns((CD,PD,PED,Itm)) are Primary key. There are other columns as well in the table. On Primary key there is a clustered index.It takes 50 mins to insert these records into the New_table.

Thanks
PS

Post #621890
Posted Thursday, December 18, 2008 5:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:04 PM
Points: 2,663, Visits: 5,798
Some things to try -

If there is no possibility that the two queries (either side of the union) can return duplicates you could make it "union all" which may save a little time.

Apart from that check if the time is in the selects (run them independantly) or the insert.

If the selects - look at indexes on the source tables
If the insert - look at basics like disc config, log on different disc to data, log NOT on raid 5 etc etc.

Mike John





Post #621959
Posted Thursday, December 18, 2008 6:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
Is it the query that's slow or the insert? Can you post an execution plan?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #622028
Posted Wednesday, December 22, 2010 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 11, 2011 8:11 AM
Points: 1, Visits: 26
Drop the primary key and index on the insert table. Add keys after data has been loaded.
Post #1038308
Posted Wednesday, December 22, 2010 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
Given nearly 300 million records being inserted it is going to take a bit of time no matter how you slice it. Retrieving the data could be a bottleneck but, there is a lot of disc I/O with that much data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1038340
Posted Wednesday, December 22, 2010 9:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
How many records does it usually insert?

As Grant asked, which part is slow, the query or the insert?

CEWII
Post #1038349
Posted Wednesday, December 22, 2010 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
The OP has record counts as comments in the query. 95 million and 190 million.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1038352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse