Bulk Inserts
By default, Business Central automatically buffers inserts in order to send them to Microsoft SQL Server at one time.
By using bulk inserts, the number of server calls is reduced, thereby improving performance.
Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.
Application developers who want to write high performance code that utilizes this feature should understand the following bulk insert constraints.
Bulk Insert Constraints
If you want to write code that uses the bulk insert functionality, you must be aware of the following constraints.
Records are sent to SQL Server when the following occurs:
You call
COMMIT
to commit the transaction.You call
MODIFY
orDELETE
on the table.You call any
FIND
orCALC
methods on the table.
Records are not buffered if any of the following conditions are met:
The application is using the return value from an
INSERT
call; for example, "IF (GLEntry.INSERT) THEN
".The table that you are going to insert the records into contains any of the following:
BLOB fields
Fields with the AutoIncrement property set to True
The following code example cannot use buffered inserts because it contains a FIND
call on the GL/Entry table within the loop.
IF (JnlLine.FIND('-')) THEN BEGIN
GLEntry.LOCKTABLE;
REPEAT
IF (GLEntry.FINDLAST) THEN
GLEntry.NEXT := GLEntry."Entry No." + 1
ELSE
GLEntry.NEXT := 1;
// The FIND call will flush the buffered records.
GLEntry."Entry No." := GLEntry.NEXT ;
GLEntry.INSERT;
UNTIL (JnlLine.FIND('>') = 0)
END;
COMMIT;
If you rewrite the code, as shown in the following example, you can use buffered inserts.
IF (JnlLine.FIND('-')) THEN BEGIN
GLEntry.LOCKTABLE;
IF (GLEntry.FINDLAST) THEN
GLEntry.Next := GLEntry."Entry No." + 1
ELSE
GLEntry.Next := 1;
REPEAT
GLEntry."Entry No.":= GLEntry.Next;
GLEntry.Next := GLEntry."Entry No." + 1;
GLEntry.INSERT;
UNTIL (JnlLine.FIND('>') = 0)
END;
COMMIT;
// The inserts are performed here.
Disabling Bulk Inserts
Disabling bulk inserts can be helpful when you are troubleshooting failures that occur when inserting records. To disable bulk inserts, you set the BufferedInsertEnabled parameter in the CustomSettings.config file of the Business Central Server to FALSE. For more information, see Configuring Business Central Server.
See Also
Data Access
Table Keys and Performance
AL Database Methods and Performance on SQL Server
Query Objects and Performance
Comentários
https://aka.ms/ContentUserFeedback.
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulteEnviar e exibir comentários de