Running an AR Report - the query errored as the TempDB filled up.
Our DBA noted the following:
This query below caused the tempdb to grow and fill up the D: drive. The issue for the is connected to the view below – vdvCustomerPayment - which joins 24 tables. I do not think it is the joins but the DISTINCT that causes the issue as it needs to sort to remove duplicates.
(The only filters on data are for [CompanyID] = 'A', dbo.tarCustPmtLog.TranStatus = 3, dbo.vListValidationString.TableName = 'tarCustPmt' and dbo.vListValidationString.ColumnName = 'CreateType')
Can you check with Sage to see if the DISTINCT clause is absolute necessary or if there is another solution to this?
==================================================================================================
An additional note:
“One part of the view definition looks bad to me. Should the lines
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP -- batch type for all but misc
ON BL.BatchType = BTYP.BatchType
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP2 -- batch type for misc
ON BL2.BatchType = BTYP.BatchType
be
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP -- batch type for all but misc
ON BL.BatchType = BTYP.BatchType
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP2 -- batch type for misc
ON BL2.BatchType = BTYP2.BatchType
?”
