April 1, 2015 at 9:58 pm
Comments posted to this topic are about the item Using APPLY to make your queries DRYer
Gerald Britton, Pluralsight courses
April 2, 2015 at 1:08 am
Hi
Great article and this looks very interesting. How about performance, have you looked into that?
April 2, 2015 at 1:33 am
Generally with this sort of refactoring there is no change in performance, good or bad. You should see identical execution plans.
Gerald Britton, Pluralsight courses
April 2, 2015 at 1:57 am
This post explores one of the performance pitfalls you can get yourself into by introducing DRY principles with SQL server.
http://cubicmile.co.uk/2013/06/02/sql-code-re-use-a-worked-example/
April 2, 2015 at 3:01 am
waxingsatirical (4/2/2015)
This post explores one of the performance pitfalls you can get yourself into by introducing DRY principles with SQL server.http://cubicmile.co.uk/2013/06/02/sql-code-re-use-a-worked-example/
Using TVF is another story. CROSS APPLY with row-scope computations doesn't affect execution plan.
April 2, 2015 at 4:11 am
Hi.
Wouldn't the logic of the APPLY syntax be logically similar to the following:
SELECT
T.[ModelRegion]
,T.[TimeIndex]
,Sum(T.[Quantity]) AS [Quantity]
,Sum(T.[Amount]) AS [Amount]
,T.[CalendarYear]
,T.[Month]
,T.[ReportingDate]
FROM
(
SELECT
CASE [Model]
WHEN 'Mountain-100' THEN 'M200'
WHEN 'Road-150' THEN 'R250'
WHEN 'Road-650' THEN 'R750'
WHEN 'Touring-1000' THEN 'T1000'
ELSE Left([Model], 1) + Right([Model], 3)
END + ' ' + [Region] AS [ModelRegion]
, (Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month]) AS [TimeIndex]
, [dbo].[udfBuildISO8601Date] ([CalendarYear], [Month], 25) AS ReportingDate
FROM
[dbo].[vDMPrep]
WHERE
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')
) T
GROUP BY
T.[ModelRegion]
,T.[TimeIndex]
,T.[CalendarYear]
,T.[Month]
,T.[ReportingDate]
I don't have an AdventureWorks db handy to compare the execution plans but I'd be most interested to know what are the pro's and con's between the APPLY and the above query, especially in terms of performance. Would there be more overhead in my query as compared to using APPLY?
PS: Very sorry that the indentation doesn't seem to work here!
Arvin
April 2, 2015 at 5:00 am
not keen on copy and paste then?
April 2, 2015 at 5:55 am
This a good reference on APPLY. However, despite my experience with SQL Server, I was not familiar with the syntax in the first example. For others not accustomed to the column alias' appearing in parenthesis with the table alias, here's another way of putting it:
SELECT
cur.dt
, xApply.dt_year
FROM
(SELECT GetDate() dt) cur
CROSS APPLY (SELECT YEAR(cur.dt) dt_year) xApply
April 2, 2015 at 6:25 am
farid.abdi (4/2/2015)
not keen on copy and paste then?
Most definitely not! Copy and paste may save a few seconds during initial development, but can cause hours or days of agony during maintenance while you try to understand why a one-line change (in the copied code) caused your production run to fail.
Refactoring code to eliminate repetition leads to more robust solutions that are easier to maintain and (IMHO) easier to read.
Gerald Britton, Pluralsight courses
April 2, 2015 at 6:36 am
arvin_d (4/2/2015)
Hi.Wouldn't the logic of the APPLY syntax be logically similar to the following:
SELECT
T.[ModelRegion]
,T.[TimeIndex]
,Sum(T.[Quantity]) AS [Quantity]
,Sum(T.[Amount]) AS [Amount]
,T.[CalendarYear]
,T.[Month]
,T.[ReportingDate]
FROM
...snip
I don't have an AdventureWorks db handy
Grab it here: http://msftdbprodsamples.codeplex.com/
I keep it in my test DB since so many Microsoft examples use it as a base.
to compare the execution plans but I'd be most interested to know what are the pro's and con's between the APPLY and the above query, especially in terms of performance. Would there be more overhead in my query as compared to using APPLY?
The execution plans are virtually identical. In fact, the refactored execution plans have one less "Compute Scalar" step than the original.
PS: Very sorry that the indentation doesn't seem to work here!
Arvin
Easy to do: just wrap your SQL in the "code" tags provided.
FWIW here is a third approach using a CTE. Note however, that with both the subquery and the CTE approach, you need to repeat some columns (Month, CalendarYear, Model (Subquery approach))
WITH CTE AS
(
SELECT [Model]
, [CalendarYear]
, [Month]
, [Amount]
, [Quantity]
, CASE [Model]
WHEN 'Mountain-100' THEN 'M200'
WHEN 'Road-150' THEN 'R250'
WHEN 'Road-650' THEN 'R750'
WHEN 'Touring-1000' THEN 'T1000'
ELSE Left([Model], 1) + Right([Model], 3)
END + ' ' + [Region] AS [ModelRegion]
, (Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month]) AS [TimeIndex]
, [dbo].[udfBuildISO8601Date] ([CalendarYear], [Month], 25) AS ReportingDate
FROM [dbo].[vDMPrep]
)
SELECT
[ModelRegion]
,[TimeIndex]
,Sum([Quantity]) AS [Quantity]
,Sum([Amount]) AS [Amount]
,[CalendarYear]
,[Month]
,[ReportingDate]
FROM CTE
WHERE
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')
GROUP BY
[ModelRegion]
,[TimeIndex]
,[CalendarYear]
,[Month]
,[ReportingDate]
Gerald Britton, Pluralsight courses
April 2, 2015 at 6:54 am
Nice article. Good points. And all that Jazz.
But - I was stunned by the use of underscores as source aliases.
cross apply (something) as _
cross apply (somethingelse) as _1
That threw me for a loop, since it's using non-alphanumeric chars as a name, and makes for cryptic select statement references.
Why not, cross apply (something) as SomethingDescriptiveButNotThisLong
Or am I missing something obvious?
April 2, 2015 at 7:14 am
INCREDIBLEmouse (4/2/2015)
Nice article. Good points. And all that Jazz.But - I was stunned by the use of underscores as source aliases.
cross apply (something) as _
cross apply (somethingelse) as _1
That threw me for a loop, since it's using non-alphanumeric chars as a name, and makes for cryptic select statement references.
Why not, cross apply (something) as SomethingDescriptiveButNotThisLong
Or am I missing something obvious?
Good question! I actually answered it in the article. The reason is that I don't care about the APPLY aliases in these examples. I do not (and would not, in these examples) use them. Since they are syntactically required, I opt for aliases that all but disappear to the eye. You will notice that those aliases are not used in the main query.
The point is that the APPLY operator is being used to provide aliases for computed columns. It is those aliases that matter and the ones used in the query.
Gerald Britton, Pluralsight courses
April 2, 2015 at 9:35 am
On my browser, a link to "Using Apply" is https://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx, but the link in the article actually lands on "FROM (Transact SQL)"
April 2, 2015 at 9:46 am
Yes, that is true. The section "Using Apply" is about half-way down the page.
Gerald Britton, Pluralsight courses
April 2, 2015 at 10:01 am
g.britton (4/2/2015)
Good question! I actually answered it in the article...
Oh, hell. Are you saying I should actually read what I comment on.
I'm so caught red-handed right now.:hehe:
Viewing 15 posts - 1 through 15 (of 59 total)
You must be logged in to reply to this topic. Login to reply