>Thanks for the suggestions, Tim, Hugo and Kevin
>I did some tests and the Linked Server takes at least 10 times as long to execute a query as it takes fill the datatable using execute reader when I query one record from the table using an indexed field.
>The Linked Server couldn't be easier to use, but that performance hit would blow the project out of the water.
>It seems that the execute reader lets the OLEDB provider use the index - it's almost as fast as VFP with Rushmore, while the Linked Server query seems to be scanning the whole table.
>I haven't tried SSIS yet, but I will
>Thanks again for the tips
See this thread
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2819229c-5c32-4c96-a85f-ea3db0dcb79e which is relevant to your problem.
If it's not broken, fix it until it is.
My Blog