firedac FireDAC array DML performance issue

[Soji 1]

Hi,

I am trying to rewrite a piece of legacy code which inserts a number of records in a bulk to SQL server tables(Total number of lines is around 200000 across 15 tables). Currently it uses ADO query to insert multiple records one by one. If the number of records are too high then this process takes a lot of time.

 

I was trying to make it faster and came across FireDAC array DML and according to the documentation, inserting 100000 records can be done less than a second!

 

So I implemented it and in my development machine  (where application and database resides) it improved a lot. From 8 minutes to less than a minute.

But when I tested it on a test environment where application and database resides on different machines connected by a network, performance is worse. (It was taking 10 minutes with ADO and with array DML it takes 15 minutes)
I can't figure it out why array DML insert is taking more time than normal record by record insert. Any ideas?

 

Delphi version:  Delphi 10.4 

SQL Server: 2017

SQL Driver: ODBC DRIVER 17 FOR SQL SERVER

 

Kind regards,

Soji.

[Brian Evans 105]

Would suggest using the Microsoft OLE DB Driver for SQL Server - OLE DB Driver for SQL Server | Microsoft Docs instead of ODBC.

 

Without seeing your code it is just guessing why it is slow. Certainly a large difference between local and over the network suggests your code isn't using ArrayDML with a large enough batch size or any batch size at all.  

 

 

 

[Soji 1]

Hi Brain,

 

Thanks for your suggestion. But those OLEDB drivers are either not maintained or deprecated.

 

Also FireDAC forces you to use ODBC driver. FireDAC, New OLE DB Driver, and SQL Server

 

I figured out that if I use begin transaction and commit transaction, then the performance increases dramatically. 15 minutes to 1 minute!!!

So I am satisfied with this solution and testing now.

 

Kind regards,

Soji.

[Anders Melander 1783]

You could probably have used the MSSQL Server Profiler to determine what the server is doing when you run the query.

 

With regard to transactions, I always use explicit transactions.

BeginTransaction;
try
  DoStuff;

  CommitTransaction;
except
  RollbackTransaction;
  raise;
end;

 

[Soji 1]

Thanks @Anders Melander

I did exactly same. I saw that even though it executes in the batch, server side it was inserting slowly. 

So I used transactions, it inserted super fast.