Post reply

Import taking too long?

  • 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.

  • November 7, 2003 at 9:00 am

    #481273

    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.

  • November 10, 2003 at 3:09 am

    #481381

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

  • November 10, 2003 at 4:38 am

    #481385

    Do you use oledb connection or ODBC ?

  • November 10, 2003 at 4:43 am

    #481387

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

  • November 10, 2003 at 3:22 pm

    #481493

    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

  • November 10, 2003 at 6:41 pm

    #481525

    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

  • November 10, 2003 at 11:33 pm

    #481536

    Use Odbc connection.

    It's about 4 times faster.

  • November 11, 2003 at 12:18 am

    #481540

    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.

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

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