I have a database which links to an Excel workbook.
The workbook is denormalised and I use VBA code, the concept of which was provided on this site,
to traverse through the Excel columns and insert these into a normalised table.
With the db and spreadsheet on my local Pc or with
the db on my local Pc with the linked spreadsheet on the file server
it takes less than 10 seconds to create ~22k lines from a 600 row by 50 column spreadsheet (not all cells are filled)
With both the db and spreadsheet sitting on the network file server the whole process takes ~20 minutes.
This app needs to be accessed by other users so it needs to be available from the file server.
What can I do to increase the performance when both sit on the file server?