The main performance issue is performaing the "N" function infront of the strings in the where clause. I get 20x-45x perfromance boost by removing it, but unfortunately my reporting tool doesn't allow me that option.
Anything I can do on the DB end to solve this? The DB is not unicode.
tia,
paul
SELECT
MAX(Dim9s.FunctionDescription) [Dim9s_FunctionDescription],
MAX(Dim9s.BU_Description) [Dim9s_BU_Description],
SUM(convert(decimal(18,2),ixvTransAx.GLS_AMOUNTMST)) [GLS_AMOUNTMST]
FROM ixvLedgerBalanceDimTransAx AS ixvTransAx
JOIN ixvDim9s AS Dim9s ON (ixvTransAx.Dim9Id=Dim9s.Id)
LEFT OUTER JOIN ixvAccounts AS Accounts ON (ixvTransAx.AccountId=Accounts.Id)
JOIN ixvCompany AS CompanyId ON (ixvTransAx.CompanyId=CompanyId.Id)
WHERE 1=1
AND ((1=1)) AND ((Dim9s.Client ='M99') OR Dim9s.Client IS NULL)
AND ((1=1)) AND ((Accounts.Client ='M99') OR Accounts.Client IS NULL)
AND ((Accounts.Code BETWEEN N' 5000' AND N' 6796') AND (Accounts.ACCOUNTPLTYPE != N'^AC_Total^'))
AND ((Dim9s.BU_Description = N'PI/Dwights') AND (Dim9s.OrganizationalViewDescription = N'2006 Current'))
AND ((1=1))
AND (CompanyId.Code = N'56')
AND ((ixvTransAx.TransDate >= '2006-3-1') AND (ixvTransAx.TransDate < '2006-4-1'))
AND NOT (ixvTransAx.GLS_AMOUNTMST IS NULL)
GROUP BY Dim9s.FunctionDescription, Dim9s.BU_Description
Hi,
I recommend that you post your question on the Transact-SQL forum. The Documentation forum is usually not the best spot for code-specific questions.
Regards,
Gail
