0

I created a query for a client and I've been tweaking it trying to get it just right for their needs. I had it almost set except for some odd counts. After a serious FACEPALM moment I realized that the DISTINCT in the COUNT and SUM were throwing off my numbers, so I removed them.

However, now the query appears to run forever, when it used to take between 2-3min. I let it run overnight and it crashed after 5hrs with the following error:

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The tempdb.mdf was at 16Gig, and I had 7 tempdb_mssql_#.ndf files at 16Gig each, and my log file was at 4Gig, very happily filling my hard drive completely.

I rebooted the server and shrunk the database so I'm back to square one, but I don't understand why it did that.

Here is the query that is causing the issues:

SELECT
    DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
    p.[PART_X] AS [Assembly Part #],
    p.[DESCR_X] AS [Assembly Part Description],
    oa2.[Part Count],
    oa2.[Total # of Parts],
    ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) AS [Factory Std Cost],
    oa2.[# of Docs],
    oa2.[# of Software],
    oa2.[# of Std Cost Parts],
    oa2.[# of HR Devices],
    oa2.[Sum HR Devices],
    oa2.[# of 3rd Party Devices],
    oa2.[Sum 3rd Party Devices],
    oa2.[# of Robots],
    oa2.[Sum of Robots],
    oa2.[# of Buy Parts],
    oa2.[# of Make Parts],
    oa2.[# of Ref Parts],
    bom.[Make/Buy]

  INTO bomSummary   
  FROM AllPartsList ap
    LEFT JOIN visuser.EN_PART p
      ON p.[EN_Part_ID] = ap.[SourcePartID]
    LEFT JOIN bomBreakdown bom
      ON bom.[SourcePartID] = ap.[SourcePartID]
    OUTER APPLY (
        SELECT
            [Part Count]                = COUNT(    DISTINCT IIF(   bom.[Qty] = 0, null,                                                bom.[Component Part #]) ),  
            [Total # of Parts]          = SUM(bom.[Qty]),
            [# of Std Cost Parts]       = COUNT(    IIF( ( [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 ),                   bom.[Component Part #], null)   ),
            [# of Docs]                 = COUNT(    IIF(   bom.[Commodity Code] IN ('009', '072', '073', '075', '079', '082'),  bom.[Component Part #], null)   ),  -- Commodity Codes: 009, 072, 073, 075, 079, 082  :  Commodity ID: 15, 84, 85, 87, 81, 92
            [# of Software]             = COUNT(    IIF(   bom.[Commodity Code] IN ('034'),                                 bom.[Component Part #], null)   ),  -- Commodity Code 034  :  Commodity ID: 28
            
            [# of HR Devices]           = COUNT(    IIF( ( bom.[Commodity Code] IN ('002') AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0  AND bom.[Qty] > 0), bom.[Component Part #], null)   ),  -- Commodity Code 002  :  Commodity ID: 11
            [Sum HR Devices]            = SUM(      IIF( ( bom.[Commodity Code] IN ('002') AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0  AND bom.[Qty] > 0), bom.[Qty], null)    ),              -- Commodity Code 002  :  Commodity ID: 11
            
            [# of 3rd Party Devices]    = COUNT(    IIF(   bom.[Commodity Code] IN ('007'),                                 bom.[Component Part #], null)   ),  -- Commodity Code 007  :  Commodity ID: 5
            [Sum 3rd Party Devices]     = SUM(      IIF( ( bom.[Commodity Code] IN ('007') AND bom.[Qty] > 0),                  bom.[Qty], null)    ),              -- Commodity Code 007  :  Commodity ID: 5
            
            [# of Robots]               = COUNT(    IIF( ( bom.[Commodity Code] IN ('005') AND bom.[Make/Buy] = 'B' ),          bom.[Component Part #], null)   ),  -- Commodity Code 005  :  Commodity ID: 13
            [Sum of Robots]             = SUM(      IIF( ( bom.[Commodity Code] IN ('005') AND bom.[Make/Buy] = 'B' ),          bom.[Qty], null)    ),              -- Commodity Code 005  :  Commodity ID: 13
            [# of Make Parts]           = COUNT(    IIF( ( bom.[Make/Buy] = 'M' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 AND bom.[Qty] > 0 ),                bom.[Component Part #], null)   ),
            [# of Buy Parts]            = COUNT(    IIF( ( bom.[Make/Buy] = 'B' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 AND bom.[Qty] > 0),             bom.[Component Part #], null)   ),
            [# of Ref Parts]            = COUNT(    IIF( ( bom.[Make/Buy] = 'B' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) = 0 ),                              bom.[Component Part #], null)   )
          FROM bomBreakdown bom

          WHERE
            bom.[ComponentPartID] IS NOT NULL AND 
            bom.[SourcePartID] = ap.[SourcePartID]
          GROUP BY bom.[SourcePartID]
    ) oa2
    ORDER BY p.[PART_X]
  • AllPartsList has 1.3m Records
  • E_Part has 20,500 Records
  • bomBreakdown has 1.3m Records

Previously, in the Outer Apply all the queries except the first two had DISTINCT but I removed them. With the DISTINCT the query ran fine, the numbers were just off.

I don't believe this is a data/table layout issue, I think there may be a cross join or something that I'm missing.

Dizzy49
  • 1,360
  • 24
  • 35
  • Remove the columns in the SELECT and replace them with a COUNT(*) , remove the ORDER BY clause, remove the INTO clause. What is the total number of records that are returned? Is this an expected value or is this much larger than anticipated? – Igor Jul 31 '20 at 18:09
  • The fact that you group `bomBreakdown.SourcePartID` suggests it is not unique. So your `LEFT JOIN bomBreakdown bom` will result in record multiplication to start with. It also doesn't look necessary, the one attribute you select from it, should probably come from the `outer apply`. I also wonder why you choose an `outer apply` and use the same alias in it. – HoneyBadger Jul 31 '20 at 18:17
  • And if you can, rewrite `[dbo].[fn_getFactoryStdCost]` to a table valued function. They are much quicker. – HoneyBadger Jul 31 '20 at 18:20
  • @HoneyBadger Thanks, I'll give that a shot. I use it in another query that takes forever (rightfully so), so it will really help there! – Dizzy49 Jul 31 '20 at 18:21
  • 1
    Your query does not have a filtering predicate. You are pretty much materializing the whole join between three tables into a new table. – The Impaler Jul 31 '20 at 18:21
  • @HoneyBadger I thought I had a good reason for not including the [Make/Buy] in the `OUTER APPLY` but now I'm realizing I don't. I removed that join, and added [Make/Buy] to the `OUTER APPLY` – Dizzy49 Jul 31 '20 at 18:25
  • @Igor If I do that it includes all 1.3m rows, if I use `COUNT(DISTINCT ap.[SourcePartID])` then I get 5000 rows, which is what I expect. – Dizzy49 Jul 31 '20 at 18:28
  • @Dizzy49 I would write that as a `left join`, I don't see a necessity to use an `outer apply`, and a `left join` is simpler to read (I think). – HoneyBadger Jul 31 '20 at 18:28
  • @Dizzy49 I also think you probably shouldn't need a `distinct`. But that is a gut feeling, I don't know your data or requirements, but a `group by` and a `distinct` is suspicious. – HoneyBadger Jul 31 '20 at 18:32
  • @HoneyBadger The `DISTINCT` in the main select is used to filter the data, the `GROUP BY` is in the `OUTER QUERY` to match the distinct values in the main select – Dizzy49 Jul 31 '20 at 18:34
  • @Dizzy49 I understand that, but that means you have to `group by` twice: once for the aggregation, and once to determine uniqueness. – HoneyBadger Jul 31 '20 at 18:36
  • @HoneyBadger Doesn't work as a `left join` I get an error when doing the ON that it can't find `ap.[SourcePartID]`. I'm open to other ways to get the data to summarize as I need it to. – Dizzy49 Jul 31 '20 at 18:41
  • I think you basically just need the query that's currently in the `outer apply`. You can `left join visuser.EN_PART`, for the attributes you need from there. You'll need to decide whether to aggregate those, or add to the group. I'm not sure what you need `AllPartsList` for, but if it's just to restrict records, you can still add it. – HoneyBadger Jul 31 '20 at 19:09
  • @HoneyBadger It was the join for the [Make/Buy] that was causing the issues, want to put that in an answer so I can give you an solve :D – Dizzy49 Jul 31 '20 at 19:13

0 Answers0