To flesh out what Aaron states a little more, linked server performance, particularly for large result sets, for cross-server joins and cross-server subqueries, is often disappointing. If you watch the remote server with Profiler, you may find that the local server bombards the remote server with requests for the fetch of a single row to match join columns. When that happens, the network latency and calling overhead conspire to kill performance.
If you can query local data without too much trouble, that would be best. You might be able to restore a production backup or use SSIS (or even bcp, it still works) to copy data from the production server to some working tables on the local server. Generally SSIS, bcp and similar tactics are faster than linked servers and may help to avoid issues with log file growth.
If you must query the data from a remote server, you may find that rewriting the query so that it uses OPENQUERY() (rather than four-part names) and 'sends' all of the 'remote parts' of the query over to the remote server and then joins the results of that to the local data will be more effective. SQL is supposed to be smart enough to move all of the joins to the remote server, but sometimes it doesn't and OPENQUERY() gives you a method to force SQL to do want you want.
Another, similar, tactic would be to run the 'remote part' of the query first and put the results into a temporary table, (optionally) index the temporary table and then join the 'local part' of the query to the temporary table. Again, this helps you to force SQL to do what it ought to.
It sounds like more work, but SQL may be able to behave more efficiently. As always, watch your data types on the joins and your SARGs or your indexes will be ignored.
There is a hack you can try: where you create a dummy DB with the same name and size as your old data and log files. Then you have to stop SQL server, replace the new empty files with the old files that have data in them.
Check out this blog link by Paul Randal where the hack is explained in detail. It looks like you have tried the first method mentioned, but read along and try the other methods. Please let us know how it goes. I do not know how well it will work without log files, but it is worth a try.
Best Answer
I see you have asked the same question at: Performance improvement in linked server-Disable data type check
Highly recommend to use SSIS. Linked servers are not meant to push data. Also, depending on the amount of data you are pushing or pulling (direction matters!!), the performance of linked server will always be slow + the n/w bandwidth plays an important role as well.
Refer to my answer for : Which one is more efficient: select from linked server or insert into linked server?
AFAIK, there is no such command in SQL Server to disable data type check which improves performance !!