Solved

Importing data takes a long time Enterprise Manager

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

Expert Comment

by:Parag_Gujarathi
ID: 11970565
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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