StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>From a performance perspective, what you have there is the version on which I would expect the optimizer to do the best job.</p> <p>If the "outer" part of your example is static and code maintenance overrides performance, I'd look to encapsulating the dateadd result in a table-valued function (TVF). Since the time conversion is very much the common thread in these queries, I would definitely focus on that part of the workload. </p> <p>For example, your query that can vary would look like this:</p> <pre><code>select timeGMT, dataCol2, dataCol3, lt.timeLocal from tbl1 t1 join tbl2 t2 on t1.ID = t2.ID cross apply dbo.LocalTimeGet(timeGMT, 'US-Eastern') AS lt </code></pre> <p>Where the TVF dbo.LocalTimeGet contains the logic for dateadd(ss,d.gmtOffset,t.timeGMT) and the lookup of the time zone offset value based on the time zone name. The implementation of that function would look something like:</p> <pre><code>CREATE FUNCTION dbo.LocalTimeGet ( @TimeGMT datetime, @TimeZone varchar(20) ) RETURNS TABLE AS RETURN ( SELECT DATEADD(ss, d.gmtOffset, @TimeGMT) AS timeLocal FROM dst AS d WHERE d.zone = @TimeZone ); GO </code></pre> <p>The upside of this approach is when you upgrade to 2008 or later, there are system functions you could use to make this conversion a lot easier to code and you'll only have to alter the TVF. If your result sets are small, I'd consider a system scalar function (SQL 2008) over a TVF, even if it implements those same system functions. Based on your comment, it sounds like the system functions won't do what you need, but you could still stick with your implementation of a dst table, which is encapsulated in the TVF above.</p> <p>TVFs can be a performance problem because the optimizer assumes they only return 1 row.</p> <p>If you need to combine encapsulation and performance, then I'd do the time zone calc in the application code instead. Even though you'd have to apply it to each project that uses it, you would only have to implement it 1x in each project (in the Data Access Layer) and treat it as a common utility library if you'll be using across projects.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload