Poorly Performing Unicode Query

Question

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

Answer 1

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