Huge speed loss comapred to MSAccess import 1

[MuadDubby]

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.

 

[MuadDubby]

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]

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

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

 

[MuadDubby]

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]

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

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

 

[MuadDubby]

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]

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

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

 

[MuadDubby]

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]

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

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

 

[MuadDubby]

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:

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)

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]

Minor correction to the C# code (Access doesn't shutdown properly otherwise):

finally
{
    // Quit Access and clean up.
    oAccess.CloseCurrentDatabase();
    oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveAll);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
    oAccess = null;
}

 

[MuadDubby]

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]

Thanks for posting back, I will have a look at it when I get a chance.

[small]----signature below----[/small]
You can't fit a square data in a round table