Azure SQL Database性能视角：如何避免使用函数？含UTC转本地时间场景

Great question—since you’re still in the development phase (months before your database goes live), this is the ideal window to refactor and steer clear of scalar function performance pitfalls. Here are practical, Azure SQL-specific alternatives tailored to your timezone conversion scenario:

• Precompute timezone offsets (with daylight savings support)
  Instead of calculating offsets on-the-fly with a scalar function, create a dedicated TimeZoneOffsets table that stores the current UTC offset (in minutes) for each timezone in your Config table. Refresh this data periodically (e.g., daily via a scheduled Azure Automation runbook or SQL Agent job for managed instances) using built-in functions to handle daylight savings changes.
  When converting times, join directly to this offsets table:

  SELECT 
      dt.utc_time,
      DATEADD(minute, tz.offset_minutes, dt.utc_time) AS local_time
  FROM YourDataTable dt
  JOIN Config c ON dt.config_id = c.id
  JOIN TimeZoneOffsets tz ON c.timezone_name = tz.timezone_name
  

• Swap scalar functions for Inline Table-Valued Functions (ITVF)
  If you need reusable conversion logic, ITVFs are far more optimizer-friendly than scalar functions—they get expanded into the main query plan instead of being executed row-by-row. Here’s an example:

  CREATE FUNCTION dbo.ConvertUtcToLocal (@UtcTime DATETIME, @TimeZoneName NVARCHAR(100))
  RETURNS TABLE
  AS
  RETURN
  SELECT @UtcTime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZoneName AS LocalTime;
  

  Use it with CROSS APPLY for clean, performant queries:

  SELECT 
      dt.utc_time,
      cv.LocalTime AS local_time
  FROM YourDataTable dt
  JOIN Config c ON dt.config_id = c.id
  CROSS APPLY dbo.ConvertUtcToLocal(dt.utc_time, c.timezone_name) cv
  

• Handle conversion at the application layer
  If your app has access to the timezone value from the Config table, consider shifting the conversion work to your application code. Tools like .NET’s TimeZoneInfo, Python’s pytz, or JavaScript’s Intl.DateTimeFormat handle timezone logic seamlessly, offloading work from the database and eliminating scalar function overhead entirely.

• Use Azure SQL’s built-in AT TIME ZONE directly in queries
  Azure SQL Database natively supports AT TIME ZONE, which simplifies conversions without custom functions. Ensure your Config table’s timezone names match those in SQL Server’s supported list (get it via SELECT name FROM sys.time_zone_info), then use it directly:

  SELECT 
      dt.utc_time,
      dt.utc_time AT TIME ZONE 'UTC' AT TIME ZONE c.timezone_name AS local_time
  FROM YourDataTable dt
  JOIN Config c ON dt.config_id = c.id
  

  This automatically handles daylight savings and is much more efficient than wrapping the logic in a scalar function.

Since you’re still in development, test these approaches with your projected data volume to pick the best fit. Ditching scalar functions now will save you from performance headaches once your database goes live.

内容的提问来源于stack exchange，提问作者Philip