При вставке данных из запроса в таблицу выполняется ли запрос для каждой записи?

Я вставляю данные проблематично в таблицы. Когда я делаю это из другой таблицы, это быстро, только немного замедлилось, если есть много записей. Даже тогда, это вопрос секунд.

Когда я вставляю из запроса в таблицу, он переходит в минуты – примерно одна минута для каждой введенной 1000 записей.

Сам исходный запрос, когда он запускается только как выделенный запрос, занимает от 1 до 2 секунд. Выполняется ли запрос для каждой вставленной записи? Я надеялся, что он будет работать один раз для всего набора данных. Или есть что-то еще, что заставляет функцию работать так медленно по сравнению с вставкой «плоских» данных из другой таблицы.

VBA, который я использую, довольно безобидна:

CurrentDb.Execute "SELECT [Extra Value Concatenation].* _ INTO [" & strTableName & "] FROM [Extra Value Concatenation];" 

и исходный запрос ниже – он использует функцию Concatenate Allen Browne .

 SELECT [Extra Fields - Fee Protection Insurance Concatenate].ContactID, ConcatRelated('[Fee Protection Insurance]', '[Extra Fields - Fee Protection Insurance Concatenate]', 'ContactID = ' & [ContactID]) AS [Fee Protection Insurance] FROM [Extra Fields - Fee Protection Insurance Concatenate]; 

EDIT: В ответ на комментарий Fionnuala, но я не мог правильно отформатировать его в комментариях.

Используя вымышленные данные, вот примерно то, что я хочу.

T1 содержит записи клиентов.

 ContactID Name 1 Example Limited 2 Another Company Limited 

T2 содержит дополнительные поля. ContactID существует как внешний ключ и может дублироваться, если несколько записей хранятся.

 ContactID FieldValue 1 Value 1 1 Value 2 2 Value 3 2 Value 4 2 Value 5 

Когда я оставил присоединиться к таблицам, дубликаты из T2 появляются, поэтому я получаю

 ContactID Name FieldValue 1 Example Limited Value 1 1 Example Limited Value 2 2 Another Company Limited Value 3 2 Another Company Limited Value 4 2 Another Company Limited Value 5 

когда я хочу

 ContactID Name FieldValue 1 Example Limited Value 1; Value 2 2 Another Company Limited Value 3; Value 4; Value 5 

Следовательно, объединение данных во временной таблице казалось хорошей идеей, но все замедляло. Есть ли другой способ взглянуть на мой запрос?

Вы используете определенную пользователем функцию (UDF) ConcatRelated, поэтому UDF запускается для каждой записи, в противном случае обычно Access SQL работает обычным способом.

Я написал довольно простой модуль, который должен выполнить это для вас очень быстро по сравнению с вашим текущим процессом. Обратите внимание, что вам нужно будет переименовать свой проект в нечто иное, чем «База данных» в панели навигации проекта, чтобы это работало

Я предположил, что table1 и table2 такие же, как у вас выше таблицы3, это просто список всех записей в таблице 1 с пустым полем FieldValues, чтобы добавить требуемое значение «value1, value2» и т. Д. Это должно привести к тому, что таблица 3 будет заполнена с желаемым результатом

