question

aRookieBIdev avatar image
aRookieBIdev 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?
sql server 2008 r2temporary-tableperfomance
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is there a need to have the results ordered by address? If not absolutely necessary removing that would improve the query quite a bit.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.