title: Slow performance in linked server query
body: <p>I'm using a Linked Server Query
  from <code>MSSQL</code> to <code>mySql</code>.
  <code>MySql</code> table to be queried has 800K + records</p>
  
  <p>I'm using a temp table to pull the results from linked server and do a join on that temp table for SQL query</p>
  
  <p>Is there a performance difference between:</p>
  
  <pre><code>Declare
  @MyString varchar(max),
  @Address varchar(20),
  @tempTable (Address, ColumnB, ColumnC)
  
  
  set @MyString  = 'Select Address, ColumnB, ColumnC from schemaname.tablename where ''' + convert(varchar(30),@Address) + ''') order by ColumnB desc limit 10'
  
  set @MyString  = 'Select * from Openquery([My_Linked_Server], ''' + REPLACE(@MyString, '''', '''''') + ''')'
  
  insert into @tempTable
  exec (@MyString) 
  </code></pre>
  
  <p>and </p>
  
  <pre><code>Set @MyString = 'Select Address, ColumnB, ColumnC from schemaname.tablename where ''' + convert(varchar(30),@Address) + ''') order by ColumnB desc limit 10'
  
  exec (@MyString) at My_Linked_Server
  </code></pre>
  
  <p>(all this is done in <code>SQL</code>)
  --second approach is currently giving me an error:</p>
  
  <blockquote>
    <p>OLE DB provider "MSDASQL" for linked server "My_Linked_Server" returned
    message "[MySQL][ODBC 5.2(w) Driver]Optional feature not supported".
    Msg 7391, Level 16, State 2, Line 14 The operation could not be
    performed because OLE DB provider "MSDASQL" for linked server
    "My_Linked_Server" was unable to begin a distributed transaction.</p>
  </blockquote>
  
  <p>DTC is started</p>
  
  <p>Another problem with using this is that I'm getting random timing on the results 1 second to 1 minute </p>
  
  <p>Thanks in advance!</p>
  
  <p>Update:</p>
  
  <p>First approach gives quite good results. The only issue remaining is the random timing of the results. More than 80% of the times the result is instant. This could be due to <code>MySql</code> table locks (tables of type <code>MyISAM</code>) since there's constant writing to this table and additional job running in <code>SQL Server</code> Querying <code>MySQL</code> for usage information.</p>
  
  <p>Is this a reasonable explanation for the delay?</p>