question

johankoopmans-9496 avatar image
0 Votes"
johankoopmans-9496 asked johankoopmans-9496 commented

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

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


sql-server-generaloffice-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CetinBasoz-9541 avatar image
1 Vote"
CetinBasoz-9541 answered CetinBasoz-9541 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Cetin thanks a lot an interesting answer. Well locally it are 8000 rows and the whole thing takes 5 to 10 seconds locally.
Still i am curious why it takes so much longer over the network while with other technologies we don't face these problems.

0 Votes 0 ·

Well, on network there are extra factors. It might be the network latency, or might be security checks etc. Regardless of excel, can you compare your local and remote servers' speed? Maybe check what is going on using Sql server profiler, and performance counters.
In either case, you might try chasing the case, or do it fast anyways using SqlBulkCopyClass as I suggested, vs individual inserts (which needs to update indexes at least per insertion). 8000 rows should be inserted in milliseconds with SqlBulkCopy.

1 Vote 1 ·
johankoopmans-9496 avatar image
0 Votes"
johankoopmans-9496 answered erinding-msft converted comment to answer

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erinding-msft avatar image
1 Vote"
erinding-msft answered KoopmansJohan-6087 commented

Hi @johankoopmans-9496
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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you i will take a look!

0 Votes 0 ·
JeffSW-6075 avatar image
1 Vote"
JeffSW-6075 answered johankoopmans-9496 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Interesting Jeff thank you very much for sharing!
What we are trying currently is to make a CSV file and read it into the database using SQL Server integration services.
The end user will be noticed in their e-mailbox once the export/import is finished.

0 Votes 0 ·