Solved

Importing data takes a long time Enterprise Manager

Posted on 2004-09-02
9
183 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
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
 
LVL 1

Expert Comment

by:Parag_Gujarathi
ID: 11970565
0
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

18 Experts available now in Live!

Get 1:1 Help Now