Import taking too long?

Question

Import taking too long?

ShaneClark

SSC Enthusiast

Points: 120
More actions
November 7, 2003 at 6:02 am

#106812

Hi everyone. I import a DBASE table into an Access database and this will take about 15 minutes. The DBASE file is about 780 MB in size.
Importing the same table into SQL using DTS takes about 70 minutes. Is this acceptable do you think? I do not know a great deal about bulk-logging but the SQL database in question runs in Bulk-Logging mode and "Auto update statistics", "Auto create statistics", "Auto shrink" and "Torn page detection" are all ticked when looking at the database properties.
Can anyone please give me an opinion as to whether or not the import should take longer/shorter into SQL than Access and what I can do to speed up the process? FYI, the DBASE file has 2.9 million records.
Many thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

vambati SSCrazy Points: 2769 More actions · Answer 1

Check for the table design and are there too many indexes on that table, which can also slow the process down. Check for fragmentation of the data pages on that table. Though many miss this"fill factor" also palys a major role in this. Try using BCP instead of DTS...that should cut down your load time by 70%. Good luck.

ShaneClark SSC Enthusiast Points: 120 More actions · Answer 2

ShaneClark

SSC Enthusiast

Points: 120

November 10, 2003 at 3:09 am

#481381

Many thanks, I'll give BCP a try....

DimitrisAgelidis Ten Centuries Points: 1166 More actions · Answer 3

DimitrisAgelidis

Ten Centuries

Points: 1166

November 10, 2003 at 4:38 am

#481385

Do you use oledb connection or ODBC ?

ShaneClark SSC Enthusiast Points: 120 More actions · Answer 4

The DTS task was created using a wizard and uses the OLE DB connection rather than ODBC.

philcart SSC-Forever Points: 47796 More actions · Answer 5

Check out "Optimizing Bulk Copy Performance" in Books Online. It has a few tips for getting good performance.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Arden Weiss Right there with Babe Points: 749 More actions · Answer 6

I have done several VFP and SQL Server conversions and found that I can access SQL Server tables using VFP and the ODBC drivers for SQL Server, including the running of SWL Server Stored Procedures (calling stored procedures works best due to the 255 character limit on pass-thru SQL via VFP).

It seems that you should be able to do the same thing I do with VFP using dBase for Windows. It runs very quickly.

The added benefit is that you can then also use any of the existing dBASE reports by returning a dBASE cursor from SQL Server and run the existing reports from that cursor or it saved to a temporary DBF file.

Arden

DimitrisAgelidis Ten Centuries Points: 1166 More actions · Answer 7

DimitrisAgelidis

Ten Centuries

Points: 1166

November 10, 2003 at 11:33 pm

#481536

Use Odbc connection.

It's about 4 times faster.

ShaneClark SSC Enthusiast Points: 120 More actions · Answer 8

Thanks for the advice Phil, Weissa and Dimitris. I'll initially try amending the connection to use the ODBC drivers (easiest solution!!!) and see where I do with that.