Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

MuadDubby (Programmer) (OP)
24 Oct 07 16:11
Hello

Importing an SQL Server 2005 table with 4.5 million records into MS Access through the MS Access import utility takes about 2.5 minutes.

With SSIS it takes over 20 minutes to do the exact same thing!!!

Any ideas on how to speed this up? I've tried using OleDB, native SqlServer and .NET for the source connection types and there's no difference.

Thx
.DaviD.

Helpful Member!  MuadDubby (Programmer) (OP)
5 Nov 07 17:05
One thing I've found so far ...

I've seen mentions of a "FastLoad" option that could supposedly be used on the connection string to MS Access and help speed things up. Thing is - I can't get a working connection string with this. If I add it, I'm told that the installable ISAM could not be found. What the ... ?

Getting a little frustrated here ...

Thx,
AlexCuse (Programmer)
5 Nov 07 21:06
Is SQL Server looking at the access database through a UNC path or a drive mapping?  

It might be helpful to provide DDL for the tables involved and an overview of what your package is doing as well.

Good Luck,

Alex

----signature below----
Now you can go where the people are one!
Now you can go where they get things done!

MuadDubby (Programmer) (OP)
5 Nov 07 21:49
Hi Alex

Thx for the response.

Everything (dbase, Access file) is local. No UNC or drive mappings in use.

I don't have access to the dbase right now, so can't really remember exactly what the layout is. But simply put, it's about six fields long, mostly nvarchars. The package itself doesn't do anything other than copy the records with absolutely no transformation. I even removed all keys and indexes on the detination table.

I'll put the exact table definition in here tomorrow.

The one thing I noticed today, though, was that a command line BCP also took about 2 minutes. The only problem with that is that it cannot be elegantly embedded in a .NET project; the closest I can get (to my knowledge) is by using the COM object for SQLDMO, and I'm really not crazy about that. I'd rather stick to SSIS if possible.

Thx again.
AlexCuse (Programmer)
6 Nov 07 11:59
You might be able to write a stored proc that executes the BCP command, and then execute that through ADO.net?  IIRC from the other forum, you are executing this from a C# app, right?

bcp

Let me know what you end up doing, I find myself needing to fill access tables from SQL a lot for various users, so this could be helpful to me.

Thanks,

Alex

----signature below----
Now you can go where the people are one!
Now you can go where they get things done!

MuadDubby (Programmer) (OP)
7 Nov 07 8:33
Alex,

I'll keep you posted. BCP seems to be out of the question since a BCP export from SQL Server can apparently only go to a text or BCP binary file. I've found no way to use MS Access as the destination.

My hopes are still on SSIS, but they're shrinking faster that the polar ice caps.
AlexCuse (Programmer)
7 Nov 07 8:56
I didn't think you could, but I don't use BCP very often...

Have you tried setting up a linked table in access to your SQL Server table?  You could then fill your other table from the linked table, or run a mak-table query.  I imagine it would be as fast as the access import (or faster).

Good Luck,

Alex

----signature below----
Now you can go where the people are one!
Now you can go where they get things done!

MuadDubby (Programmer) (OP)
7 Nov 07 9:19
Not a bad idea, except that doesn't work within the context of my project. The project is a 100% c# program that takes data from SQL Server, applies some business rules and transformations to it, and then dumps the results to a new table.

Several tables are processed in this fashion, and when they're done, they're copied to Access (which is what the end user uses).

My preference is for finding a way to copy the data to Access within C# so that the project is uniform and doesn't use outdated mechanisms.
AlexCuse (Programmer)
7 Nov 07 9:51
To be perfectly honest, I think using Access for a table this large is an outdated mechanism itself.  If you need a faster solution than SSIS, perhaps we should start by talking about what the end user is doing with this data, and why they can't access it on the SQL Server itself?  And where does C# enter the picture?  Is access serving as data storage for the C# app, or is the C# app simply used to refresh the data for users (who use access)?

