?
Solved

Importing data takes a long time Enterprise Manager

Posted on 2004-09-02
9
Medium Priority
?
224 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

765 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