August 5, 2014 at 3:23 pm
Is there anyway to improve performance of below linked query
Delete from [Myserver].db.dbo.z2t where UniqueKey in (Select UniqueKey from #DeleteKeys where CAST(ZipCode AS INT) < 9999 )
This takes more than 2 hrs to execute
Note that UniqueKey is not an int.. its a varchar.. looks like
V110566207
V110566208
V110566209 -- first char can vary
ZipCode is a varchar so need to convert to int-the subquery on its own takes just aroung 2-3 seconds
#DeleteKeys is a temp tables
the query is a linked query to another server
I have already created a non clustered index on UniqueKey column on z2tn and after that this takes 2hrs
Index that i have created is
CREATE NONCLUSTERED INDEX [z2t_Zip4G0_UniqueKey] ON [dbo].[z2t]
(
[UniqueKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Excution paln
Delete Cost:0% <- Remote delete Cost:0% <- Table spool(eager spool) Cost:0% <- Hash match (right semi join)Cost:2% <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%
All arrors are liner excep that the last 2 ( <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%) both join Hash match (right semi join)Cost:2%
August 5, 2014 at 3:32 pm
mishrakanchan86 (8/5/2014)
Is there anyway to improve performance of below linked queryDelete from [Myserver].db.dbo.z2t where UniqueKey in (Select UniqueKey from #DeleteKeys where CAST(ZipCode AS INT) < 9999 )
This takes more than 2 hrs to execute
Note that UniqueKey is not an int.. its a varchar.. looks like
V110566207
V110566208
V110566209 -- first char can vary
ZipCode is a varchar so need to convert to int-the subquery on its own takes just aroung 2-3 seconds
#DeleteKeys is a temp tables
the query is a linked query to another server
I have already created a non clustered index on UniqueKey column on z2tn and after that this takes 2hrs
Index that i have created is
CREATE NONCLUSTERED INDEX [z2t_Zip4G0_UniqueKey] ON [dbo].[z2t]
(
[UniqueKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Excution paln
Delete Cost:0% <- Remote delete Cost:0% <- Table spool(eager spool) Cost:0% <- Hash match (right semi join)Cost:2% <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%
All arrors are liner excep that the last 2 ( <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%) both join Hash match (right semi join)Cost:2%
My first suggestions is to move the #deleteKeys table to the linked server
- add a bit column (ZipBit) to hold the "CAST(ZipCode)<9999" result
- update the #deleteKeys.ZipBit column with that formula
- then run the delete ON the linked server, make an SP in the target DB and call it
That's not all that can be done, but it should speed it up a bit.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 5, 2014 at 3:33 pm
Jason Selburg (8/5/2014)
My first suggestions is to move the #deleteKeys table to the linked server, add a bit column to hold the "CAST(ZipCode)<9999" result. Then run the delete ON the linked server.
Exactly this. The current cost is the distributed query, the data set is being read across the network, searched locally and then the delete sent back.
Can you put this into a stored proc on the other server? Failing that, use OPENQUERY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 3:41 pm
But in this way I will have to copy zipbit column to the linbked server which will again need matching the uniquekey column for all rows.. uniquekey is a varchar..
I know the index i have created on uniquekey is not correct because its a varchar.. How can i chnage this index to suit my query???
August 5, 2014 at 3:49 pm
UniqueKey is not your problem.
Think of it this way. Server A doesn't know anything about Server B's indexes or stats. A proper query plan can not be built because of this. Essentially you're performing a table scan across the network.
By MOVING the # table to the target server performing the delete there, you allow the engine to do what it's meant to do.
Also, by creating the new "bit" column and applying that update before the delete you remove any blocking caused by the time to do the conversion on the fly.
** correct me if I'm wrong Gila. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 5, 2014 at 3:57 pm
populating that column will need a join between the 2 linked servers which can be even more costly...
But i can create a new table with just the zipcode and unique key colum on the linked server and copy the temp table data on to it.. i will be creting this table on the fly and then deleting it after my delete process is done.. will this help??.. or its there a way to create temp table on the linked server??
August 5, 2014 at 4:17 pm
Try this instead.....
---- on server B ([Myserver].db)
---- create a table type
CREATE TYPE DeleteKeys AS TABLE
(UniqueKey VARCHAR(10) PRIMARY KEY CLUSTERED)
GO
---- create the procedure to accept the table valued parameter
CREATE PROCEDURE dbo.DeleteUniqueKeys
(@DeleteKeys DeleteKeys READONLY)
AS
BEGIN
DELETE a
FROM [Myserver].db.dbo.z2t AS a
INNER JOIN @DeleteKeys AS b
ON a.UniqueKey = b.UniqueKey
END
GO
---- on server A
---- actually just remove the "good" ZipCodes before passing over to server b
DELETE #DeleteKeys WHERE (CAST ZipCode AS INT) >= 9999);
EXEC [Myserver].db.dbo.DeleteUniqueKeys;
[EDITED] Typo fixed
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 5, 2014 at 4:24 pm
Sorry, one last change...
EXEC [Myserver].db.dbo.DeleteUniqueKeys #DeleteKeys;
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply