SQL Server Performance

Insert is slow when using MS access to insert records into a table that has two fields with encryption

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Feb 27, 2009.

  1. WingSzeto Member

    We use MS Access to import data into our SQL tables and this is done by a remote user using his home computer via VPN. He is using MS Access 2003. During the import which he did one table at a time, all except one table we encounter slow insert problem. One noticable difference about this table is that we have two fields that we use the build-in SQL encryption function to encrypt the ssn and password, and this table has 100 columns which is the most compared to others. If we insert 5 to this table, it will finish in 4~8 seconds which is slow already but if we insert 10, it would take up to 4 minutes. But for other tables, even with thousand of records, it took less than 30 sec to import. We recently upgrade from SQL 2005 std 32 bit to SQL 2008 std 64 bit version. Is there something that I need to watch out for?
    Any help on this matter is very much appreciated.
    Wingman
  2. melvinlusk Member

    Let me make sure I understand the situation here....
    When inserting records, only one of the tables encounters slow inserts? Is this the table with 100+ columns and the two encrypted fields?
    When you insert 5 records it could take up to 8 seconds, but could take up to 4 minutes if you insert 10 records?
    So when this user impots data, is he taking this data from an Access database and pasting it into the SQL table? Are the two encrypted fields plaintext upon insertion into SQL?

Share This Page