We're currently testing whether a linked server is the right way to go for accessing .dbf files from within a SQL environment... It all works well however the performance seems poor. When performing a basic select * from <table> we are looking at 7 seconds for a 250 record table and 10 seconds for a 12000 record table. It appears as though it is taking about 5-6 seconds to establish the connection.
The files that the linked server is accessing are on the local machine (ie the SQL Server). THe server is also idle (its a new Compaq 370 with 1GB ram).
Can anybody shed some light on ways to improve performance or alternatives as this will not be acceptable?
We will need to run a number of batches accessing about 20-30 dbfs. One thought that came to mind was to run 4-5 tables in parallel thereby decreasing the overall job time. However when using a linked server we have found the following:
Both of these were run separately:
'select * from table a' - 15 seconds
'select * from table b' - 15 seconds
when running these two in parallel (ie with 2 separate connections) I would expect each statement to take slightly longer than when they ran independantly. However this is not the case - when running these 2 concurrently they take a combined time of 30 seconds to complete. Any ideas why? If anybody has any links to some real documentation about how linked servers work please would you enlighten me.