March 26, 2019 at 9:47 am
Hi, thanks for your help in advance. Is there a way to make this query faster
DELETE FROM dbo.polygon dest
WHERE NOT EXISTS (
SELECT 1 FROM dbo.polygon srce
WHERE srce.changedetect_uuid = dest.changedetect_uuid);
Is the query taking into account all the fields or is it only looking at the single joined field? The other tables is in a linked server and I can't get around that unfortunately.
Many Thanks for any advice you may have,
Oliver
March 26, 2019 at 10:21 am
olibbhq - Tuesday, March 26, 2019 9:47 AMHi, thanks for your help in advance. Is there a way to make this query fasterDELETE FROM dbo.polygon dest
WHERE NOT EXISTS (
SELECT 1 FROM dbo.polygon srce
WHERE srce.changedetect_uuid = dest.changedetect_uuid);Is the query taking into account all the fields or is it only looking at the single joined field? The other tables is in a linked server and I can't get around that unfortunately.
Many Thanks for any advice you may have,
Oliver
How many rows are in each table? You'd be better off pulling the srce uuids to the local server and joining there, unless there are millions of rows in the srce and only a very limited number of rows in the dest.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2019 at 10:29 am
Hi, there are roughly the same number of rows in each table, about 1million. When I use locally it is very quick taking seconds, when running with the remote server it takes minutes. Is it faster to pull the data to a temporary table then do the join?
Thank you
March 26, 2019 at 10:42 am
olibbhq - Tuesday, March 26, 2019 9:47 AMHi, thanks for your help in advance. Is there a way to make this query fasterDELETE FROM dbo.polygon dest
WHERE NOT EXISTS (
SELECT 1 FROM dbo.polygon srce
WHERE srce.changedetect_uuid = dest.changedetect_uuid);Is the query taking into account all the fields or is it only looking at the single joined field? The other tables is in a linked server and I can't get around that unfortunately.
Many Thanks for any advice you may have,
Oliver
The query is checking the same table against the same table and the same column against the same column. So it won't delete anything.
So if you want to improve efficiency just don't run the statement.
March 26, 2019 at 10:56 am
Bringing a copy of the uuid over from the source to the destination and running the query on destination almost halved the time. Any further suggestions very welcome, thank you.
March 26, 2019 at 11:06 am
olibbhq - Tuesday, March 26, 2019 10:56 AMBringing a copy of the uuid over from the source to the destination and running the query on destination almost halved the time. Any further suggestions very welcome, thank you.
Cluster the temporary table on the changedetect_uuid value before loading the table. That is, create the table with a clustered index in one step, then load the table in the next step. This may or may not help in the current situation, but it could in future situations, so it's the correct approach.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2019 at 11:28 am
thanks appreciate the hint!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply