SQL-запросы занимают слишком много времени

Некоторые люди написали, что @@IDENTITY будет быстрым, поэтому вот доказательство (с использованием VBA) того, как мои INSERT INTO две таблицы одновременно с помощью трюка VIEW примерно в три раза быстрее, чем выполнение двух INSERTS и получение значений @@IDENTITY каждый раз... что неудивительно, потому что последний включает три оператора Execute, а первый - только один :)

На моей машине для 4200 итераций трюк VIEW занял 45 секунд, а подход @@IDENTITY — 127 секунд:

Sub InitInerts()
  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0
  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE TableA" & vbCr & "(" & vbCr & "   ID IDENTITY NOT" & _
      " NULL UNIQUE, " & vbCr & "   a_col INTEGER NOT NULL" & vbCr & ")"
      .Execute Sql

      Sql = _
      "CREATE TABLE TableB" & vbCr & "(" & vbCr & "   ID INTEGER NOT" & _
      " NULL UNIQUE" & vbCr & "      REFERENCES TableA (ID)," & _
      "  " & vbCr & "   b_col INTEGER NOT NULL" & vbCr & ")"
      .Execute Sql

      Sql = _
      "CREATE VIEW TestAB" & vbCr & "(" & vbCr & "   a_ID, a_col, " & vbCr & " " & _
      "  b_ID, b_col" & vbCr & ")" & vbCr & "AS " & vbCr & "SELECT A1.ID, A1.a_col," & _
      " " & vbCr & "       B1.ID, B1.b_col" & vbCr & "  FROM TableA AS" & _
      " A1" & vbCr & "       INNER JOIN TableB AS B1" & vbCr & "    " & _
      "      ON A1.ID = B1.ID"
      .Execute Sql

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

Sub TestInerts_VIEW()

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim timer As CPerformanceTimer
    Set timer = New CPerformanceTimer
    timer.StartTimer

    Dim counter As Long
    For counter = 1 To 4200
      .Execute "INSERT INTO TestAB (a_col, b_col) VALUES (" & _
                   CStr(counter) & ", " & _
                   CStr(counter) & ");"
    Next

    Debug.Print "VIEW = " & timer.GetTimeSeconds

  End With

End Sub

Sub TestInerts_IDENTITY()

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim timer As CPerformanceTimer
    Set timer = New CPerformanceTimer
    timer.StartTimer

    Dim counter As Long
    For counter = 1 To 4200
      .Execute "INSERT INTO TableA (a_col) VALUES (" & _
          CStr(counter) & ");"

      Dim identity As Long
      identity = .Execute("SELECT @@IDENTITY;")(0)

      .Execute "INSERT INTO TableB (ID, b_col) VALUES (" & _
                   CStr(identity) & ", " & _
                   CStr(counter) & ");"

    Next

    Debug.Print "@@IDENTITY = " & timer.GetTimeSeconds

  End With

End Sub

Это показывает, что теперь узким местом являются накладные расходы, связанные с выполнением нескольких операторов. Что, если бы мы могли сделать это всего одним оператором? Что ж, угадайте, что, используя мой надуманный пример, мы можем. Во-первых, создайте таблицу Sequence с уникальными целыми числами, что является стандартным трюком SQL (каждая база данных должна иметь ее, IMO):

Sub InitSequence()

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim sql As String

    sql = _
        "CREATE TABLE [Sequence]" & vbCr & "(" & vbCr & "   seq INTEGER NOT NULL" & _
        " UNIQUE" & vbCr & ");"
    .Execute sql

    sql = _
        "INSERT INTO [Sequence] (seq) VALUES (-1);"
    .Execute sql

    sql = _
        "INSERT INTO [Sequence] (seq) SELECT Units.nbr + Tens.nbr" & _
        " + Hundreds.nbr + Thousands.nbr AS seq FROM ( SELECT" & _
        " nbr FROM ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
        " ALL SELECT 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
        " [Sequence] UNION ALL SELECT 3 FROM [Sequence] UNION" & _
        " ALL SELECT 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
        " [Sequence] UNION ALL SELECT 6 FROM [Sequence] UNION" & _
        " ALL SELECT 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
        " [Sequence] UNION ALL SELECT 9 FROM [Sequence] ) AS" & _
        " Digits ) AS Units, ( SELECT nbr * 10 AS nbr FROM" & _
        " ( SELECT 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
        " 1 FROM [Sequence] UNION ALL SELECT 2 FROM [Sequence]" & _
        " UNION ALL SELECT 3 FROM [Sequence] UNION ALL SELECT" & _
        " 4 FROM [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
        " UNION ALL SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
        " 7 FROM [Sequence] UNION ALL SELECT 8 FROM [Sequence]" & _
        " UNION ALL SELECT 9 FROM [Sequence] ) AS Digits )" & _
        " AS Tens, ( SELECT nbr * 100 AS nbr FROM ( SELECT" & _
        " 0 AS nbr FROM [Sequence] UNION ALL SELECT 1 FROM" & _
        " [Sequence] UNION ALL SELECT 2 FROM [Sequence] UNION"
    sql = sql & _
        " ALL SELECT 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
        " [Sequence] UNION ALL SELECT 5 FROM [Sequence] UNION" & _
        " ALL SELECT 6 FROM [Sequence] UNION ALL SELECT 7 FROM" & _
        " [Sequence] UNION ALL SELECT 8 FROM [Sequence] UNION" & _
        " ALL SELECT 9 FROM [Sequence] ) AS Digits ) AS Hundreds," & _
        " ( SELECT nbr * 1000 AS nbr FROM ( SELECT 0 AS nbr" & _
        " FROM [Sequence] UNION ALL SELECT 1 FROM [Sequence]" & _
        " UNION ALL SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
        " 3 FROM [Sequence] UNION ALL SELECT 4 FROM [Sequence]" & _
        " UNION ALL SELECT 5 FROM [Sequence] UNION ALL SELECT" & _
        " 6 FROM [Sequence] UNION ALL SELECT 7 FROM [Sequence]" & _
        " UNION ALL SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
        " 9 FROM [Sequence] ) AS Digits ) AS Thousands;"
    .Execute sql

  End With

End Sub

Затем используйте таблицу Sequence для перечисления значений от 1 до 42000 и создания строк в одном операторе INSERT INTO..SELECT:

Sub TestInerts_Sequence()

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim timer As CPerformanceTimer
    Set timer = New CPerformanceTimer
    timer.StartTimer

    .Execute "INSERT INTO TestAB (a_col, b_col) " & _
             "SELECT seq, seq " & _
             "FROM Sequence " & _
             "WHERE seq BETWEEN 1 AND 4200;"

    Debug.Print "Sequence = " & timer.GetTimeSeconds



  End With

End Sub

Это выполняется на моей машине за 0,2 секунды!

Более поздние версии Access поддерживают переменную @@IDENTITY. Вы можете использовать это для получения столбца идентификаторов после вставки без выполнения запроса.

INSERT INTO mytable (field1,field2) VALUES (val1,val2);
SELECT @@IDENTITY;

См. эту статью базы знаний.

мы вставляем строку, а затем делаем еще один запрос, чтобы получить ее автоматически увеличивающийся идентификатор; затем используйте этот идентификатор, чтобы вставить еще несколько строк, связав их с первым

Это одна таблица, две таблицы или более двух таблиц?

Если одна таблица, вы можете рассмотреть другой дизайн, например. вы можете генерировать свои собственные случайные идентификаторы, использовать модель вложенных наборов, а не модель списка смежности и т. д. Трудно понять, не поделитесь ли вы своим дизайном ;-)

