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
Solved

Importing data takes a long time Enterprise Manager

Posted on 2004-09-02
9
203 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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