ИМПОРТАН: Для тех, кто использует функции recordset .edit и .update, убедитесь, что вы удалили блокировку уровня записи в меню параметров доступа, его можно найти в разделе «Настройки клиента» в разделе «Параметры доступа», так как это не приведет к чрезмерному вздутию вашего файл, так как доступ не будет отменять блокировки записи, пока вы не упадете и не восстановите базу данных. Это может привести к тому, что ваша база данных станет невосстановимой, если она достигнет предела 2gb для окон.

 Function addValueField() 'Declarations Dim db As Database Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset Dim qry As String Dim value As String Dim recordcount as Long Set db = CurrentDb() 'Open a select query that is a join of table 1 and table 2 'I have made Contact ID a foreign key in the second table qry = "SELECT Table1.[Contact ID], Table1.Name, Table2.FieldValue FROM Table1 INNER JOIN Table2 ON Table1.[Contact ID] = Table2.[Contact ID(FK)] ORDER BY [Contact ID];" Set rs1 = db.OpenRecordset(qry, dbOpenDynaset) 'Table 3 was filled with each record from table1, with a 3rd "Field Value" field to 'be filled with your Value 1, Value 2 etc. qry = "SELECT * FROM Table3 ORDER BY [Contact ID]" Set rs2 = db.OpenRecordset(qry, dbOpenDynaset) 'Ensure you have enough file locks to process records recordcount = rs1.recordcount DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, recordcount + 1000 rs1.MoveFirst rs2.MoveFirst 'Here we test to see if "Name" is the same in both recordsets, if it is, add the FieldValue 'to the FieldValue in Table3, otherwise move to the next record in table 3 and compare again Do While Not rs1.EOF If IsNull(rs2![FieldValue]) = True Then If rs2![FieldValue] = "" Then rs2.Edit rs2![FieldValue] = rs1![FieldValue] rs2.Update rs1.MoveNext Else rs2.Edit rs2![FieldValue] = rs2![FieldValue] & "; " & rs1![FieldValue] rs2.Update rs1.MoveNext End If Else rs2.MoveNext End If Loop rs1.close rs2.close db.close set db = nothing set rs1 = nothing set rs2 = nothing End Function 

Основываясь на ответе pegicity, мой возможный код был:

 Option Compare Database Sub Concatenate(strTableToConcatenate As String, strFieldToConcatenate As String, strIDField As String) Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Dim qry As String Dim strSourceTable As String Dim i As Integer Dim strFieldName As String Dim strValue As String Dim intConcatenateID As Integer Dim intSortID As Integer strSourceTable = strTableToConcatenate & " (Concatenate)" 'Creates a duplicate copy of the table to be concatenated and empties the original table' DeleteTable (strSourceTable) DoCmd.CopyObject , strSourceTable, acTable, strTableToConcatenate qry = "DELETE FROM [" & strTableToConcatenate & "]" CurrentDb.Execute (qry) qry = "ALTER TABLE [" & strTableToConcatenate & "] ALTER COLUMN [" & strFieldToConcatenate & "] memo" 'Changes the DataType of the field to be concatenated to Memo, as the result may be considerably longer than the original data' CurrentDb.Execute (qry) i = 0 intCurrentID = 0 qry = "SELECT * FROM [" & strSourceTable & "] ORDER BY [" & strIDField & "], [" & strFieldToConcatenate & "]" Set rsSource = CurrentDb.OpenRecordset(qry, dbOpenDynaset) qry = "SELECT * FROM [" & strTableToConcatenate & "]" Set rsDestination = CurrentDb.OpenRecordset(qry, dbOpenDynaset) For Each fld In rsSource.Fields 'Finds the column number of the fields you are sorting by and concatenating from your source table.' strFieldName = rsSource.Fields(i).Name If strFieldName = strFieldToConcatenate Then intConcatenateID = i ElseIf strFieldName = strIDField Then intSortID = i End If i = i + 1 Next If rsSource.recordcount <> 0 Then rsSource.MoveFirst intCurrentID = rsSource.Fields(intSortID).Value strConcatenateValue = "" While Not rsSource.EOF 'The source recordset is sorted by your designated sort field, so any duplicates of that field will be next to each other. If the row below has the same id as the row above, the sub continues to build the concatenated value. If the row changes, it adds the concatenated value to the destination record set.' If intCurrentID = rsSource.Fields(intSortID).Value Then strConcatenateValue = strConcatenateValue & "," & rsSource.Fields(intConcatenateID).Value rsSource.MoveNext Else rsDestination.AddNew i = 0 If Len(strConcatenateValue) > 0 Then strConcatenateValue = Right(strConcatenateValue, Len(strConcatenateValue) - 1) End If For Each fld In rsSource.Fields strFieldName = rsSource.Fields(i).Name If strFieldName = strFieldToConcatenate Then strValue = strConcatenateValue ElseIf strFieldName = strIDField Then strValue = intCurrentID Else strValue = rsSource.Fields(i).Value End If rsDestination.Fields(strFieldName) = "" & strValue & "" i = i + 1 Next rsDestination.Update intCurrentID = rsSource.Fields(intSortID).Value strConcatenateValue = "" End If Wend End If rsSource.Close rsDestination.Close Set rsSource = Nothing Set rsDestination = Nothing End Sub