Solved

Importing data takes a long time Enterprise Manager

Posted on 2004-09-02
9
222 Views
Last Modified: 2013-12-03
I am importing about 500 million records from a .csv (10GB) file in a PC to a SQL server 2000.

It has imported about 460 million (could be the total) it took around 10 hours to get to this point, now it is "executing" no more records are imported, it reads 459,180,000 records copied.

Space in the server is plenty. Memory ia 1GB

The network reports data movement between the SQL server and the PC.

Is this normal? How long can this take?

Is the end of the process like this? What can I do?
0
Comment
Question by:robrodp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11970423
How are you importing?  BCP or Bulk Insert (I would hope)?  DTS?

I would definately watch the log file growth (depending on how you're loading the data) and make sure you logs/data files are on different drives/raid groups.  Also, data this size, you wanna drop all indexes (unless they need to be used for the load--primary keys, lookups, etc) and add them after the data loads.....
0
 

Author Comment

by:robrodp
ID: 11970435
It is DTS. I dit not know there was a Bulk Insert. How do you do that.
I have a raid group. 3 drives, it starts up from drive c whichis independent.
0
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.

 
LVL 11

Expert Comment

by:ram2098
ID: 11970580
As Arbert said,

I think since the data is very huge, I think it is good if you would have dropped the indexes on these tables and load them after data load.

Even..I too observed the same behaviour as DTS takes long time as it reaches the end (when doing large data transfers), if the indexes are not dropped.

Totally agree with Arbert!

0
 
LVL 34

Expert Comment

by:arbert
ID: 11973809
If you click the "fast load" option in DTS, it will attemp to do bulk inserts....Also, if you know you're the only one using the tables being loaded, click the "lock table" option....

So you have 1 raid group and your data and logs are located on it???
0
 

Author Comment

by:robrodp
ID: 11974491
From enterprise manager I cannot find the fast load option in DTS (from the import data option)

And yes the data and logs arein the same raid group. Is that terrible?

Any help?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11974589
"And yes the data and logs arein the same raid group. Is that terrible?"

Not a good idea--not only from performance (I bet you will see a LOT of IO waits), but from a recoverability standpoint....I suppose it's also RAID5???

If you double click on the line between your source and target in the DTS designer, you have the options tab--down at the bottom in the SQL Server options frame, there is an option for TABLE LOCK and USE FAST LOAD.....
0
 

Author Comment

by:robrodp
ID: 11977990
Sorry I cannot find the "line between your source and target in the DTS designer".

I am using the import option in the enterprise manager. Is this correct?
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 11978275
If you use the import option in enterprise manger, it creates a DTS package behind the scenes.  You should be able to go to Enterprise mangaer, data transformation services, local packages, and choose your package....You should see a line between your source and target...
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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