Export from excel to SQL Server goes slow over network while fast locally

Question

I have an Excel which exports a table to SQL server database. If i run the excel locally and put on the profiler tool it goes very rapidly once i insert it to a SQL Server database on my local laptop (beneath 10 seconds).

Once i insert it to my network database (the network server) it takes minutes (around 5).
Very typical when i look inside the trace is that the duration increases of each set of insert statements once being fired on the server.

I am not a data-entry expert, nor an excel VBA expert as this excel has been given to me, but i would like to know when you do a recordset insert whether it is explainable by nature it takes longer on a remote databaseserver. (PS out network is fast and the data sent is really low so it is for sure not an infrastructure thing)

I suspect it is a sort of checking mechanism between excel and the database (a sort of confirmation per row) which of course takes longer over a network

Answer 1

Excel VBA calls are COM calls and COM calls are slow by nature. 10 seconds locally doesn't sound fast or normal unless you are inserting many rows. How many rows of data that is? Over 500,000? If not, you are already slow locally. I wouldn't chase why it is slower on the network.
IMHO it would be wise to use some external code written in say C# to accomplish this using SqlBulkCopy class.

Answer 2

Hi @johan koopmans
This may be related to network problems–relating to the speed and capacity of the “pipe” connecting your SQL application client to the database.
I find an article "Why is My Database Application so Slow?", hope it helps.
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I have the same problem two weeks ago and found a solution and you may want to try.
I assume you are using VBA code to achieve your task, and I assume you are using the "insert into" query in a loop.
The problem is on the insert into query, especially in a loop, it has to open and close the connection with the network database in every loop.
My solution is instead of using "insert into", I use "Select" and "Add new" to a recordset and perform the loop to update the recordset, and close the recordset at the end. the advantage of this method is only open and close the connection once.
This method takes me 5 seconds to update 3000 rows of data and the insert into method took about 4 minutes.

Answer 4

Hi Cetin thanks a lot an interesting answer. Well locally it are 8000 rows and the whole thing takes 5 to 10 seconds.
Still i am curious why it takes so much longer.