Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Question Unanswered: Append query performance

    Hi all

    I have a tables in MS Access with about 30,000 rows. I use an append query to insert these rows into a linked SQL Server 2000 table.

    This process, which should take a minute or so, takes about 20 minutes.

    I ran the SQL Profiler and noticed that the Jet engine actually sends 20,000 individual INSERT statements to SQL Server, hence the inordinate amount of time this is taking.

    Is there a way to force MS Access to send the data to SQL Server in a single

    INSERT INTO SQL_SERVER_TABLE
    (SELECT * FROM ACCESS_TABLE)

    statement. The query builder suggests this will occur but in practice the Jet engine is intervening and breaking it up into 30,000 SQL statements.

    Is there perhaps another technique I can use to improve the performance of this append query?

    Thanks
    Matt

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Re: Append query performance

    Hey Matt:

    Yeah, talking to a linked SQL Server table in Access can be really slow!

    Just to make sure I understand the way you have things set up...you have a linked SQL Server table through ODBC/DSN, and it has the little globe icon next to the linked table?

    Anyway, I cannot think of a way to improve performance in a simple way with how you are doing things if you want to port data from Access to SQL Server, but here are a few thoughts...

    First, I've sometimes found if you pull data from a data source rather than push data to a data destination, the process will run faster. For example, right now, you are pushing data from Access (source) to SQL Server (destination). If you are able to...maybe try pulling the data from Access within some type of SQL Server process (DTS, stored proc, bcp, etc.) That way, you can use an OLEDB Provider for Jet (a lot faster than the generic ODBC Jet Driver that Access uses) or bypass Jet all together.

    As far as pulling data from Access to SQL Server, here are some ways to try if your application architecture will allow it:

    -- Bulk Insert. Export the source Access table to a text file. Then create a stored procedure in SQL Server that contains a BULK INSERT command to import the text file into the destinatination table in SQL Server. If you have some automated process in Access to do a bunch of stuff, you can automate the export and through VBA, then talk to SQL Server through ADO and execute the stored proc. by using the COMMAND object. The big advantage here is you can trap for errors through the ADO Errors collection and handle them gracefully if the insert fails for some reason.

    -- bcp. Pretty similar to BULK INSERT. This is a command line utility that you can call manually or in an automated way in Access. You'll need a format file describing the various fields in the source/destination. The down side to doing it this way is you cannot trap for errors/exceptions if the insert fails.

    -- DTS. If you are not tied into a fully automated process in Access to push the data to SQL Server, try creating a DTS package directly on SQL Server with a data pump between the Access and SQL Server tables. This should run faster than the way you are doing things now b/c you can choose an OLEDB Provider for Jet which is a lot faster than the generic ODBC driver for Jet / System DSN you have set up in Access for the linked SQL table.

    There are many other ways to accomplish the same thing, such as maybe creating a linked server to the Access database within SQL Server. Also, you may want to temporarily remove indexes in the SQL table, run the import, and then recreate the indexes. Another thing would be to use a table locking hint to exclusively acquire a table lock on the destination table.

    Anyway, without knowing much about your architecture/environment, and assuming you want to do this in an automated way, I would recommend the BULK INSERT in a stored proc first. I think you will get the best performance overall. If you have no problem manually doing this in a separate process, then just do a DTS in SQL Server.

    If you have any other thoughts, feel free to ask!

    HTH.

    Kael MCSD, A+

  3. #3
    Join Date
    Feb 2004
    Posts
    199
    are you making an application to append lines to SQL by othe users often or you need this operation done just once?
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Re: Append query performance

    Originally posted by kaeldowdy
    Hey Matt:

    Yeah, talking to a linked SQL Server table in Access can be really slow!

    Just to make sure I understand the way you have things set up...you have a linked SQL Server table through ODBC/DSN, and it has the little globe icon next to the linked table?

    Anyway, I cannot think of a way to improve performance in a simple way with how you are doing things if you want to port data from Access to SQL Server, but here are a few thoughts...

    First, I've sometimes found if you pull data from a data source rather than push data to a data destination, the process will run faster. For example, right now, you are pushing data from Access (source) to SQL Server (destination). If you are able to...maybe try pulling the data from Access within some type of SQL Server process (DTS, stored proc, bcp, etc.) That way, you can use an OLEDB Provider for Jet (a lot faster than the generic ODBC Jet Driver that Access uses) or bypass Jet all together.

    As far as pulling data from Access to SQL Server, here are some ways to try if your application architecture will allow it:

    -- Bulk Insert. Export the source Access table to a text file. Then create a stored procedure in SQL Server that contains a BULK INSERT command to import the text file into the destinatination table in SQL Server. If you have some automated process in Access to do a bunch of stuff, you can automate the export and through VBA, then talk to SQL Server through ADO and execute the stored proc. by using the COMMAND object. The big advantage here is you can trap for errors through the ADO Errors collection and handle them gracefully if the insert fails for some reason.

    -- bcp. Pretty similar to BULK INSERT. This is a command line utility that you can call manually or in an automated way in Access. You'll need a format file describing the various fields in the source/destination. The down side to doing it this way is you cannot trap for errors/exceptions if the insert fails.

    -- DTS. If you are not tied into a fully automated process in Access to push the data to SQL Server, try creating a DTS package directly on SQL Server with a data pump between the Access and SQL Server tables. This should run faster than the way you are doing things now b/c you can choose an OLEDB Provider for Jet which is a lot faster than the generic ODBC driver for Jet / System DSN you have set up in Access for the linked SQL table.

    There are many other ways to accomplish the same thing, such as maybe creating a linked server to the Access database within SQL Server. Also, you may want to temporarily remove indexes in the SQL table, run the import, and then recreate the indexes. Another thing would be to use a table locking hint to exclusively acquire a table lock on the destination table.

    Anyway, without knowing much about your architecture/environment, and assuming you want to do this in an automated way, I would recommend the BULK INSERT in a stored proc first. I think you will get the best performance overall. If you have no problem manually doing this in a separate process, then just do a DTS in SQL Server.

    If you have any other thoughts, feel free to ask!

    HTH.

    Kael MCSD, A+
    Whew. Thanks for the detail. For security reasons I have no access, other than via ODBC, to the SQL Server database so I cant really create any DTS, bcp or bulk insert stuff unfortunately.

    Thanks for the ideas though. A few options I hadn't thought of (even i i cant use em

    I might have a look at the drop/recreate index option.

    Thanks
    Matt

  5. #5
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Yeah, no prob.

    It sucks when you have to deal with the inability to do things the way you'd like to do it in a best case scenario...

    You mentioned that for security reasons, you are limited in what you can do directly on SQL Server. If that's the case, are you able to drop/recreate indexes through SQL DDL statements for the destination table on SQL Server?

    Anyway, in your case, b/c of your limitations, I would use a combination of the index thing along with the table hint.

    Ex:

    INSERT INTO SQL_SERVER_TABLE (WITH LockHintType)
    (SELECT * FROM ACCESS_TABLE)

    I would chose a pretty high level of locking hint such as TABLOCKX or XLOCK if other processes won't be competing with yours at the same time...

    HTH!

    Kael

  6. #6
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by kaeldowdy
    Yeah, no prob.

    It sucks when you have to deal with the inability to do things the way you'd like to do it in a best case scenario...

    You mentioned that for security reasons, you are limited in what you can do directly on SQL Server. If that's the case, are you able to drop/recreate indexes through SQL DDL statements for the destination table on SQL Server?

    Anyway, in your case, b/c of your limitations, I would use a combination of the index thing along with the table hint.

    Ex:

    INSERT INTO SQL_SERVER_TABLE (WITH LockHintType)
    (SELECT * FROM ACCESS_TABLE)

    I would chose a pretty high level of locking hint such as TABLOCKX or XLOCK if other processes won't be competing with yours at the same time...

    HTH!

    Kael
    Thanks for the tips.

    I think I will be allowed to do the following by the security guys

    1. run access append query to staging tables on sql server. It will still take forever but wont lock any live tables
    2. run a web page (password protecetd) that kicks off a stored proc or script or whatever that copies, in a single sql statement, staging data to the live table which should take seconds to run, rather than 30 mins!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •