2014-02-21 08:40:50 8 Comments
I have a table on SQL Asure server with no indexes except clustered. And I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes. Although, when I do it with SSIS it takes just a second. This is the query:
INSERT INTO DatabaseName.dbo.tmpOurClients
(KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop)
SELECT KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop
FROM dbo.OurClients
That is how I created the Linked Server:
EXEC sp_addlinkedserver
@server='Azure',
@srvproduct='',
@provider='sqlncli',
@datasrc='ServerName.DATABASE.WINDOWS.NET',
@location='',
@provstr='',
@catalog='db_name'
EXEC sp_addlinkedsrvlogin
@rmtsrvname='Azure',
@useself='false',
@rmtuser='[email protected]_name.database.windows.net',
@rmtpassword='password'
EXEC sp_serveroption 'Azure', 'Collation Compatible', true;
I've already checked this topic Linked server to SQL Azure very slow but none of suggestions helped me.
Version of local SQL-Server: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
So, what can be the problem?
Related Questions
Sponsored Content
1 Answered Questions
adding 2nd catalog for linked server to DB2
- 2019-08-15 12:42:47
- Jett_DBA
- 36 View
- 0 Score
- 1 Answer
- Tags: sql-server sql-server-2014 db2 linked-server db2-luw
5 Answered Questions
[SOLVED] Why is this explicit cast causing problems only with a Linked Server?
- 2016-08-17 07:42:58
- krystah
- 4161 View
- 21 Score
- 5 Answer
- Tags: sql-server sql-server-2008-r2 sql-server-2012 linked-server
1 Answered Questions
[SOLVED] Why is SQL Profiler showing empty for database name and database id?
- 2016-03-16 10:09:18
- jerry xu
- 501 View
- 3 Score
- 1 Answer
- Tags: sql-server sql-server-2008-r2 profiler
1 Answered Questions
[SOLVED] Database Backup compression
- 2017-01-05 15:18:32
- shamim
- 92 View
- 1 Score
- 1 Answer
- Tags: sql-server sql-server-2008 backup compression
2 Answered Questions
[SOLVED] Why can't I read my transaction log backup file using fn_dump_dblog?
- 2013-01-23 23:28:20
- Alan Whited
- 2462 View
- 6 Score
- 2 Answer
- Tags: sql-server sql-server-2008 sql-server-2008-r2 transaction-log restore
1 Answered Questions
[SOLVED] How to install Microsoft SQL Server 2014 Standard Edition on trial?
- 2016-01-18 16:32:43
- gotqn
- 7602 View
- 4 Score
- 1 Answer
- Tags: sql-server sql-server-2014
1 Answered Questions
[SOLVED] Differentiate SQL Server Express from SQL Server installed in a server
- 2015-03-25 07:44:58
- Venkat
- 1738 View
- 2 Score
- 1 Answer
- Tags: sql-server sql-server-2008-r2
1 Answered Questions
[SOLVED] What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2
- 2014-12-16 17:09:00
- Joe Hayes
- 365 View
- 4 Score
- 1 Answer
- Tags: sql-server sql-server-2008-r2 wait-types
1 comments
@Paul White says GoFundMonica 2014-02-21 12:21:08
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 asbcp
orSSIS
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 theVALUES
clause. The statement is then used withEXECUTE AT
, for example: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
requiresRPC
andRPC OUT
enabled in the linked server options.The
VALUES
clause for a planINSERT
statement is limited to 1000 elements. There are ways around that (aVALUES
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.