Using APPLY to make your queries DRYer

  • Nice article. I use APPLY quite a bit...but have never even thought to use it in this scenario. You've opened my eyes where I didn't think you could or would...very nice. 😀

  • HOW about Common Table Expression expressions? You should be able to use them in View definition 🙂 And I believe that they performed are BETTER than CROSS APPLY

  • Thanks for good article!

    Haven't seen "_(dt_year)" before?

    How is the underscore here working?

    SELECT dt, dt_year

    FROM (VALUES (GetDate())) cur(dt)

    CROSS APPLY (SELECT YEAR(cur.dt)) _(dt_year)

  • tom.gjos (6/10/2016)


    Thanks for good article!

    Haven't seen "_(dt_year)" before?

    How is the underscore here working?

    SELECT dt, dt_year

    FROM (VALUES (GetDate())) cur(dt)

    CROSS APPLY (SELECT YEAR(cur.dt)) _(dt_year)

    It's just an alias for the virtual table [font="Courier New"](VALUES (GetDate())) cur(dt) CROSS APPLY (SELECT YEAR(cur.dt))[/font]. It wouldn't work in my environment, because I have a rule that every column name has to be qualified with a table alias - that would make it [font="Courier New"]_.dt_year[/font] in this case. Otherwise, if you introduce another table or view to your query that happens to have a column called dt_year, you get an ambiguous column error.

    John

  • The use of APPLY is interesting and I did learn a couple of things, but in my opinion the CTE approach exemplified earlier is cleaner and easier to read especially after moving the selection criteria to the CTE:

    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]

    WHERE

    [Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',

    'Road-650', 'Road-750', 'Touring-1000')

    )

    SELECT

    [ModelRegion]

    ,[TimeIndex]

    ,Sum([Quantity]) AS [Quantity]

    ,Sum([Amount]) AS [Amount]

    ,[CalendarYear]

    ,[Month]

    ,[ReportingDate]

    FROM CTE

    GROUP BY

    [ModelRegion]

    ,[TimeIndex]

    ,[CalendarYear]

    ,[Month]

    ,[ReportingDate]

    With this approach, the CTE performs data collection and transformation leaving the outer query to simply perform aggregation.

  • fregatepllada (6/9/2016)


    HOW about Common Table Expression expressions? You should be able to use them in View definition 🙂 And I believe that they performed are BETTER than CROSS APPLY

    As far as performance goes, the only true answer is "it depends." However for the example in this article, using CROSS APPLY in this way has no performance penalty at all.

    BTW, always keep in mind that CTEs are just another way to write sub queries, and generally result in similar -- if not identical -- execution plans as the same queries written with sub queries instead (except for recursive CTEs that is).

    OTOH I love CTEs for their ability to clean up a rats' nest of sub queries. To my eye at least, writing what are effectively sub queries up front yields easier-to-read code.

    Gerald Britton, Pluralsight courses

  • Aaron N. Cutshall (6/10/2016)


    The use of APPLY is interesting and I did learn a couple of things, but in my opinion the CTE approach exemplified earlier is cleaner and easier to read especially after moving the selection criteria to the CTE:

    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]

    WHERE

    [Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',

    'Road-650', 'Road-750', 'Touring-1000')

    )

    SELECT

    [ModelRegion]

    ,[TimeIndex]

    ,Sum([Quantity]) AS [Quantity]

    ,Sum([Amount]) AS [Amount]

    ,[CalendarYear]

    ,[Month]

    ,[ReportingDate]

    FROM CTE

    GROUP BY

    [ModelRegion]

    ,[TimeIndex]

    ,[CalendarYear]

    ,[Month]

    ,[ReportingDate]

    With this approach, the CTE performs data collection and transformation leaving the outer query to simply perform aggregation.

    Yes, that is an attractive, alternative approach. In the spirit of DRY, I feel I should point out that this variant repeats references to

    [CalendarYear]

    [Month]

    [Amount]

    [Quantity]

    that occur only once in the CROSS APPLY version. That means that if a new column is added (or an existing column removed), there are two places to change, not one. For me at least, that would potentially increase future maintenance efforts. Let's remember that code, whatever the language, is written (that is, created) once but often modified many times over by many different people.

    Gerald Britton, Pluralsight courses

  • g.britton (6/10/2016)


    In the spirit of DRY, I feel I should point out that this variant repeats references

    Yes, you are correct that column references are repeated. I feel that those are safer to repeat than calculated or derived columns for clarity's sake, but your point is taken. Still, I'd take clarity over cleverness any day provided performance and results are not impacted.

  • Aaron N. Cutshall (6/10/2016) [snip] Still, I'd take clarity over cleverness any day provided performance and results are not impacted.

    As do I! Though I don't consider the use of CROSS APPLY any more clever than a CTE performing the same function. It's just an alternate (and DRYer!) approach.

    Of course, this is a (very) simple example. Imagine there were many, many more columns. As the number of columns goes up, so does the repeated code and the choice between which tool to use becomes easier.

    Gerald Britton, Pluralsight courses

  • What about using the APPLY in the CTE?

  • John Hick-456673 (6/10/2016)


    What about using the APPLY in the CTE?

    If it leads to DRYer, more maintainable code, count me in! Why not take the example and rework it as you suggest then post your results?

    Gerald Britton, Pluralsight courses

  • Oh wow. Why have I never considered using APPLY for this purpose? You've just simplified my life. Thank you, sir!

  • Nice article, but

    - minus point for the usage of [dbo].[udfBuildISO8601Date] (UDFs are slow); there is a built in function DATEFROMPARTS() with the same syntax, that outputs a date. If necessary you could convert this date to whatever you want

    - instead of CROSS APPLY you could have used CROSS JOIN too

    God is real, unless declared integer.

  • ronmoses (4/2/2015)


    INCREDIBLEmouse (4/2/2015)


    Why not, cross apply (something) as SomethingDescriptiveButNotThisLong

    I also prefer at least semi-descriptive table aliases, just to help me keep track of where different values are coming from. So for a table of patient history data, I'll typically use 'pthist', and so on.

    Not to mention that if anybody tries to slip code containing an unqualified column reference past me, they'll get their login revoked for a number of minutes proportional to how hungover I am.

    Nice article though, I've actually used this before in some quite horrifically complex temporal search procedures and functions. When you're mangling datetime data types to the point that you need 10+ lines with indents just to make the code for a single expression readable, you don't want to have to paste those lines all over the place and then have to either comment them with their column alias or go hunting around the SELECT to find out what the hell they represent later on

  • t.franz (6/10/2016)


    Nice article, but

    - minus point for the usage of [dbo].[udfBuildISO8601Date] (UDFs are slow); there is a built in function DATEFROMPARTS() with the same syntax, that outputs a date. If necessary you could convert this date to whatever you want

    The point of the article was to redo a view in AdventureWorks to use CROSS APPLY, not to optimize it. Besides DATEFROMPARTS arrived with SQL Server 2012, and the AdventureWorksDW20008R2 database is designed for SQL Server 2008 R2, where that function is not available.

    instead of CROSS APPLY you could have used CROSS JOIN too

    Not sure how you could do that. You'd need to re-reference the source data set, and then how would you match the rows to get the correct derived column? You could maybe use an INNER JOIN (assuming you get the join columns correct) but I can't see how a CROSS JOIN would work.

    Still, if you're up for it, rewrite the solution using CROSS JOIN instead of CROSS APPLY and post your results. I for one would be most interested!

    Gerald Britton, Pluralsight courses

Viewing 15 posts - 31 through 45 (of 60 total)

You must be logged in to reply to this topic. Login to reply