January 29, 2014 at 7:20 am
We run a delete query which deletes 500-1000 records. But this takes more than 2hours to run.
We use Linked server between 2 SQL 2008 servers.
We delete rows from a local table based on running couple of joins on remote tables. How can we reduce the duration.
January 29, 2014 at 7:26 am
balasach82 (1/29/2014)
We run a delete query which deletes 500-1000 records. But this takes more than 2hours to run.We use Linked server between 2 SQL 2008 servers.
We delete rows from a local table based on running couple of joins on remote tables. How can we reduce the duration.
linked servers are slow, usually because you don't realize what is happening behind the scenes.
if you delete data from a linked server (ie delete from linkedserver.database.dbo.tablename, typically, the entire remote table is copied to temp db,and joined agaisnt your local tables, and used to create non-obvious deleted that are handled by the provider.
try doing the work at the remote server instead.
you can use EXECUTE ...AT with a static string for example. you might have to do it all via dynamic SQL, but try to avoid joining remote to local; materialize the data at teh remote instead.
EXECUTE ( 'DELETE FROM AdventureWorks2008R2.dbo.SalesTbl WHERE SomeID IN(145,146,147,) ' ) AT REMOTE_LINKED_SERVER
Lowell
January 29, 2014 at 8:46 am
Lowell,
to make things clear, deletion on local table .. joins happen on remote tables (linked server tables)
delete from table where .. join linkedserver.db..table on ...
join linkedserver.db..table on ...
FOr this query too, should i do the operation on remote server first so as to reduce remote access?
January 29, 2014 at 8:53 am
ok, in that case, the idea is kind of the same...minimize the data you are selecting from the remote server.
a join to a local tablre, like this:
FROM Customers T1
INNER JOIN RemoteServer.DatabaseName.dbo.Invoices T2
ON T1.ID = T2.ID
that requires the entire remote table to be materialized in temp, and then the join is performed.
you want to limit the rows from the remote, maybe into a temp table, via openrowset with a filter of some type first;
you know your data better than me, maybe you can limit it by date range, or a range of IDS or something
SELECT *
INTO #tmp
FROM OPENQUERY(REMOTE_LINKED_SERVER,'SELECT ColumnList From DatabaseName.dbo.TableName WHERE DateCreated > ''2014-01-01'' ;')
Lowell
January 29, 2014 at 9:00 am
How big are the tables on the other server?
I use linked servers quite extensively between SQL and also Oracle and sometimes you have to play around with how you do it.
When you query the linked server with dotted notation (servername.schema.table) it is usually slower because of all the parsing it has to do. If at all possible, I always use openquery notation as this is usually faster and just passes the query through.
My suggestion, if the tables on the other server are not huge - is to do an openquery to pull them down into your local database first (into temp tables) and then do your delete based on the temp tables. Sometimes its actually much faster to pull the entire table down (or if you can filter anything out of it first) and then do all the work locally.
IE:
SELECT * INTO #temp_table openquery(servername,'select * from schema.table_name')
Then you can do your local delete statement by joining to the temp table. I do this a lot when I'm trying to work between SQL Server and Oracle and some of the Oracle tables I'm using are a couple hundred thousand records large but it is actually faster for me to bring that table into my sql server rather than try to do stuff with it via the linked server.
January 29, 2014 at 9:01 am
LOL, Lowell got to it first... took me a few minutes to type all that up... 😛 At least we both said the same thing hehe!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply