Azure SQL Database – Executing Query Using Linked Server Takes Hours

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert    
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.

You can perhaps play with EXECUTE AT vs. OPENQUERY, but what I see as critical is:

If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned.

If this is both true now, and will be true after a few years of growth, and you've already restricted the amount of data returned as much as possible by doing as many joins and WHERE clause restrictions and even RTRIM if you have CHAR columns with trailing spaces in your OPENQUERY statement as you can, then at the beginning of your stored procedure/query batch, create a #temp table for the results you need and fill it with the OPENQUERY results. Then use it in your actual queries and joins!

Use Profiler (reads, writes, cpu, duration columns) to check on overall performance with SQL:BatchCompleted and RPC:Completed events; in particular, see if various ways of indexing the #temp table yield a net gain.

Alternately, consider that instead of an agent job refreshing a permanent table willy-nilly, is there a guaranteed way of telling if the remote server's table has been changed, perhaps a rowcount to find deletes combined with a MAX(updatedate) or a MAX(column that's INT IDENTITY and always autopopulates) that'll catch changes, inserts, and inserts equal in quantity to the deletes, or another way?

If so, then in your stored procedure/batch, instead of fetching all the data, just check to see if it's changed. If it's changed, update your permanent local table. If not, use your permanent local table as-is. WARNING: be very careful not to deadlock yourself with this code if it's run by several people at close to the same time (i.e. "run at the same time" in layman's terms).

Also alternatively, put a trigger on the remote table that simply sets a flag table with a single row and BIT column (call it YourDataIsOutOfDate) to 1 on every DML statement on that remote table. Then do the above "check for updates" technique, except that you need to lock the remote table, update your local table, update the YourDataIsOutOfDate remote table to be 0 again, and then unlock (finish your transaction) the remote table. Again, beware of deadlocking yourself, and this may run into remote transaction intricacies that could be a headache. I don't recommend this, but include it for completeness.