asked
SQL UPDATE PERFOMANCE IMPROVEMENT ??
I have a table SiteIdTable . I would like to fill ids to Locationid field in an incremental fashion for those records the LocationId is null . There are a million records in the table CREATE TABLE [dbo].[SiteIDTable]( [LocationId] [int] NULL, [mem_Address1] [nvarchar](50) NULL, [mem_Address2] [nvarchar](50) NULL, [mem_City] [nvarchar](50) NULL, [mem_zip] [nvarchar](50) NULL ) ON [PRIMARY] INSERT INTO [dbo].[SiteIDTable] ([LocationId] ,[mem_Address1] ,[mem_Address2] ,[mem_City] ,[mem_zip]) SELECT 1 , ' 123 COURTVILLE', '123 COURTVILLE' , 'VILLE' , 6012 UNION SELECT NULL , ' 23 COURTVILLE', '34 COURTVILLE' , 'VILLE' , 4512 UNION SELECT NULL , ' 55 COURTVILLE', '55 COURTVILLE' , 'VILLE' , 7845 UNION SELECT 2 , ' 58 COURTVILLE', '74 COURTVILLE' , 'VILLE' , 7845 UNION SELECT NULL , ' 14 COURTVILLE', '98 COURTVILLE' , 'VILLE' , 6845 My query is SELECT IDENTITY (int,1,1) as [LocationId], ISNULL(mem_Address1,'''') mem_Address1, ISNULL(mem_Address2,'''') mem_Address2, ISNULL(mem_City,'''') mem_City, ISNULL(mem_zip,'''') mem_zip INTO #tmp_SiteIDTable FROM [dbo].[SiteIDTable] WHERE LocationID IS NULL GROUP BY ISNULL(mem_Address1,''''), ISNULL(mem_Address2,''''), ISNULL(mem_City,''''), ISNULL(mem_zip,'''') ORDER BY mem_Address1, mem_Address2 UPDATE mem SET mem.LocationID= t.LocationID FROM [dbo].[SiteIDTable] mem INNER JOIN #tmp_SiteIDTable t ON ISNULL(mem.mem_Address1,'''') = ISNULL(t.mem_Address1 ,'''') AND ISNULL(mem.mem_Address2,'''') = ISNULL(t.mem_Address2,'''') AND ISNULL(mem.mem_City,'''') = ISNULL(t.mem_City,'''') AND ISNULL(mem.mem_zip,'''') = ISNULL(t.mem_zip,'''') Can you suggest me the best way to do it , in terms of perfomance?
Using functions in the ON clause like you have with the ISNULL values will absolutely lead to scans. Instead you can use = between the values and OR IS NULL. This is more likely to get index use if you put indexes on the table, and, if you're filtering a table, you really should have an index for it. So one on Address1, Address2, City and Zip would be needed here.
19 People are following this question.