Если две таблицы, предполагая, что между таблицами в столбце AUTOINCREMENT/IDENTITY есть FOREIGN KEY, вы можете создать VIEW, который INNER JOINs две таблицы и INSERT INTO значения VIEW и AUTOINCREMENT/IDENTITY будут «скопированы» в ссылочную таблицу. Более подробная информация и рабочий пример в этом ответе на переполнение стека (ссылка ниже).

Если более одной таблицы, трюк VIEW не выходит за пределы двух таблиц, насколько мне известно, поэтому вам, возможно, просто придется жить с низкой производительностью или изменить технологию, например. DAO сообщается быстрее, чем ADO, SQL Server может быть быстрее, чем ACE/Jet и т. д. Опять же, не стесняйтесь делиться своим дизайном, может быть решение «нестандартного мышления».

Как вставить данные?

В вашей ситуации может быть лучше использовать ADO для вставки вместо запуска команды SQL.

for i = 1 to 100
   rs.AddNew
   rs("fieldOne") = "value1"
   rs("fieldOne") = "value2"
   rs.Update
   id = rs("uniqueIdColumn")
   'do stuff with id...
next

Я знаю, что использование ADO медленное, но это во много раз быстрее, чем открытие 4200 соединений...

Некоторое предложение (которое можно даже комбинировать):

