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