Update works on some systems but on few others

Question

Update works on some systems but on few others

rajawat.niranjan

Old Hand

Points: 373
More actions
January 26, 2013 at 9:39 pm

#273505

Hi All,
I have some update and insert queries to update GEO location data, which when I execute on my system it works fine taking around 15 mins to complete on one another test system it works the same way, but there is an another system on which it never finishes execution it always gets stuck at specific block causing other services to stop working...
please provide any possible solution
query sample:
PRINT N'Creating dbo.#TB_Geo_data...'
create TABLE [dbo].[#TB_Geo_data](
[Country] [varchar](50) NULL,
[Language] [varchar](50) NULL,
[ID] [varchar](50) NULL,
[ISO2] [varchar](50) NULL,
[Region1] [varchar](200) NULL,
[Region2] [varchar](200) NULL,
[Region3] [varchar](200) NULL,
[Region4] [varchar](200) NULL,
[ZIP] [varchar](50) NULL,
[City] [nvarchar](300) NULL,
[Area1] [varchar](200) NULL,
[Area2] [nvarchar](200) NULL,
[Lat] [varchar](50) NULL,
[Lng] [varchar](50) NULL,
[TZ] [varchar](100) NULL,
[UTC] [varchar](50) NULL,
[DST] [varchar](50) NULL
)
GO
PRINT N'Inserting into dbo.#TB_Geo_data...'
GO
BULK INSERT #TB_Geo_data
FROM 'C:\Users\Administrator\Desktop\GeoPC_WO-UTF-16.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '',
datafiletype = 'widechar'
)
GO
---------Update #TB_Geo_data ------
UPDATE #TB_Geo_data
SET Region2='"Bruxelles"',Region3='"Bruxelles"',Region4='"Bruxelles"'
WHERE Country='"BE"' and Language='"FR"' AND Region1='"Bruxelles-Capitale"' AND Region2='""' AND REGION3='""' AND Region4='""'
UPDATE #TB_Geo_data
SET Region2='"Brussel"',Region3='"Brussel"',Region4='"Brussel"'
WHERE Country='"BE"' and Language='"NL"' AND Region1='"Brussels Hoofdstedelijk Gewest"' AND Region2='""' AND REGION3='""' AND Region4='""'
PRINT N'Insert and Update Geodata for Belgium ...'
PRINT N'Inserting Province Data ...'
IF NOT EXISTS (select 1 from geodata.TB_Province where provincelevel =1 and ProvinceCountryID=121)
BEGIN
--REGION1
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceLevel)
select distinct replace(region1,'"','') ProvinceDefaultName,cu.CountryInternalID ProvinceCountryID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID ,1 ProvinceLevel from #TB_Geo_data gd , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu where gd.Country='"BE"' and gd.Language='"FR"'and la.LanguageCultureCodeID='fr-be' and cu.CountryDefaultName='Belgium' and Region1<>'""'
END
--REGION2
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =1 and ProvinceCountryID=121)
BEGIN
update GeoData.TB_Province
set ProvinceLevel=2, ProvinceInternalID_1=pr3.ProvinceInternalID
from geodata.TB_Province pr2 inner join (select distinct pr1.ProvinceInternalID ,gd.Region1,gd.Region2 from #TB_Geo_data gd inner join geodata.TB_Province pr1 on REPLACE(gd.Region1,'"','')=pr1.ProvinceDefaultName and pr1.ProvinceLevel=1 and gd.Country='"BE"' and gd.Language='"FR"' and pr1.ProvinceCountryID=121 )pr3
on pr2.ProvinceDefaultName=replace(pr3.Region2,'"','') and pr2.ProvinceCountryID=121 and pr2.ProvinceInternalID_1 is null and pr2.ProvinceLevel is null
END
--REGION3
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =2 and ProvinceCountryID=121)
IF NOT EXISTS (select 1 from geodata.TB_Province where ProvinceLevel=3 and ProvinceCountryID=121)
BEGIN
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceInternalID_1,ProvinceInternalID_2,ProvinceLevel)
select distinct replace(region3,'"','') ProvinceDefaultName,cu.CountryInternalID ProvinceCountryID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID,pr.ProvinceInternalID_1 ProvinceInternalID_1,pr.ProvinceInternalID ProvinceInternalID_2,3 ProvinceLevel from [#TB_Geo_data] gd inner join GeoData .TB_Province pr on REPLACE(gd.Region2,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=2 , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu where pr.ProvinceCountryID=cu.CountryInternalID and PR.ProvinceInternalID_1=(SELECT PR2.ProvinceInternalID FROM geodata.TB_Province PR2 WHERE PR2.ProvinceDefaultName=replace(GD.Region1,'"','') AND PR2.ProvinceLevel=1 and pr2.ProvinceCountryID=cu.CountryInternalID)and gd.Country='"BE"' and gd.Language='"FR"'and la.LanguageCultureCodeID='fr-be' and cu.CountryDefaultName='Belgium' and Region3<>'""'
END
--REGION4
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =3 and ProvinceCountryID=121)
IF NOT EXISTS (select 1 from geodata.TB_Province where ProvinceLevel=4 and ProvinceCountryID=121)
BEGIN
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceInternalID_1,ProvinceInternalID_2,ProvinceInternalID_3,ProvinceLevel)
select distinct replace(region4,'"','') ProvinceDefaultName,cu.CountryInternalID ProvinceCountryID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID,pr.ProvinceInternalID_1 ProvinceInternalID_1,pr.ProvinceInternalID_2 ProvinceInternalID_2,pr.ProvinceInternalID ProvinceInternalID_3,4 ProvinceLevel from [#TB_Geo_data] gd inner join GeoData .TB_Province pr on REPLACE(gd.Region3,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=3 , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu WHERE pr.ProvinceCountryID=cu.CountryInternalID and PR.ProvinceInternalID_2=(SELECT PR3.ProvinceInternalID FROM geodata.TB_Province PR3 WHERE PR3.ProvinceDefaultName=replace(GD.Region2,'"','') AND PR3.ProvinceLevel=2 and pr3.ProvinceCountryID=cu.CountryInternalID)AND PR.ProvinceInternalID_1=(SELECT PR2.ProvinceInternalID FROM geodata.TB_Province PR2 WHERE PR2.ProvinceDefaultName=replace(GD.Region1,'"','') AND PR2.ProvinceLevel=1 and pr2.ProvinceCountryID=cu.CountryInternalID) and gd.Country='"BE"' and gd.Language='"FR"'and la.LanguageCultureCodeID='fr-be' and cu.CountryDefaultName='Belgium' and Region4<>'""'
END
PRINT N'Update City GeoData...'
--CITY
IF EXISTS (select 1 from geodata.TB_Province where provincelevel IN(1,2,3,4) and ProvinceCountryID=121)
BEGIN
--Region4 is not null
update geodata.TB_City
set [CityProvinceID_1]=res.CityProvinceID_1,[CityProvinceID_2]=res.CityProvinceID_2,[CityProvinceID_3]=res.CityProvinceID_3,[CityProvinceID_4]=res.CityProvinceID_4
from
(select distinct replace(gd.city,'"','') CityName,[Lng] Longitude,[Lat] Latitude,REPLACE(zip,'"','')ZipCodes,CU.CountryInternalID CountryInternalID,1 StatusID,CU.CountryRegionID RegionID,'system' CreatedBy,pr.ProvinceLanguageID ,pr.ProvinceInternalID_1 [CityProvinceID_1],pr.ProvinceInternalID_2 [CityProvinceID_2],pr.ProvinceInternalID_3 [CityProvinceID_3],pr.ProvinceInternalID [CityProvinceID_4]
from [#TB_Geo_data] gd
inner join [GeoData].[TB_Province] pr
on pr.ProvinceDefaultName=REPLACE(gd.Region4,'"','')
and pr.ProvinceLevel=4 ,GeoData.TB_Country cu
where PR.ProvinceInternalID_3 IN (SELECT PR2.ProvinceInternalID FROM geodata.TB_Province PR2 WHERE PR2.ProvinceDefaultName=replace(Region3,'"','') AND PR2.ProvinceLevel=3 and PR2.ProvinceCountryID=cu.CountryInternalID)
AND PR.ProvinceInternalID_2 IN (SELECT PR3.ProvinceInternalID FROM geodata.TB_Province PR3 WHERE PR3.ProvinceDefaultName=replace(Region2,'"','') AND PR3.ProvinceLevel=2 and PR3.ProvinceCountryID=cu.CountryInternalID)
AND PR.ProvinceInternalID_1 =(SELECT PR4.ProvinceInternalID FROM geodata.TB_Province PR4 WHERE PR4.ProvinceDefaultName=replace(Region1,'"','') AND PR4.ProvinceLevel=1 and PR4.ProvinceCountryID=cu.CountryInternalID)
AND gd.Country='"BE"' and gd.Language='"FR"' and gd.Region4<>'""'
AND cu.CountryDefaultName='Belgium' and pr.ProvinceCountryID=cu.CountryInternalID
)res
where CityDefaultName=res.CityName and Latitude=res.Latitude and Longitude=res.Longitude and CityZipCodes=res.ZipCodes and CityCountryID=res.CountryInternalID and CityStatusID=1 and CityRegionID=res.RegionID
END
PRINT N'Insert and Update Province Translation Data...'
IF NOT EXISTS (select 1 from geodata.TB_ProvinceTranslation PT INNER JOIN geodata.TB_Province PR ON PT.ProvinceInternalID=PR.ProvinceInternalID AND PR.ProvinceLevel=1 AND PR.ProvinceCountryID=121)
BEGIN
--Region1
insert into [GeoData].[TB_ProvinceTranslation] (ProvinceInternalID,[LanguageInternalID],[ProvinceTranslationStatusID],[ProvinceName],[CreatedBy],[ProvinceLevel])
select distinct pr.ProvinceInternalID,la.LanguageInternalID,1,replace(gd2.Region1,'"',''),'system',Pr.ProvinceLevel from #TB_Geo_data gd1 inner join #TB_Geo_data gd2 on gd1.ZIP=gd2.ZIP and gd1.ID=gd2.ID and gd1.Lat=gd2.Lat and gd1.Lng=gd2.Lng inner join geodata.TB_Province Pr on replace(gd1.Region1,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=1,geodata.TB_Country cu ,ReferenceData.TB_Language la where gd1.Country='"BE"' and gd2.Country='"BE"' and gd1.Language='"FR"' and gd2.Language='"NL"' and cu.CountryDefaultName='Belgium' and cu.CountryStatusID=1 and pr.ProvinceCountryID=cu.CountryInternalID and pr.ProvinceStatusID=1 and la.LanguageCultureCodeID='nl-be'
END
--Region2
update geodata.TB_ProvinceTranslation
set ProvinceLevel=2
from (select pr.ProvinceInternalID InternalID from geodata.TB_Province pr inner join geodata.TB_ProvinceTranslation pt on pr.ProvinceInternalID=pt.ProvinceInternalID and pr.ProvinceCountryID=(select CountryInternalID from geodata.TB_Country where CountryDefaultName='Belgium') and pr.ProvinceLevel=2)res
where ProvinceInternalID=res.InternalID and ProvinceLevel is NULL
--Region3
IF NOT EXISTS (select 1 from geodata.TB_ProvinceTranslation PT INNER JOIN geodata.TB_Province PR ON PT.ProvinceInternalID=PR.ProvinceInternalID AND PR.ProvinceLevel=3 AND PR.ProvinceCountryID=121)
BEGIN
insert into [GeoData].[TB_ProvinceTranslation] (ProvinceInternalID,[LanguageInternalID],[ProvinceTranslationStatusID],[ProvinceName],[CreatedBy],[ProvinceLevel])
select distinct pr.ProvinceInternalID,la.LanguageInternalID,1,replace(gd2.Region3,'"',''),'system',pr.ProvinceLevel from #TB_Geo_data gd1 inner join #TB_Geo_data gd2 on gd1.ZIP=gd2.ZIP and gd1.ID=gd2.ID and gd1.Lat=gd2.Lat and gd1.Lng=gd2.Lng inner join geodata.TB_Province Pr on replace(gd1.Region3,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=3 ,geodata.TB_Country cu ,ReferenceData.TB_Language la where gd1.Country='"BE"' and gd2.Country='"BE"' and gd1.Language='"FR"' and gd2.Language='"NL"' and cu.CountryDefaultName='Belgium' and cu.CountryStatusID=1 and pr.ProvinceCountryID=cu.CountryInternalID and pr.ProvinceStatusID=1 and la.LanguageCultureCodeID='nl-be' and pr.ProvinceInternalID_1 =(select pr2.ProvinceInternalID from GeoData.TB_Province pr2 where pr2.ProvinceDefaultName=REPLACE(gd1.Region1,'"','') and pr2.ProvinceLevel=1 and pr2.ProvinceCountryID=cu.CountryInternalID)and pr.ProvinceInternalID_2 =(select pr3.ProvinceInternalID from GeoData.TB_Province pr3 where pr3.ProvinceDefaultName=REPLACE(gd1.Region2,'"','') and pr3.ProvinceLevel=2 and pr3.ProvinceCountryID=cu.CountryInternalID) order by 1
END
--Region4
IF NOT EXISTS (select 1 from geodata.TB_ProvinceTranslation PT INNER JOIN geodata.TB_Province PR ON PT.ProvinceInternalID=PR.ProvinceInternalID AND PR.ProvinceLevel=4 AND PR.ProvinceCountryID=121)
BEGIN
insert into [GeoData].[TB_ProvinceTranslation] (ProvinceInternalID,[LanguageInternalID],[ProvinceTranslationStatusID],[ProvinceName],[CreatedBy],[ProvinceLevel])
select distinct pr.ProvinceInternalID,la.LanguageInternalID,1,replace(gd2.Region4,'"',''),'system',pr.ProvinceLevel from #TB_Geo_data gd1 inner join #TB_Geo_data gd2 on gd1.ZIP=gd2.ZIP and gd1.ID=gd2.ID and gd1.Lat=gd2.Lat and gd1.Lng=gd2.Lng inner join geodata.TB_Province Pr on replace(gd1.Region4,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=4 ,geodata.TB_Country cu ,ReferenceData.TB_Language la where gd1.Country='"BE"' and gd2.Country='"BE"' and gd1.Language='"FR"' and gd2.Language='"NL"' and cu.CountryDefaultName='Belgium' and cu.CountryStatusID=1 and pr.ProvinceCountryID=cu.CountryInternalID and pr.ProvinceStatusID=1 and la.LanguageCultureCodeID='nl-be' and pr.ProvinceInternalID_1 =(select pr2.ProvinceInternalID from GeoData.TB_Province pr2 where pr2.ProvinceDefaultName=REPLACE(gd1.Region1,'"','') and pr2.ProvinceLevel=1 and pr2.ProvinceCountryID=cu.CountryInternalID)and pr.ProvinceInternalID_2 =(select pr3.ProvinceInternalID from GeoData.TB_Province pr3 where pr3.ProvinceDefaultName=REPLACE(gd1.Region2,'"','') and pr3.ProvinceLevel=2 and pr3.ProvinceCountryID=cu.CountryInternalID) and pr.ProvinceInternalID_3 =(select pr4.ProvinceInternalID from GeoData.TB_Province pr4 where pr4.ProvinceDefaultName=REPLACE(gd1.Region3,'"','') and pr4.ProvinceLevel=3 and pr4.ProvinceCountryID=cu.CountryInternalID) order by 1
END
---------------ITALY----------------
UPDATE #TB_Geo_data
SET Region2='"Aosta"'
WHERE Country='"IT"' and Language='"IT"' AND Region2='"Valle d''Aosta"'
PRINT N'Insert and Update Province Geodata for ITALY ...'
--REGION1
IF NOT EXISTS (select 1 from geodata.TB_Province where provincelevel =1 and ProvinceCountryID=140)
BEGIN
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceLevel)
select * from
(select distinct replace(region1,'"','') ProvinceDefaultName,cu.CountryInternalID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID,1 ProvinceLevel from [#TB_Geo_data] gd , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu where gd.Country='"IT"' and gd.Language='"IT"'and la.LanguageCultureCodeID='it-it' and cu.CountryDefaultName='Italy' and Region1<>'""' ) tab
order by tab.ProvinceDefaultName
END
--REGION2
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =1 and ProvinceCountryID=140)
BEGIN
update GeoData.TB_Province
set ProvinceLevel=2, ProvinceInternalID_1=pr3.ProvinceInternalID
from geodata.TB_Province pr2 inner join (select distinct pr1.*,gd.Region1,gd.Region2 from #TB_Geo_data gd inner join geodata.TB_Province pr1 on REPLACE(gd.Region1,'"','')=pr1.ProvinceDefaultName and pr1.ProvinceLevel=1 and gd.Country='"IT"' and pr1.ProvinceCountryID=140 )pr3
on pr2.ProvinceDefaultName=replace(pr3.Region2,'"','') and pr2.ProvinceCountryID=140 and pr2.ProvinceLevel is null and pr2.ProvinceInternalID_1 is null
END
--REGION3
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =2 and ProvinceCountryID=140)
IF NOT EXISTS (select 1 from geodata.TB_Province where ProvinceLevel=3 and ProvinceCountryID=140)
BEGIN
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceInternalID_1,ProvinceInternalID_2,ProvinceLevel)
select * from
(select distinct replace(region3,'"','') ProvinceDefaultName,cu.CountryInternalID ProvinceCountryID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID,pr.ProvinceInternalID_1,pr.ProvinceInternalID,3 ProvinceLevel from [#TB_Geo_data] gd inner join GeoData .TB_Province pr on (REPLACE(gd.Region2,'"','')=pr.ProvinceDefaultName) and pr.ProvinceLevel=2 , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu where gd.Country='"IT"' and gd.Language='"IT"' and pr.ProvinceCountryID=cu.CountryInternalID and PR.ProvinceInternalID_1=(SELECT PR2.ProvinceInternalID FROM geodata.TB_Province PR2 WHERE PR2.ProvinceDefaultName=replace(GD.Region1,'"','') AND PR2.ProvinceLevel=1 and pr2.ProvinceCountryID=cu.CountryInternalID) and la.LanguageCultureCodeID='it-it' and cu.CountryDefaultName='Italy' and Region3<>'""' )tab
order by 1
END
--CITY
PRINT N'Update City Geodata for Italy ...'
IF EXISTS (select 1 from geodata.TB_Province where provincelevel IN(1,2,3) and ProvinceCountryID=140)
BEGIN
--Region3 is not null
update geodata.TB_City
set [CityProvinceID_1]=res.CityProvinceID_1,[CityProvinceID_2]=res.CityProvinceID_2,[CityProvinceID_3]=res.CityProvinceID_3
from
(select distinct replace(gd.city,'"','') CityName,[Lng] Longitude,[Lat] Latitude,REPLACE(zip,'"','') ZipCodes,CU.CountryInternalID CountryInternalID,1 StatusID ,CU.CountryRegionID RegionID,'system' CreatedBy,pr.ProvinceLanguageID ProvinceLanguageID,pr.ProvinceInternalID_1 CityProvinceID_1,pr.ProvinceInternalID_2 CityProvinceID_2,pr.ProvinceInternalID CityProvinceID_3
from [#TB_Geo_data] gd
inner join [GeoData].[TB_Province] pr
on ( pr.ProvinceDefaultName=REPLACE(gd.Region3,'"','')
and pr.ProvinceLevel=3 ) ,GeoData.TB_Country cu
where PR.ProvinceInternalID_2 IN (SELECT PR3.ProvinceInternalID FROM geodata.TB_Province PR3 WHERE PR3.ProvinceDefaultName=replace(gd.Region2,'"','') AND PR3.ProvinceLevel=2 and PR3.ProvinceCountryID=cu.CountryInternalID)
AND PR.ProvinceInternalID_1 =(SELECT PR4.ProvinceInternalID FROM geodata.TB_Province PR4 WHERE PR4.ProvinceDefaultName=replace(gd.Region1,'"','') AND PR4.ProvinceLevel=1 and PR4.ProvinceCountryID=cu.CountryInternalID)
AND gd.Country='"IT"' and gd.Language='"IT"' and gd.Region3<>'""' and gd.Region4='""'
AND cu.CountryDefaultName='ITALY' and pr.ProvinceCountryID=cu.CountryInternalID
)res
where CityDefaultName=res.CityName and Latitude=res.Latitude and Longitude=res.Longitude and CityZipCodes=res.ZipCodes and CityCountryID=res.CountryInternalID and CityStatusID=1 and CityRegionID=res.RegionID
END
---------------NETHERLANDS--------------
PRINT N'Update Province Data For Netherlands ...'
BEGIN
--REGION1
update GeoData.TB_Province
set ProvinceLevel=1
from #TB_Geo_data gd inner join GeoData.TB_Province pr on ltrim(rtrim(REPLACE(gd.Region1,'"','')))= pr.ProvinceDefaultName and pr.ProvinceCountryID=(select CountryInternalID from GeoData.TB_Country where CountryDefaultName='Netherlands') and gd.Country='"NL"'
where ProvinceDefaultName=pr.ProvinceDefaultName and ProvinceCountryID=pr.ProvinceCountryID and ProvinceLevel is NULL
END
PRINT N'Insert Province Data For Netherlands ...'
--REGION2
IF EXISTS (select 1 from geodata.TB_Province where provincelevel =1 and ProvinceCountryID=152)
IF NOT EXISTS (select 1 from geodata.TB_Province where ProvinceLevel=2 and ProvinceCountryID=152)
BEGIN
insert into geodata.TB_Province(ProvinceDefaultName,ProvinceCountryID,ProvinceStatusID,CreatedBy,ProvinceLanguageID,ProvinceInternalID_1,ProvinceLevel)
select * from
(select distinct replace(region2,'"','') ProvinceDefaultName,cu.CountryInternalID ProvinceCountryID,1 ProvinceStatusID,'system' CreatedBy,la.LanguageInternalID ProvinceLanguageID,pr.ProvinceInternalID,2 ProvinceLevel from [#TB_Geo_data] gd inner join GeoData .TB_Province pr on REPLACE(gd.Region1,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=1 , [ReferenceData].[TB_Language] la ,GeoData.TB_Country cu where pr.ProvinceCountryID=cu.CountryInternalID and gd.Country='"NL"' and gd.Language='"nl"'and la.LanguageCultureCodeID='nl-nl' and cu.CountryDefaultName='Netherlands' and Region2<>'""')tab
order by 1
END
IF EXISTS (select 1 from geodata.TB_Province where provincelevel IN(1,2) and ProvinceCountryID=152)
BEGIN
update geodata.TB_City
set CityProvinceID_2=pr.ProvinceInternalID,CityProvinceID_1=PR.ProvinceInternalID_1
from geodata.TB_Province pr inner join #TB_Geo_data gd on replace(gd.Region2,'"','')=pr.ProvinceDefaultName and pr.ProvinceLevel=2 and gd.Country='"NL"' and pr.ProvinceCountryID=152
where CityDefaultName=replace(gd.City,'"','') and CityProvinceID=pr.ProvinceInternalID_1 and CityZipCodes=ltrim(rtrim(replace(gd.ZIP,'"',''))) and CityLatitude=gd.Lat and CityLongitude=gd.Lng and CityCountryID=152 and CityProvinceID_1 is NULL
END
quoted code never finishes execution
and here are result messages:
Creating dbo.#TB_Geo_data...
Inserting into dbo.#TB_Geo_data...
(6207830 row(s) affected)
(22 row(s) affected)
(22 row(s) affected)
Insert and Update Geodata for Belgium ...
Inserting Province Data ...
(0 row(s) affected)
Update City GeoData...
(2776 row(s) affected)
Insert and Update Province Translation Data...
(0 row(s) affected)
(131 row(s) affected)
Insert and Update Province Geodata for ITALY ...
(0 row(s) affected)
Update City Geodata for Italy ...
(16825 row(s) affected)
Update Province Data For Netherlands ...
(0 row(s) affected)
Insert Province Data For Netherlands ...
Update City Data For Netherlands ...
The statement has been terminated.
Query was cancelled by user.
this took 4 hours and then i terminated it
I'd try it 4 times same result every time..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

The fact that the code works on some systems and not others indicates the code is likely sound but that there may be an environmental factor in play preventing success only in some situations. It could be a lot of things though, including code, for example blocking, poor hardware causing long delays, a trigger on one system not on some others, index fragmentation or out of date statistics could be causing the query to take longer to complete.

When you are running the process you can check the activity of that query to see what resource it is waiting on and whether it is blocked or just taking a longer time than expected to complete. You could fire up Activity Monitor from SSMS or get a copy of sp_WhoIsActive, either way you can filter on your SPID to see what's happening when you run your process.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

rajawat.niranjan Old Hand Points: 373 More actions · Answer 2

I have tried on activity monitor as it shows wait type as IO_COMPLETION.

I have tried creating indexes on a table which i join while updating

but query still does not completing execution...

🙁

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

IO_COMPLETION means the code is working but the database engine is waiting for the IO system to finish committing the transaction. It sounds like the system that it will not finish on is just much slower than the other systems where it finishes.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato