question

Linking project: Can anyone help me optimized the below T-SQL scripts one for mappable and the other for unmappable: Mappable scripts: DECLARE @pmtid int DECLARE @prop_name nvarchar(4000) DECLARE @prop_addr1 nvarchar(4000) DECLARE @prop_city nvarchar(4000) DECLARE @prop_zip nvarchar(4000) DECLARE tblName_cur1 CURSOR FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city, prop_zip FROM tblname_CA WITH (NOLOCK) WHERE linked_pmt_id IS NULL AND (status <> 'U' AND addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL) AND prop_city IS NOT NULL AND prop_zip IS NOT NULL ORDER BY prop_name, prop_addr1, prop_city, prop_zip, pmt_id ASC OPEN tblName_cur1 FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip WHILE @@FETCH_STATUS = 0 BEGIN IF (@prop_name IS NULL or @prop_addr1 IS NULL) UPDATE tblname_CA SET [linked_pmt_id] = @pmtid, looptag = '1Ma-NBCNBZ' WHERE linked_pmt_id IS NULL AND (Status <> 'U' AND ( addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL)) AND (prop_city IS NOT NULL AND prop_city = @prop_city) AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip) AND ISNULL(prop_addr1,'NULL') = ISNULL(@prop_addr1,'NULL') AND ISNULL(prop_name,'NULL') = ISNULL(@prop_name,'NULL') ELSE UPDATE tblname_CA SET [linked_pmt_id] = @pmtid, looptag = '1Ma-NBCNBZ' WHERE linked_pmt_id IS NULL AND (Status <> 'U' AND ( addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND addr_type IS NOT NULL)) AND (prop_city IS NOT NULL AND prop_city = @prop_city) AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip) AND prop_addr1 = @prop_addr1 AND prop_name = @prop_name FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip END CLOSE tblName_cur1 DEALLOCATE tblName_cur1 GO Unmappable script: DECLARE @pmtid int DECLARE @prop_name nvarchar(4000) DECLARE @prop_addr1 nvarchar(4000) DECLARE @prop_city nvarchar(255) DECLARE @prop_zip nvarchar(255) DECLARE tblName_cur1 CURSOR FOR SELECT PMT_ID, prop_name, prop_addr1, prop_city, prop_zip FROM tblname WITH(NOLOCK) WHERE LINKED_PMT_ID IS NULL AND (STATUS = 'U' OR (status <> 'U' AND ADDR_TYPE IN ( 'GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL)) AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%') AND prop_city IS NOT NULL AND prop_zip IS NOT NULL OPEN tblName_cur1 FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip WHILE @@FETCH_STATUS = 0 BEGIN IF (@prop_name IS NULL) UPDATE tblname SET [linked_pmt_id] = @pmtid, looptag = '2Ua-NBCNBZ' WHERE linked_pmt_id IS NULL AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL))) AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%') AND prop_addr1 = @prop_addr1 AND prop_name IS NULL AND (prop_city IS NOT NULL AND prop_city = @prop_city) AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip) ELSE UPDATE tblname SET [linked_pmt_id] = @pmtid, looptag = '2Ua-NBCNBZ' WHERE linked_pmt_id IS NULL AND (status = 'U' OR (status <> 'U' AND (addr_type IN ('GIS LOCATION','ASSIGNED LOCATION') OR addr_type IS NULL))) AND (prop_addr1 IS NOT NULL AND prop_addr1 LIKE '[0-9]%') AND prop_addr1 = @prop_addr1 AND prop_name = @prop_name AND (prop_city IS NOT NULL AND prop_city = @prop_city) AND (prop_zip IS NOT NULL AND prop_zip = @prop_zip) FETCH NEXT FROM tblName_cur1 INTO @pmtid, @prop_name, @prop_addr1, @prop_city, @prop_zip END CLOSE tblName_cur1 DEALLOCATE tblName_cur1 GO Sample table: PMT_ID Status Addr_type prop_name prop_city prop_county prop_state prop_country prop_zip linked_pmt_id looptag 327041579 U NULL GREENLAND CORPORATION CALGARY NULL AB CAN T2C 1N9 NULL NULL 330154038 U NULL STENA DRILLING LIMITED ABERDEEN AB AB123BG NULL NULL 330720879 U NULL NEWALTA ENVIRONMENTAL SERVICES FT SASKATCHEWAN AB T8L 2 NULL NULL 330725081 U NULL CUSTOM ENVIRONMENTAL SVCS EDMONTON AB T6P 1 NULL NULL 330725083 U NULL ALASKA WEST EXPRESS INC EDMONTON AB T6E 5 NULL NULL 330725085 U NULL TRI LINE EXPRESSWAYS LTD CALGARY AB T2C 2 NULL NULL 330725089 U NULL ALASKA WEST EXPRESS INC EDMONTON AB T5L 3 NULL NULL 330725094 U NULL INDUSTRIAL BY PRODUCT RECYCLING INC CALGARY AB T2R 0 NULL NULL 330725095 U NULL MULLEN TRUCKING LTD ALDERSYDE AB T0L 0 NULL NULL 330725098 U NULL CANADA FOREST PRODUCTS SALES L CALGARY AB T2C 1 NULL NULL 330725108 U NULL WESTCAN BULK TRANSPORT LTD CALGARY AB T2C 1 NULL NULL 330725109 U NULL HAZCO TRANSPORTATION SERVICES LTD CALGARY AB T2H 1 NULL NULL 330725112 U NULL CROSSLINE CARRIERS INC CALGARY AB T2C 2 NULL NULL 330725115 U NULL ECONOMY CARRIERS LTD CALGARY AB T2G 4 NULL NULL 330725117 U NULL HAZMAT TRANSPORTATION SVCS LTD CALGARY AB T2G 4 NULL NULL The result will be those records with the same prop_name, prop_city, prop_zip, prop_county will have to belong on the minimum pmt_id and we call it "linked". Same happend for the unmappable scripts. My script is working fine but the efficiency is very slow even using temp table and a while statement. Cursor is much faster for the mappable but very slow in the unmappable specially if the record for unmappable would be 400000 and up. It would take me 3 days or more to finish. I have partition the table provide proper indexes on the table. I cannot make use of the columnstore index due to the edition that we have on the prod which is a developer edition. Thanks so much for the abrupt help. Can anyone translate this using a window function?