If the whole purpose is just to get this data into access tables, then you could have SSIS package run as a scheduled job, and schedule it during off hours.  Then, the time it takes is not so much of an issue.

If you want the user to always have the most up to date information, then use ADO within access to connect to SQL and refresh your local tables each time the access app is opened.

Over many posts, you have yet to give the whole story about what is going on here, but I am starting to suspect the problem is really that you're trying to drive a screw in with a hammer.

Hope this helps,

Alex

----signature below----
Now you can go where the people are one!
Now you can go where they get things done!

MuadDubby (Programmer) (OP)
8 Nov 07 13:34
Ok, I think this nightmare has finally come to a close.

Several points:

1) I agree that Access should not be used. But you know how hard it is to change legacy apps and procedures. It'll change, but not today. The customer has been using Access since the age of the dinosaurs (give or take a few millenia), and will continue to do so for the forseable future.

2) For some bizzare reason, the fastest way to copy the table from SQL Server to MS Access is still by doing an import from Access. So I'm not very proud of the approach I followed (since I had to pollute my project with COM and automation), but I see no other way for the moment:

I created a VBA module that does an import from an SQL server, and it receives three parameters: a DSN name to use (Access doesn't support dynamic connnection strings), a source table name and a target name:

CODE

Public Function Import(dsnName As String, sourceTableName As String, targetTableName As String)

    On Error GoTo CopyTable
    DoCmd.DeleteObject acTable, targetTableName

CopyTable:
    DoCmd.TransferDatabase _
        acImport, _
        "ODBC Database", _
        "ODBC;DSN=" + dsnName, _
        acTable, _
        sourceTableName, _
        targetTableName
End Function

I then added a macro that calls this function, and called the macro "Import".

And finally, in my C# project, I added a reference to the Microsoft Access 9.0 Object Library, and added the following code in order to call the macro (based on an MSDN article at http://support.microsoft.com/kb/306683)

CODE

using Access;

// Create an instance of Microsoft Access, make it visible,
// and open Db1.mdb.
Access.ApplicationClass oAccess = new Access.ApplicationClass();

try
{
oAccess.Visible = true;
oAccess.OpenCurrentDatabase(mQualifiedDatabaseName, false);

oAccess.GetType().InvokeMember("Run",
    System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
    null, oAccess, new Object[] { "Import", mDestDsn, sourceTableName, targetTableName });
}
finally
{
// Quit Access and clean up.
oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
}

This works flawlessly, with one thorn: I have no progress indication. I've tried to find a way to trap events from the Access object, but haven't really gone very far. It doesn't seem to be well documented, and I feel I'm in uncharted waters on this one. Haven't found too much about it on the net.

I also tried launching a separate thread that does a "SELECT COUNT(*)" on the target table every few seconds, but that failed too since Access maintains a full lock on the target table until the copy is complete, and the SELECT fails.

All in all, the damned thing works but I've now added COM to my project and have lost the ability to show any progress to the user. Not the best solution, but better than nothing.

If you have any ideas on how to trap progress events from the Access object, or how to get Access to copy a table without locking it (e.g. disable transactions somehow?) it would be appreciated.

Thx.
MuadDubby (Programmer) (OP)
8 Nov 07 14:26
Minor correction to the C# code (Access doesn't shutdown properly otherwise):

CODE

finally
{
    // Quit Access and clean up.
    oAccess.CloseCurrentDatabase();
    oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveAll);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
    oAccess = null;
}
MuadDubby (Programmer) (OP)
28 Nov 07 21:02
Hi Alex
I just finished polishing up an article on this. You can see it (along with a sample project that shows how to do this) here: http://www.codeproject.com/useritems/MSAccessTableCopyInCSharp.asp
AlexCuse (Programmer)
29 Nov 07 14:18
Thanks for posting back, I will have a look at it when I get a chance.

----signature below----
You can't fit a square data in a round table

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close