RESOLVED | Outer Apply in a Query & Not able to run TOP 1000 rows in a View

  • sqlguru1day

    SSC Veteran

    Points: 275

    Hi all,

    Very new to SQL & have 2 Different questions here please -

    1. Can you please explain what OUTER APPLY is doing in the query below? I do get the joins and all but don't know what is the purpose of Outer Apply.

    2. Also, what can be a reason of me not being able to select TOP 1000* rows from a view? I even tried running TOP 10 rows it just goes on forever and doesn't return anything. I am on a prod server so doesn't want to cause any issues. But is there any other query I could try and return me some data and we can take it further from there.

    Thanks in advance.

    Rory

    SELECT

    E.[Promotions ID],

    P.[Promotion Desc.],

    P.[Customer Number] AS [Customer Code],

    C.[Group Description],

    H.[Brand Description] AS [Brand]

    FROM View.EventsData AS E

    LEFT JOIN View.Promotions AS P

    ON E.[Promotion ID] = P.[Promotion ID]

    LEFT JOIN View.Costs AS CO

    ON E.[Product Code] = CO.[Product Code]

    OUTER APPLY (

    SELECT TOP 1 *

    FROM Fact.PromotionData AS S

    WHERE E.[Promotions ID] = S.[Promotion] AND E.[Product Code] = S.[Product Code]

    ) AS S

    LEFT JOIN Dim.ProductHierarchy AS H

    ON E.[Product Code] = H.[Product Code]

    LEFT JOIN Dim.CustomerHierarchy AS C

    ON P.[Customer Number] = C.[Customer Number]

    LEFT JOIN Dim.Time as T

    ON P.[Start Date] = T.[Date]

    WHERE C.[Group Description] IN ('Supermarket')

    AND P.[Start Date]> '1 January 2020'

    • This topic was modified 1 month, 1 week ago by  sqlguru1day.
  • pietlinden

    SSC Guru

    Points: 62848

    This is really odd:

    LEFT JOIN View.Costs AS CO

    You're using the keyword VIEW as a schema name? That feels all kinds of No. Don't use reserved words as object names in T-SQL. It may come back to bite you later. Best to avoid that. Does the query run at all? Oh, and you're not using any of the results returned from your OUTER APPLY -- there's no S.[ColumnName] in your SELECT statement.

    Any time you type a word in the T-SQL editor and it shows up as any other color than the default color (usually black), it's a reserved word or function in T-SQL, so don't use that as a column name - you're just asking for trouble.

    SELECT
    E.[Promotions ID],
    P.[Promotion Desc.],
    P.[Customer Number] AS [Customer Code],
    C.[Group Description],
    H.[Brand Description] AS [Brand]
    FROM View.EventsData AS E
    LEFT JOIN View.Promotions AS P
    ON E.[Promotion ID] = P.[Promotion ID]
    LEFT JOIN View.Costs AS CO
    ON E.[Product Code] = CO.[Product Code]
    OUTER APPLY (
    SELECT TOP 1 *
    FROM Fact.PromotionData AS S
    WHERE E.[Promotions ID] = S.[Promotion]
    AND E.[Product Code] = S.[Product Code]
    ) AS S
    LEFT JOIN Dim.ProductHierarchy AS H
    ON E.[Product Code] = H.[Product Code]
    LEFT JOIN Dim.CustomerHierarchy AS C
    ON P.[Customer Number] = C.[Customer Number]
    LEFT JOIN Dim.Time as T
    ON P.[Start Date] = T.[Date]
    WHERE C.[Group Description] IN ('Supermarket')
    AND P.[Start Date] > '1 January 2020';
  • sqlguru1day

    SSC Veteran

    Points: 275

    Hi Pietlendin,

    Thanks for your prompt response.

    Yes you are correct I am NOT using View as a Schema name. It was just to give the audience a brief idea that the data is coming from a View.

    Refined Query below : It runs fine. Also, S.Column Name is now added in the query.

    Can you please have a look again and advise what is OUTER APPLY doing here?

    And do you have any suggestion for the below?

    2. Also, what can be a reason of me not being able to select TOP 1000* rows from a view? I even tried running TOP 10 rows it just goes on forever and doesn't return anything. I am on a prod server so doesn't want to cause any issues. But is there any other query I could try and return me some data and we can take it further from there.

    Refined query below:

    SELECT

    E.[Promotions ID],

    P.[Promotion Desc.],

    P.[Customer Number] AS [Customer Code],

    C.[Group Description],

    H.[Brand Description] AS [Brand],

    S.[Promo Price] AS [Scan Price]

    FROM Live.EventsData AS E

    LEFT JOIN Live.Promotions AS P

    ON E.[Promotion ID] = P.[Promotion ID]

    LEFT JOIN Live.Costs AS CO

    ON E.[Product Code] = CO.[Product Code]

    OUTER APPLY (

    SELECT TOP 1 *

    FROM Fact.PromotionData AS S

    WHERE E.[Promotions ID] = S.[Promotion]

    AND E.[Product Code] = S.[Product Code]

    ) AS S

    LEFT JOIN Dim.ProductHierarchy AS H

    ON E.[Product Code] = H.[Product Code]

    LEFT JOIN Dim.CustomerHierarchy AS C

    ON P.[Customer Number] = C.[Customer Number]

    LEFT JOIN Dim.Time as T

    ON P.[Start Date] = T.[Date]

    WHERE C.[Group Description] IN ('Supermarket')

    AND P.[Start Date] > '1 January 2020';

  • pietlinden

    SSC Guru

    Points: 62848

    The OUTER APPLY lets you join a table to a table-valued function, which you can't do with LEFT JOIN (that just joins tables/views). The APPLY forces the TOP query to run once for each record in the "outer" query.

  • Erland Sommarskog

    SSC-Insane

    Points: 23894

    OUTER APPLY is quite similar to a LEFT JOIN. The difference between JOIN and APPLY is that when you have A JOIN B, B cannot be a query that refers to A, but you relate B to A with the ON clause. With A APPLY B you don't have any join, but B can be a query (or call to a table-valued function) that refers to A, which is happening here.

    To be able to say why the query stalls when you say TOP 1000, we would need to know a lot more. We need to know the query plan, we need to know the view definitions, we need to know the tables and their indexes. All that information may bit a little too much to share here, so you be better off to find someone locally  with whom you can discuss the issue.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • sqlguru1day

    SSC Veteran

    Points: 275

    Thanks Erland!

Viewing 6 posts - 1 through 6 (of 6 total)

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