• Почему бы вам не получить автоматически увеличивающийся идентификатор перед вставкой строки, чтобы у вас уже было значение, доступное для вашего следующего запроса? Это должно сэкономить вам некоторое время.
• Также необходимо проверить закрытие/размыкание соединения.
• Думали ли вы об управлении наборами записей (с помощью кода Visual Basic для обновления ваших данных) вместо таблиц (с инструкциями SQL, отправляемыми в базу данных)?
• Другое решение (если вы уверены, что соединение является узким местом) состоит в том, чтобы создать таблицу локально, а затем экспортировать ее в файл доступа после выполнения задания.
• Поскольку вы используете ADO, вы даже можете сохранять свои данные в виде XML-файла, загружать набор записей из этого XML-файла, манипулировать им с помощью VBA и экспортировать его в базу данных Access.

Не в смысле умничать... Но есть ли резон продолжать пользоваться Access? SQL Server Express бесплатен, быстрее и мощнее...

Похоже, вы импортируете данные, а в Access есть гораздо лучшие возможности для импорта данных. Да, он вставляет много записей за раз, и это было бы быстрее.

Можете ли вы описать приложение немного больше?

Самый дешевый и (в основном каждый раз) лучший прирост скорости, который вы можете дать базе данных, - это хранить неизменяемые данные в кеше.

Не знаю, применимо ли это к вашему случаю, но это очень просто. Вы можете просто подключить (бесплатную) библиотеку, которая делает это, или сохранить локальную коллекцию прочитанных элементов. Пример :

• Один хочет прочитать пункт X.
• Есть ли элемент X в местной коллекции?
• Нет: прочитать элемент X из базы данных и поместить его в локальную коллекцию.
• Да: просто верните локальную копию элемента X.

Конечно, это может быть немного сложнее, когда вы используете веб-сайт с большим количеством серверов. В этом случае просто используйте блоки приложений или кэширование.

Позвольте мне опровергнуть следующие утверждения:

предложение SELECT @@IDENTITY - определенно гораздо более быстрый способ вставки данных и получения значения Autonumber, чем открытие набора записей AddOnly, обновление полей, сохранение записи и сохранение значения Autonumber. SQL INSERT всегда будет быстрее, чем использование построчного набора записей.

Я думал, что подход с двумя наборами записей может быть немного быстрее, чем подход @@IDENTITY, потому что я подозревал, что он требует меньшего количества обращений к базе данных. В моем тестировании это было намного быстрее — 1,2 секунды по сравнению со 127 секундами для подхода @@IDENTITY. Вот мой код (код для создания .mdb опубликован в другом ответе):

Sub TestInerts_rs()

  Dim con
  Set con = CreateObject("ADODB.Connection")
  con.Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

  Dim timer As CPerformanceTimer
  Set timer = New CPerformanceTimer
  timer.StartTimer

  Dim rs1
  Set rs1 = CreateObject("ADODB.Recordset")
  With rs1
    .ActiveConnection = con
    .CursorType = 1  ' keyset
    .LockType = 3  ' optimistic
    .Source = "SELECT a_col, ID FROM TableA;"
    .Open
  End With

  Dim rs2
  Set rs2 = CreateObject("ADODB.Recordset")
  With rs2
    .ActiveConnection = con
    .CursorType = 1  ' keyset
    .LockType = 3  ' optimistic
    .Source = "SELECT b_col, ID FROM TableB;"
    .Open
  End With

  Dim counter As Long
  For counter = 1 To 4200
    rs1.AddNew "a_col", counter

    Dim identity As Long
    identity = rs1.Fields("ID").value

    rs2.AddNew Array(0, 1), Array(counter, identity)

  Next

  Debug.Print "rs = " & timer.GetTimeSeconds

End Sub

Некоторые мысли, которые могут быть или не быть полезными:

1. Позвольте мне поддержать предложение SELECT @@IDENTITY - определенно гораздо более быстрый способ вставки данных и получения значения Autonumber, чем открытие набора записей AddOnly, обновление полей, сохранение записи и сохранение значения Autonumber. SQL INSERT всегда будет быстрее, чем использование построчного набора записей.

2. используя транзакции DAO, вы можете преобразовать несколько таких вставок в пакет, который выполняется одновременно. Я не уверен в этом, так как точно не знаю, что вы делаете и сколько таблиц используете. Дело в том, что вы запускаете серию SQL INSERT в транзакции, а затем выполняете .Commit в конце, так что фактическая запись в реальный файл базы данных будет происходить как одна операция, а не как 4200 (или сколько угодно) отдельных операций.