Looking for faster Insertion of records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Looking for faster Insertion of records

Looking for faster Insertion of records

Rate Topic

Display Mode

Topic Options

Author

Message

PNS

Posted Thursday, December 18, 2008 4:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:12 AM
Points: 37, Visits: 243

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

Mike John

Posted Thursday, December 18, 2008 5:33 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 2,639, Visits: 4,549

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

Grant Fritchey

Posted Thursday, December 18, 2008 6:50 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,375, Visits: 25,159

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

etfocus

Posted Wednesday, December 22, 2010 8:34 AM

Forum 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

Sean Lange

Posted Wednesday, December 22, 2010 9:20 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1038340

Elliott Whitlow

Posted Wednesday, December 22, 2010 9:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

How many records does it usually insert?

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

CEWII

Post #1038349

Sean Lange

Posted Wednesday, December 22, 2010 9:31 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1038352

« Prev Topic | Next Topic »

Permissions